Finding and correcting errors can be tedious and time-consuming in formulas and functions. Here are some practical tips for chasing you.
Fixing formulas and functions of an Excel worksheet may be a great job. The package comes with errors. Excel offers valuable auditing tools found in the Formula Auditing group on the Formulas tab, but you’re never going to need that much power. Sometimes, you can solve your bug with just a little bit of special knowledge. Even experienced Excel users will quickly find common mistakes with these 10 tips.
The error values are an indication, not an annoyance: If a form cannot be computed by Excel, the value of the error is displayed. These values are valuable indices and you can quickly identify mistakes if you know their meaning. The first step and often the only step you take to debug an error is the following error values:
- #DIV/0: Excel cannot be divided by 0 and your formula refers to a cell containing, evaluating, or being white. This can be found and fixed easily.
- #NAME?: This error happens if you incorrectly refer to a field or do not include text in quotation marks. Excel seeks a text as a cell reference, a range name, or a function name when it meets a formula. When the text cannot be recognized as one such thing, this error value will be displayed. Check function names, cell references, and range names (as long as you have spelled it correctly, make sure the range name actually exists). Make sure you have defined the text with quote signs if all of the ranges are correct.
- #NULL!: The link between two intersections, like A1:C3 and D4:F6, was specified. If the ranges are intersecting, you probably have used a space character (the union operator) (A1:C3 D4:F6 instead of A1:C3, D4:F6).
- #NUM!: This error value shows a problem with the formula number. Either it is invalid or it is too big or too small for the result.
- #REF!: You have incorrectly referenced a cell. You probably deleted a cell, most probably. For example, if you delete the value of B1 the phrase =A1+B1 will not return an error. But it’s A reference in a numerical operation is the most common reason for this error value.
Use error handling for #N/A: Excel’s #N/A error is not the same as the rest – the formula itself usually doesn’t have anything wrong. This means Excel cannot return a meaningful result. With VLOOKUP() and HLOOKUP, this mistake is very apparent (). When one of the features returns #N/A, it typically means you won’t find the value you are looking for (which isn’t always needed) or that you have not sorted the list. This error needs to be handled if this is fixed. In this case, specifically in an error-handling routine that displays a significant message or more appropriate value, you’d wrap the original function. For example, the message “value not found” could be displayed — the user understands this — while the #N/A.
Evaluate components: One of the fastest ways to track the problem is in the Formula bar or even right in the cell to evaluate individual components. Specify, press F9, and Excel will evaluate the highlighted expression and return the result to the formula bar to highlight the cell reference or simple equation. You can identify specific logic issues quickly, either way. (Simply double-click the cell to edit the in-cell. Check Advanced Editing Options, if that doesn’t work.) When you finish, click [Esc].
Check for multiple lines: Put the cursor where you wish the formula to wrap into a new line and press [Alt]+Enter to separate the formula components. Breaking an expression across lines may help you to confine the logic of expression, which contains a step to the resulting value. (Doing confuses users so often, however.)
Display formulas for easy checks: You can continue your audit from your computer by printing the formulas. Most users know how to do that, but using two windows, one with formulas and one with results, you can use the following to work more efficiently. Forms in the Formula Audit group can also be clicked on the Formula tab to switch between the two views. Select Options from the Tools menu in Excel 2003. Then, in the Window Options section, click the View tab to uncheck the Formula Options.
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:
- 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.
- In the resulting window, check the Formulas option.
- Click OK.
It’s a date; no, it’s a number; no, it’s an error!: Sometimes, instead of the number expected, a formula returns a date and time value. This is usually if a formula refers to a cell that you format as a date or time. In this case, the formula has nothing wrong; the referenced cell should simply be reformatted correctly and the error value should disappear.
Check the number of characters: Excel 2007 extended the maximum characters limit to 8,192 for older versions of its Excel limit formula for 1 024 characters. Ribbons are doubtful that they will ever touch that wall, but those who still use the menu version can remember the limitation of 1024 characters. Furthermore, nesting functions increased from seven to 64.
When your function returns the function: Excel interprets the cell contents as text if you enter a function, press [Enter] and the function displays Excel. Two possible solutions are available:
- If at the start of this function there is an apostrophic character, remove it.
- Be sure that you did not format the cell in Text formatting. If you did, change it to General or more suitable.
When your formula doesn’t recalculate: You know the formula or function works – it worked until one of the dependent values was changed. The feature was not recalculated and displayed the updated result when you did. The most likely reason is that the automatic re-calculation of Excel has been turned off. This option is easy to turn off when you work and forget to turn it on. Simply reset the method of calculation to automatic:
- Choose the tab File and click Options. Click Office and click Excel Options in Excel 2007. Excel 2007. Select Options from the Tools menu in Excel 2003.
- In the left pane, select Formulas. Click on the Calculation tab in Excel 2003.
- See the Automatic option in the Calculation sections.
- Click OK.
Author: Olivia Smith is a Microsoft Office expert and a full time blogger with 5 years of experience in the technology industry. She has written technical blogs, white papers, and reviews for a variety of websites, including office.com/setup.