How to Detect Errors in Formulas

addition black and white black and white chalk
  • Applies to: Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel 2019, Excel 2016, Excel 2019 for Mac, Excel 2013, Excel 2010, Excel 2007, Excel 2016 for Mac, Excel for Mac 2011, Excel Starter 2010.

Formulas can often cause error values as well as gathering unexpected results. The following are some tools that you can use for finding and correcting formula errors then analysing their causes, and identifying solutions.

Note: This topic includes techniques that can guide you with finding and correcting formula errors. It is not an exhaustive list of methods for correcting every possible formula error. For help on specific errors, you can search for questions like yours in the Excel Community Forum, or post one of your own.

Learn how to enter a simple formula

Formulas are equations that perform calculations on values in your worksheet. A formula starts with an equal sign (=). For example, the following formula adds 3 to 1.

=3+1

A formula can also contain any or all of the following: functions, references, operators, and constants.

Parts of a formula

Parts of a formula

  1. Functions: built-in with Excel, functions are formulas that perform specific calculations due to its articulate design. For example, the PI() function returns the value of pi: 3.142…
  2. References: refer to individual cells or ranges of cells. A2 returns the value in cell A2.
  3. Constants: numbers or text values entered directly into a formula, such as 2.
  4. Operators: The ^ (caret) operator raises a number to a power, and the * (asterisk) operator multiplies. Use + and – add and subtract values, and / to divide.

Note: Some functions require arguments. Arguments are the values that certain functions use to perform their calculations. Arguments reside between the function’s parentheses () any time they must be present. The PI function does not require any arguments, which is why it’s blank. Some functions require one or more arguments, and can leave room for additional arguments. You need to use a comma to separate arguments, or a semi-colon (;) depending on your location settings.

The SUM function for example, requires only one argument, but can accommodate 255 total arguments.

SUM function

=SUM(A1:A10) is an example of a single argument.

=SUM(A1:A10, C1:C10) is an example of multiple arguments.

Finding and correcting common formula errors when entering formulas

The following table summarises some of the most common errors that a user can make when entering a formula, and explains how to correct them.

Make sure that youMore information
Start every function with the equal sign (=)If you skip the equal sign, what you type could be presented as text or as a date. For example, if you type SUM(A1:A10), Excel shows the text string SUM(A1:A10) and overlooks running the calculation. If you input 11/2, Excel presents the date 2-Nov (assuming the cell format is General) rather than dividing 11 by 2.
Match all open and closing parenthesesCheck that all parentheses are linked to a matching pair (opening and closing). Once you use a function in a formula, it is essential for each parenthesis to be in its right position for the function to work properly. For example, the formula =IF(B5<0),”Not valid”,B5*1.05) will malfunction because there are two closing parentheses and only one open parenthesis, when there should solely be one each. The formula should appear like so: =IF(B5<0,”Not valid”,B5*1.05).
Use a colon to indicate a rangeAfter you refer to a range of cells, use a colon (:) to split the reference to the first cell in the range and the reference to the last cell in the range. For example, =SUM(A1:A5), not =SUM(A1 A5), which would gather a #NULL! Error.
Enter all required argumentsSome functions have required arguments. Also, ensure that you have typed more arguments than what is necessary.
Enter the correct type of argumentsSome functions, like SUM, need numerical arguments. Other functions, such as REPLACE, mandate a text value for at least one of their arguments. If you use the wrong type of data as an argument, Excel might calculate inaccurate results or present an error.
Nest no more than 64 functionsYou can type, or nest, up to 64 levels of functions within a function.
Enclose other sheet names in single quotation marksIf a formula refers to values or cells on other worksheets or workbooks, and the other workbook’s/worksheet’s name includes spaces or non-alphabetical characters, you must enclose its name within single quotation marks ( ‘ ), like =’Quarterly Data’!D3, or =‘123’!A1.
Place an exclamation point (!) after a worksheet name when you refer to it in a formulaFor example, to return the value from cell D3 in a worksheet called Quarterly Data in the same workbook, use this formula: =’Quarterly Data’!D3.
Include the path to external workbooksGuarantee that each external reference has a workbook name and the path to the workbook.
A reference to a workbook contains the workbook’s name and is required to enclosed in brackets ([Workbookname.xlsx]). The reference must additionally include the worksheet’s name in the workbook.
If the workbook that you want to refer to is closed in Excel, you can still add a reference to it in a formula. You offer the entire path to the file, like in the following example: =ROWS(‘C:\My Documents\[Q2 Operations.xlsx]Sales’!A1:A8).
This formula returns the number of rows in the range that contains cells A1 until A8 in the other workbook (8).
Note: If the full path includes space characters, as does the previous example, you have to enclose the path in single quotation marks (at the beginning of the path and after the name of the worksheet, before the exclamation point).
Enter numbers without formattingDo not format numbers once you type them in formulas. For example, if the value that you want to state is $1,000, type 1000 in the formula. If you state a comma amongst a number, Excel assumes it to be a separator character. If you want numbers illustrated so that they display thousands or millions separators, or currency symbols, format the cells after you type the numbers.
For example, if you want to add 3100 to the value in cell A3, and you write the formula =SUM(3,100,A3), Excel adds the numbers 3 and 100 and then adds that total to the value from A3, as opposed to adding 3100 to A3 which would be =SUM(3100,A3). Or, if you input the formula =ABS(-2,134), Excel presents an error because the ABS function accepts only one argument: =ABS(-2134).

