There are many common Excel formula errors that can be corrected. Here are a few tips to help you avoid these errors:
- Make sure your formulas are correct.
- Use the correct type of input data.
- Check your work against the correct formulas and results.
- Use the correct order of operations when working with formulas.
- Check for mistakes in your work before submitting it to Microsoft Excel for review.
Formulas can be powerful ways to manipulate, evaluate, find, and analyze data. But with formulas can come problems. Here are several common formula errors in Microsoft Excel, how to correct them, and tools for further help.
When you enter a formula into a cell and hit Enter or Return, you may see one of the following errors in the cell instead of the expected result. You might also see errors beyond those listed here, but these are some of the most common formula errors in Excel.
Error:
This is probably one of the easiest errors to correct in Excel. If you have data in a cell and the column isn’t wide enough to display that data, you’ll see #####.
Fix: Widen the column by dragging the letter header to the right. Alternatively, simply double-click the double-sided arrow on the right side of the column. This adjusts the width to accommodate the longest string of data in that column.
Error: #DIV/0!
You’ll see the #DIV/0! error if you’re dividing by zero or a blank cell. This can happen easily if you haven’t entered all of your data before creating the formula.
In our example, we are dividing cell B2 by cell C2 and C2 is blank.
Fix: Confirm that your cell contains a value that isn’t zero or blank. You can also use the IFERROR function so that if an error occurs, it returns a result you specify rather than the #DIV/0! error.
Error: #N/A
When you use lookup functions like XLOOKUP, HLOOKUP, VLOOKUP, and MATCH, you’ll see this error when the formula can’t find a match.
RELATED: INDEX and MATCH vs. VLOOKUP vs. XLOOKUP in Microsoft Excel
Below we have a formula for the XLOOKUP function where the lookup value (20735) doesn’t exist in our data set.
Fix: Review your data to be sure the value you’re looking up exists or confirm you’ve entered the correct value in the formula.
Error: #NAME?
The #NAME? error is another common formula error you’ll see in Excel. This one can occur for several reasons.
You’ll see this error if the formula:
Contains a misspelled function Contains an undefined name Has a typo in the defined name Is missing quotation marks for text Is missing a colon between cell ranges
Here, we have one formula with a misspelled function and another with a missing colon.
Fix: Look for misspellings, typos, missing operators, and that named ranges referenced in your formula are defined.
Error: #NULL!
You’ll see this error in a couple of cases. The most common is if you have an incorrect range operator in your formula.
For instance, here we have the SUM function to add two cell ranges. The formula contains a space instead of a comma, which is a union separator.
Another time you’ll see the #NULL! error is if you’re using an intersection operator for ranges that don’t intersect.
Fix: Correct the range or union operator in your formula or change the cell ranges to those that intersect.
Error: #REF!
If you’re referencing an invalid cell in a formula, you’ll see the #REF! error. This can occur if you remove the column or row containing the reference, if you’re using a lookup function like VLOOKUP where the reference doesn’t exist, or you’re using INDIRECT for a closed workbook.
RELATED: How to Use VLOOKUP in Excel
In the formula below, we’re using SUM to subtract the value in cell B2 from that in cell C2. We then delete column C which produces the error.
Fix: Replace the column or row, rebuild the formula, or reopen the referenced workbook.
Error: #VALUE!
If you see this error, that means there is something wrong with how the formula is written or with the cells you’re referencing. You might use a math function for non-numeric data or cells that contain hidden spaces.
Here, we are using SUM to subtract values in two cells where one contains text, not a number.
Fix: Correct the data formats you’re using like text instead of number. In our example, we simply entered the wrong cell references. Or, look at the cell references for hidden spaces, such as leading or trailing spaces, and remove them.
Tools to Help With Errors in Excel
Errors can be aggravating but they can keep us from making mistakes and using incorrect data. For additional help figuring out errors in Excel, try these tools.
RELATED: How to Hide Error Values and Indicators in Microsoft Excel
Enable background error checking: Go to File > Options > Formulas. Check the box below Error Checking and then use the boxes in the Error Checking Rules section for those you want to see.
Use the Evaluate Formula tool: Go to Formulas > Evaluate Formula to open a step-by-step evaluation of your formula.
Trace the error: If you’re unsure of the cell with the issue in your formula, you can use the error tracing tool. Go to Formulas > Error Checking and select “Trace Error.” You’ll then see blue lines from your formula to the cells in the arguments. Fix the formula to remove the tracing.
Get help on the error: When you have a cell with an error and see the error icon display, click to show additional options. Select “Help on This Error” to open a sidebar with details on the issue.
Hopefully knowing when these common formula errors occur can help you avoid them in the future. For more, take a look at the basics of structuring formulas in Excel.
