10 tips for troubleshooting Excel formulas and functions

Takeaway: Finding and fixing errors in formulas and functions can be tedious and time-consuming. Here are some practical tips to help you cut to the chase.

Troubleshooting an Excel worksheet’s formulas and functions can be a big job. Errors just come with the package. Excel offers some valuable auditing tools, found on the Formulas tab in the Formula Auditing group, but you won’t always need that much power. Sometimes, just a bit of special knowledge can help you resolve an error. These 10 tips will help even the most experienced Excel users find common errors quickly.

1: Error values are a clue, not a nuisance

If Excel can’t calculate a formula, it displays an error value. These values are valuable clues and knowing what they mean can help you quickly spot errors. The following error values are your first step, and often the only step, to debugging an error:

• #DIV/0: Excel can’t divide by 0 and your formula refers to a cell that contains or evaluates to 0 or is blank. This is one of the easiest to find and fix.
• #NAME?: This error occurs when you refer to a range incorrectly or forget to enclose text in quotation marks. When Excel encounters text in a formula, it tries to interpret the text as a cell reference, a range name, or a function name. When it can’t recognize the text as any of those things, it displays this error value. Check your function names, cell references, and range names (make sure the range name actually exists if you’ve spelled it correctly). If all ranges are correct, make sure you’ve delimited text using quotation marks.
• #NULL!: You specified an intersection between two ranges that don’t intersect, such as A1:C3 and D4:F6. If the ranges do intersect, you’ve probably used a space character (the union operator) instead of a comma to separate the two ranges (A1:C3 D4:F6 instead of A1:C3,D4:F6).
• #NUM!: This error value indicates a problem with a number in the formula. Either the argument is invalid or the result is too large or small.
• #REF!: You’ve referenced a cell incorrectly. Most likely, you’ve deleted a cell. For instance, the expression =A1+B1 won’t return an error if you delete the value in B1. But if you delete column B, it will.
• #VALUE!: You’ve expressed the wrong data type or operator. The most common cause for this error value is a reference to text by a numeric operation.

2: Use error handling for #N/A

Excel’s #N/A error value isn’t like the rest — there’s usually nothing wrong with the formula itself. It means that Excel can’t return a result that makes sense. This error shows itself a lot with VLOOKUP() and HLOOKUP(). When one of these functions returns #N/A, it usually means it can’t find the value you’re looking for or that you haven’t sorted the list (which isn’t always necessary). Fixing this error requires error handling. Specifically, in this case, you’d wrap the original function in an error-handling routine that displays a meaningful message or more appropriate value. For instance, you might display the message, “value not found” — the user understands that — whereas the #N/A value might cause confusion.

3: Evaluate components

One of the quickest ways to track down a problem is to evaluate individual components in the Formula bar or even right in the cell. Specifically, you highlight a cell reference or simple equation, press [F9], and Excel evaluates the highlighted expression and returns the result in the Formula bar. Either way, you can quickly pinpoint specific problems with logic. (To edit in-cell, simply double-click the cell. If that doesn’t work, check the Advanced Editing Options.) Press [Esc] when you’re done.

Figure A shows a simple IF() function that returns the string “is down” if the yearly total for 2011 is less than 2010 — well, it’s supposed to. When evaluating the expression in the Formula bar, as shown in Figure B, you can quickly see that the true and false actions don’t match the conditional statement’s intent. This works, this time, because the expression follows the conditional logic: $44,075 is less than $45,962. But that won’t always be the case. You can also use the Formula bar to learn a constant’s value and to discover errors caused by invalid data. It’s not just for troubleshooting formulas.

Figure A

Shouldn’t this expression return “is down?”

Figure B

Evaluating a portion of the formula in the Formula bar helps you pinpoint logic errors.

The Evaluate Formula option on the Formulas tab (in the Formula Auditing group) works similarly. It’s a bit more methodical but certainly useful. In Excel 2003, you’ll find Evaluate Formula on the Tools menu under Auditing.

4: Check for multiple lines

Check the right end of the Formula bar if a formula looks too simple. Figure C shows a double arrow, which indicates that the formula comprises multiple lines. Click the down arrow to walk through the lines or click the Expand Formula Bar button (to the right) to display the full formula.

Figure C

The down arrow to the right indicates that there’s more to this formula.

To separate formula components, simply position the cursor where you want the formula to wrap to a new line and press [Alt]+Enter. Breaking an expression across lines can help you confine expression logic, where each line contains a step toward the resulting value. (Doing so often confuses users, though.)

5: Display formulas for easy checks

To quickly display all formulas, press [Ctrl]+~ (that’s the tilde sign to the left of 1). Figure D shows a sheet’s formulas. It’s a toggle, so pressing [Ctrl]+~ a second time will display results. Display formulas to check consistency and spot potential errors. Printing the formulas lets you continue your audit away from your computer. Most users already know how to do this, but you can use this tip to work more efficiently by using two windows, one with formulas, one with results, as shown inFigure E.

Figure D

Toggling between formulas and their results can help spot errors and inconsistencies.

Figure E

Display both formulas and their results to work more efficiently while troubleshooting.

You can also click Show Formulas in the Formula Auditing group on the Formulas tab to toggle between these two views. In Excel 2003, choose Options from the Tools menu. Then, click the View tab and check or uncheck the Formulas options in the Window Options section.

6: Select formula cells

Locating all the formulas in a complex sheet can be tedious work and you’re apt to miss some if you rely on your memory or eyesight. Instead, let Excel do the work for you as follows:

1. Press [F5] to display the Go To dialog and click the Special button at the bottom. In Excel 2003, choose GoTo from the Edit menu and then choose Special.
2. In the resulting window, check the Formulas option, as shown in Figure F.
3. Click OK. Figure G shows the result — in this case, a small range of formulas (B2:B4).

Figure F

Check the Formulas option.

Figure G

Use the Special option to select cells that contain formulas.

If you need a more permanent solution, read Identify which cells in Excel are formula cells.

7: It’s a date; no, it’s a number; no, it’s an error!

Sometimes, a formula returns a date or time value instead of the number you were expecting. This usually happens when a formula references a cell you’ve formatted as a date or time. In this case, there’s nothing wrong with the formula; simply reformat the referenced cell correctly and the error value should disappear.

8: Check the number of characters

Older versions of Excel limit formulas to 1,024 characters; Excel 2007 has increased the maximum character limit to 8,192. It’s doubtful that Ribbon users will ever hit that wall, but those still using the menu version might keep the 1,024-character limitation in mind. In addition, function nesting has increased from seven to 64 — it boggles my mind. For more limits on Excel 2003 and Excel 2007/2010, read the following articles:

• Excel specifications and limits (2003)
• Excel specifications and limits (2010)

9: When your function returns the function

If you enter a function, press [Enter], and Excel displays the function, Excel is interpreting the cell contents as text. There are two possible fixes:

• If there’s an apostrophe character at the beginning of the function, delete it.
• Make sure you haven’t formatted the cell as Text. If you did, change it to General or something more appropriate.

10: When your formula doesn’t recalculate

You know the formula or function works — it was fine until you changed one of the dependent values. When you did that, the function did not recalculate and show the updated result. The most likely reason is that you’ve turned off Excel’s automatic recalculation. It’s easy to turn this option off while working and forget to turn it back on. Simply reset the calculation method to automatic as follows:

1. Select the File tab and click Options. In Excel 2007, click the Office button and click Excel Options. In Excel 2003, choose Options from the Tools menu.
2. Select Formulas in the left pane. In Excel 2003, click the Calculation tab.
3. In the Calculation sections, check the Automatic option.
4. Click OK.