Finding and correcting formula errors – common problems in formulas

You can enforce specific rules for finding and correcting errors in formulas. These rules however, do not fully confirm that your worksheet is infallible, but nevertheless, they are huge timesavers in helping you to find common mistakes. You can enable and disable any of these rules individually.

There are two ways to highlight and correct errors: one error at a time (like a spell checker), or immediately during any of their occurrences on the worksheet as you enter data.

You can resolve an error by selecting Excel’s built-in options, or you can ignore the error by selecting Ignore Error. If you ignore an error in a particular cell, the error in that cell will refrain from displaying in pending error checks. However, you can reset all past ignored errors so that they display again.

Turn error checking rules on or off

  1. For Excel on Windows, press File > Options > Formulas, or
    for Excel on Mac, select the Excel menu > Preferences > Error Checking. In Excel 2007, click the Microsoft Office button Office button image > Excel Options > Formulas.
  2. Below Error Checking, check Enable background error checking. Any found error will include a triangle in the top-left corner of the cell.Cell with a formula error
  3. To edit the colour of the triangle that marks where an error occurs, in the Indicate errors using this colour box, pick your desired colour.
  4. Below Excel checking rules, choose or clear the checkboxes of any of the following rules:

Cells containing formulas that result in an error

  • A formula omits using the expected syntax, arguments, or data types. Error values comprise #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, and #VALUE!. Each of these error values have different causes and solutions.

Important Note

Note: If you enter an error value straight into a cell, it takes the place of that error value but is not actually an error. However, if a formula in another cell refers to that cell, the formula returns the error value from that cell.

Inconsistent calculated column formula in tables

  • A calculated column can have individual formulas that contrast from the master column formula, which produces an exception. Calculated column exceptions arise when you do any of the following:

  • Write data other than a formula in a calculated column cell.
  • Input a formula in a calculated column cell, and then use Ctrl +Z or click Undo Undo button on the Quick Access Toolbar.
  • Enter a new formula in a calculated column that already occupies one or more exceptions.
  • Copy data into the calculated column that differs from the calculated column formula. If the copied data has a formula, this formula replaces the data in the calculated column.
  • Reposition or erase a cell on another worksheet area that is directly referenced by one of the rows in a calculated column.

