Excel: Debugging formulas
Formulas can sometimes yield error values or unintended results. Below are some tools that will help you investigate the causes of these errors and determine solutions.
Formulas are equations that perform calculations on values in the worksheet. A formula starts with an equal sign (=). In the example below, 3 and 1 are added together.
= 3 + 1
A formula can also contain one or more of the following items: functions, references, operators, and constants.
Parts of a formula
Functions: Included in Excel, functions are designed formulas in which specific calculations are performed. For example, the PI () function returns the value of pi: 3.142 …
References: refer to individual cells or cell ranges. A2 returns the value in cell A2.
Constants: numbers or text values entered directly into a formula, such as 2.
Operators: The operator ^ (caret character) is used to raise a number to a power and the operator * (asterisk) is used to multiply. Use + and – to add and subtract values. Use / to share.
Note: Some functions require that arguments are called. Arguments are the values that certain functions use to perform their calculations. If required, arguments are enclosed in the parentheses () of the function. No arguments are required for the PI function, which is why it is empty. Some functions require one or more arguments, and you can leave space for additional arguments. You must use a comma to separate arguments, or a semicolon (;) depending on your location settings.
The SUM function requires only one argument, but the function has 255 arguments.
= SUM (A1: A10) is an example of one argument.
= SUM (A1: A10; C1: C10) is an example of multiple arguments.
Common mistakes
The following table lists some of the common mistakes a user can make while typing a formula. And explains how to correct those errors:
Please note the following | More information |
---|---|
Start each function with an equal sign (=) | If you omit the equal sign, the data you type may appear as text or as a date. For example, if you SUM (A1: A10) Excel becomes the string SUM (A1: A10) is displayed and the calculation is not performed. If you 11/2 Excel will change the date as Nov 2 (as the cell format General instead of dividing the result 11 by 2. |
Make sure the opening and closing brackets match | Check whether each opening parenthesis has a corresponding closing parenthesis. When using a function in a formula, each parenthesis must be in the right place, otherwise the function will not work properly. The formula = IF (B5 <0), “Not valid”, B5 * 1.05) does not work as there are two parentheses close and only one parenthesis open while there must be one of each. The correct formula is: = IF (B5 <0, “Not valid”, B5 * 1.05). |
Use a colon to indicate a range | If you reference a range of cells, you must put a colon (:) as a separator between the reference to first and the reference to the last cell in the range. For example: = SUM (A1: A5), and not = SUM (A1 A5), causing the error # EMPTY! would be returned. |
Type all required arguments | Some functions have mandatory arguments. Also check that you have not provided too many arguments. |
Enter the correct type of arguments | For some functions, such as SUM, only numerical arguments are required, while other functions, such as TO REPLACE, a text value is needed for at least one of the arguments. If you use the wrong data type, some functions in Excel may produce unexpected results or an error. |
Do not nest more than 64 functions | You may enter (nest) up to 64 levels of functions within a function. |
Enclose names of other worksheets with single quotes | If a formula contains references to values or cells on other worksheets or in other workbooks, and the name of the other worksheet or workbook contains spaces or non-alphabetic characters, you must enclose this name in single quotes (“), for example = “Quarterly Figures”! D3, or = “123”! A1. |
Use an exclamation mark (!) After the worksheet name when referring to it in a formula | For example, to display the value of cell D3 in a worksheet named Quarterly data in the same workbook, use the following formula: = “Quarterly data”! D3. |
Include the path to external workbooks | Make sure that all external references contain the name of a workbook and the path to the workbook. A workbook reference contains the name of the workbook and must be enclosed in parentheses (Worksheet name.xlsx). The reference must also include the name of the worksheet in the workbook. If the workbook you want to reference is not open in Excel, you can still insert a reference in a formula. Enter the full path to the file, as in the following example: = ROWS (“C: My Documents [K2 activiteiten.xlsx]Sales ’! A1: A8). This formula gives the number of rows in the range of cells A1 through A8 in the other workbook (8). Note: If the full path contains spaces, as in the previous example, you must enclose the path in single quotes (at the beginning of the path and after the name of the workbook, before the exclamation mark). |
Type numbers without formatting | Do not format numbers that you specify in formulas. If you want to enter 1000 euros as value, enter 1000 in the formula. If you enter a comma as part of a number, Excel interprets the comma as a separator. To display numbers with thousands separators or currency characters, format the cells after entering the values. For example, if you want to add 3100 to the value in cell A3 and you enter the formula = SUM (3, 100, A3) Excel adds the numbers 3 and 100 and adds that total to the value in A3, instead of adding 3100 to A3, so if = SUM (3100, A3). For example, if you enter the formula = ABS (-2,134), Excel displays an error message because the ABS function accepts only one argument: = ABS (-2134). |
For more information, see Microsoft’s page on Excel.