When using Microsoft Excel, you may encounter a few common Excel errors while creating or working with formulas. It’s important to learn how to correct these errors, otherwise you may risk the chance of unintended results being displayed on your spreadsheet.
In this week’s tip, we take a look at a few of the common Excel errors and how to easily correct them.
Applies To: Microsoft Excel 2010, 2013, and 2016.
1. ###### error
Problem: The column is not wide enough to display all the characters in a cell.
Fix: Extend the width of the column.
2. # Div/0! error
Problem: Excel displays this error when a number is divided either by zero (0) or an empty cell.
Fix: Change the divider to a value that is not equal to 0.
3. #Name? error
Problem: The text in a formula is not recognised by Excel. This is caused by misspelling the function names. For instance =su(A1:A7) will result in the #Name? error.
Fix: Correct the error by entering =sum(A1:A7).
4. #Value! error
Problem: This error will be displayed if the formula includes cells that contain different data types. = B1(85) + B2(75) +B3(data) will result in the #Value! error message.
Fix: Replace B3(data) with a numeric value and the error will be corrected.
5. #REF! error
Problem: The error will be displayed when a cell reference is not valid. Deleting cells that were referred by other formulas will cause this error.
Fix: Refer the cells to the correct ranges and the error will be corrected.
6. #NUM! error
Problem: The formula or function contains invalid numeric values.
Fix: Using $ ,% symbols with the number can result in this error, so avoid using these.
7. #NULL error
Problem: Excel 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. =Sum(A1:A2 C3:C5) returns the #NULL error because the two ranges do not intersect.
Fix: =SUM(A1:F1 B1:B10) will return the correct reference.
To avoid all the frustration of having to deal with these errors, it is important to be able to identify these Excel errors and how to correct them.The next step would be to learn how to trap error messages within Excel and quickly fix it.
Download our Excel formula fix cheat sheet, and make those common formula errors a thing of the past.