Cells containing years represented as 2 digits

  • The cell includes a text date that can be misinterpreted as the incorrect century once it is used in formulas. For example, the date in the formula =YEAR(“1/1/31”) could be 1931 or 2031. Use this rule to scan for vague text dates.

Numbers formatted as text or preceded by an apostrophe

  • The cell has numbers stored as text. This usually happens when data is imported from other sources. Numbers that are categorised as text can result in unexpected sorting results, so it is recommended to convert them to numbers. ‘=SUM(A1:A10) is classed as text.

Formulas inconsistent with other formulas in the region

  • The formula deviates from the preceding patterns of other nearby formulas. In many cases, formulas that are next to other formulas differ only in the references used. In the following example of four adjacent formulas, Excel states an error beside the formula =SUM(A10:C10) in cell D4 because the surrounding formulas increment by one row, and that one increments by 8 rows — Excel expects the formula =SUM(A4:C4).
Excel displays an error when a formula does not match the pattern of adjacent formulas
  • If the references that are used in a formula are inconsistent with those in the nearby formulas, Excel presents an error.

Formulas which omit cells in a region

  • A formula might not instinctively contain references to data that you add between the first range of data and the cell that has the formula. This rule compares the reference in a formula against the real range of cells that is close to the cell with the formula. If the adjacent cells include extra values and are filled, Excel shows an error beside the formula.For example, Excel inserts an error next to the formula =SUM(D2:D4) when this rule is used, because cells D5, D6, and D7 are next to the cells that are referenced in the formula and the cell that occupies the formula (D8), and those cells contain data that should have been referenced in the formula.
Excel displays an error when a formula skips cells in a range

Unlocked cells containing formulas

  • The formula is unlocked for protection. By default, all cells on a worksheet are locked so they can’t be modified when the worksheet is protected. This can help avoid accidental errors like mistakenly deleting or altering formulas. This error reflects that the cell has been set to be unlocked, but the sheet has not been protected. Check to ensure that you do not want the cell locked or not.

Formulas referring to empty cells

  • The formula has a reference to an empty cell. This can cause unexpected results, as illustrated in the following example. Suppose you want to calculate the average of the numbers in the following column of cells. If the third cell is blank, it is excluded in the calculation and the result is 22.75. If the third cell has 0, the result is 18.2.
Excel displays an error when a formula refers to empty cells
  • Data entered in a table is invalid: There is a validation error in a table. Review the validation setting for the cell by selecting the Data tab > Data Tools group > Data Validation.

Finding and correcting formula errors (common) one by one

  1. Firstly, select the worksheet you want to check for errors.
  2. Secondly, if the worksheet is manually calculated, press F9 to recalculate.If the Error Checking dialogue is not displayed, then click on the Formulas tab > Formula Auditing > Error Checking button.
  3. Furthermore, if you have previously ignored any errors, you can check for those errors again by doing the following: click File > Options > Formulas. For Excel on Mac, click the Excel menu > Preferences > Error Checking. In the Error Checking section, click Reset Ignored Errors > OK.
Error Checking

Note: Resetting ignored errors resets all errors in all sheets in the active workbook.

Tip: It might help if you move the Error Checking dialogue box just below the formula bar when finding and correcting formula errors.

Move the Error Checking box below the Formula Bar for finding and correcting formula errors.
  1. After this, click one of the action buttons in the right side of the dialogue box. The available actions differ for each type of error.
  2. Lastly, click Next.

Note: If you click Ignore Error, the error is marked to be ignored for each consecutive check. Pay close attention to this for finding and correcting formula errors.

Finding and correcting formula errors (common) individually

  1. Beside the cell, press the Error Checking button Error Checking icon that appears, and then choose your desired option. The available commands vary for each type of error, and the first entry outlines the error. If you pick Ignore Error, the error is marked to be ignored for each following check.
Move the Error Checking box below the Formula Bar

Finding and correcting a # error value

If a formula cannot correctly evaluate a result, Excel presents an error value, like #####, #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, and #VALUE!. Every error type has various causes, and different solutions.

The following table includes links to articles that summarise these errors in-depth, and a concise description to get you started.

TopicDescription
Correct a #### errorExcel presents this error when a column is too narrow to display all the characters in a cell, or a cell has negative date or time values.

For example, a formula that subtracts a date in the future from a date in the past, such as =06/15/2008-07/01/2008, results in a negative date value.
Tip: Try to auto-fit the cell by double-clicking between the column headers. If ### is shown because Excel can’t display every single character this will correct it.
# error
Correct a #DIV/0! errorExcel gives this error when a number is divided either by zero (0) or by a cell that has no value.

Tip: Add an error handler like in the following example, which is =IF(C2,B2/C2,0).

Error handling function such as IF can be used to cover errors
Correct a #N/A errorExcel displays this error when a value is not available to a function or formula.

If you’re using a function like VLOOKUP, does what you’re trying to lookup have a match in the lookup range? Most often, it doesn’t.

Try using IFERROR to suppress the #N/A. In this case, you could use:=IFERROR(VLOOKUP(D2,$D$6:$E$8,2,TRUE),0)

#N/A error
Correct a #NAME? errorThis error is presented when Excel does not recognise text in a formula. For example, a range name or the name of a function may be spelled incorrectly.

Note: If you’re using a function, make sure the function name is spelled correctly. In this case SUM is spelled incorrectly. Remove the “e” and Excel will correct it.

Excel displays #NAME? error when a function name has a typo
Correct a #NULL! errorExcel displays this error when you specify an intersection of two areas that do not intersect (cross). The intersection operator is a space character that separates references in a formula.

Note: Make sure your ranges are correctly separated – the areas C2:C3 and E4:E6 do not intersect, so entering the formula =SUM(C2:C3 E4:E6) returns the #NULL! error. Putting a comma between the C and E ranges will correct it =SUM(C2:C3,E4:E6)

#NULL! error
Correct a #NUM! errorExcel displays this error when a formula or function contains invalid numeric values. Are you using a function that iterates, such as IRR or RATE? If so, the #NUM! error is probably because the function can’t find a result. Refer to the help topic for resolution steps.
Correct a #REF! errorExcel displays this error when a cell reference is not valid. For example, you may have deleted cells that were referred to by other formulas, or you may have pasted cells that you moved on top of cells that were referred to by other formulas. Did you accidentally delete a row or column? We deleted column B in this formula, =SUM(A2,B2,C2), and look what happened.Either use Undo (Ctrl+Z) to undo the deletion, rebuild the formula, or use a continuous range reference like this: =SUM(A2:C2), which would have automatically updated when column B was deleted.

Excel displays the #REF! error when a cell reference is not valid
Correct a #VALUE! errorExcel can display this error if your formula includes cells that contain different data types.Are you using Math operators (+, -, *, /, ^) with different data types? If so, try using a function instead. In this case =SUM(F2:F5) would correct the problem.

#VALUE! error

Watch a formula and its result by using the Watch Window

When cells are out of view on a worksheet, you can observe those cells and their formulas in the Watch Window toolbar. The Watch Window makes it easier to monitor, audit, or verify formula calculations and results in huge worksheets. Using the Watch Window allows you to skip the need to constantly scroll or navigate to various sections of your worksheet.

Watch Window enables to easily monitor formulas used in a worksheet

This toolbar can be rearranged or docked like any other toolbar. For example, you can dock it on the bottom of the window. The toolbar records the following cell properties: 1) Workbook, 2) Sheet, 3) Name (if the cell has a corresponding Named Range), 4) Cell address, 5) Value, and 6) Formula.

Note: You can have only one watch per cell.

Add cells to the Watch Window

  1. Pick the cells that you want to watch. To choose all cells on a worksheet with formulas, on the Home tab, in the Editing group, press Find & Select (or you can use Ctrl+G, or Control+G on the Mac)> Go To Special > Formulas.
Go To Special dialog box
  1. On the Formulas tab, in the Formula Auditing group, select Watch Window.
  2. Pick Add Watch.
Click Add Watch to add a watch in your spreadsheet
  1. Verify that you have chosen each of the cells you want to watch and press Add.
In Add Watch, enter the cell range to watch
  1. To adjust the width of a Watch Window column, drag the boundary on the right side of the column heading.
  2. To present the cell that an entry in Watch Window toolbar refers to, double-click the entry.

Note: Cells that have external references to other workbooks are illustrated in the Watch Window toolbar only when the other workbooks are open.

Remove cells from the Watch Window

  1. If the Watch Window toolbar is hidden, on the Formulas tab, in the Formula Auditing group, select Watch Window.
  2. Choose the cells that you want to delete. To click multiple cells, press CTRL and then pick the cells.
  3. Select Delete Watch.
Delete Watch helps you with finding and correcting formula errors.

Evaluate a nested formula one step at a time

Sometimes, understanding how a nested formula calculates the final result is challenging because there are multiple intermediate calculations and logical tests. However, by using the Evaluate Formula dialogue box, you can notice the various parts of a nested formula evaluated in the order the formula is calculated. For example, the formula =IF(AVERAGE(D2:D5)>50,SUM(E2:E5),0) is simpler to understand when you can view the following intermediate results:

Evaluate Formula helps you to see how different parts of a nested formula are evaluated and finding and correcting formula errors.
In the Evaluate Formula dialogue boxDescription
=IF(AVERAGE(D2:D5)>50,SUM(E2:E5),0)The nested formula is first presented. The AVERAGE function and the SUM function are nested within the IF function.
The cell range D2:D5 contains the values 55, 35, 45, and 25, and so the result of the AVERAGE(D2:D5) function is 40.
=IF(40>50,SUM(E2:E5),0)The cell range D2:D5 includes the values 55, 35, 45, and 25, and so the result of the AVERAGE(D2:D5) function is 40.
=IF(False,SUM(E2:E5),0)Because 40 is less than 50, the expression in the first argument of the IF function (the logical_test argument) is False.
The IF function returns the value of the third argument (the value_if_false argument). The SUM function is skipped from evaluation because it is the second argument to the IF function (value_if_true argument), and it is returned only when the expression is True.
  1. Firstly, pick the cell that you want to evaluate. Just one cell can be evaluated at a time.
  2. Secondly, click the Formulas tab > Formula Auditing > Evaluate Formula.
  3. Thirdly, select Evaluate to inspect the value of the underlined reference. The result of the evaluation is shown in italics. If the underlined part of the formula is a reference to another formula, press Step In to display the other formula in the Evaluation box. Choose Step Out to return back to the previous cell and formula. The Step In button is unavailable for a reference the second time the reference displays in the formula, or if the formula refers to a cell in a separate workbook.
  4. Fourthly, keep clicking Evaluate until each part of the formula has been evaluated.
  5. Next, select Restart to view the evaluation again.
  6. Finally, choose Close to end the evaluation.

Notes: 

  • Some aspects of formulas that use the IF and CHOOSE functions are omitted from being evaluated — in these cases, #N/A is presented in the Evaluation box.
  • However, if a reference is blank, a zero value (0) is displayed in the Evaluation box.
  • The following functions are recalculated each time the worksheet changes, and can cause the Evaluate Formula dialogue box to give inconsistent results from what appears in the cell: RANDAREASINDEXOFFSETCELLINDIRECTROWSCOLUMNSNOWTODAYRANDBETWEEN.

Need more help?

Check out the following resources for answering your queries and/or suggesting a new feature/improvement to Excel with finding and correcting formula errors:

See Also

Display the relationships between formulas and cells

How to avoid broken formulas

Leave a Reply

%d bloggers like this: