Error #1: “Hashtag, ERROR”!!!
No, I’m not asking you to search for tweets about errors. I’m talking about the “#” symbol that appears in cells that contain large numbers.
This isn’t an error in as much as it is a notification. Excel is trying to let you know that your column is too skinny and can’t display the values in their entirety.
The solution to this is simple: make the column wider. This can be done by either selecting a cell in the column to be adjusted, then select Home (tab) -> Cells (group) -> Format -> AutoFit Column Width.
Or, double click the divider to the right of the column letter you wish to resize.
Featured Course
Excel Essentials for the Real World
Error #2: #VALUE!
The appearance of the #VALUE! error message is typically the result of using data that is of an incorrect data type.
In the following example, we have created a series of subtraction formulas. The rows where empty cells exist do not cause an error because Excel treats empty cells as 0 (zero).
However, if we were to place text in one of those empty cells, Excel would attempt to subtract text from a number that generates the #VALUE! error message.
PRO TIP: If you think you may receive text in these cells and you don’t want to see the #VALUE! error message, you can “trick” Excel into ignoring the text by placing the cell reference in a SUM function.
The SUM function ignores text when looking at a cell or a range of cells.
Error #3: #SPILL!
The #SPILL! error message is a new error type that is present in Office 365 and Office 2021.
This error occurs when a function attempts to output multiple rows (or columns) of results but encounters something in the target cells.
The only solution to this is to remove the blockage. In other words, delete or move the text outside the needed range of the formula.
Featured Course
Power Excel Bundle
Error #4: #NAME?
Examine the following formula and see if you can spot why it is generating a #NAME? error message.
Excel does not contain a function named “ADD”.
Excel’s method of adding cells is by way of the SUM function. Changing the formula to use a SUM function instead of a non-existent ADD function solves the problem.
The #NAME? will most often occur due to simple typos when creating formulas, like asking for a “BLookup” function instead of a “VLookup” function.
Error #5: #REF!
The #REF! occurs when a cell reference is no longer present, either on the sheet, another sheet, or another file.
Take this before and after example where a formula subtracts the content of columns C from column B, then we delete the column B data.
With Data in Columns B & C
Data in Column C Only; Column B Deleted
This can be a common behavior when copying and pasting formulas from one location to another in a sheet or between workbooks.
Imagine writing the following formula in cell A100…
=SUM(B1:B10)Then copying and pasting that formula in cell A1.
Because we are working with Relative References, the cell references will change to rows that are roughly 100 rows above rows 1 through 10 (i.e., B1:B10).
If such a place existed, the cell reference would be a negative value, like
=SUM(B-100:B-90)These are not valid locations on a worksheet, to say the least.
Featured Course
Master Excel Power Query – Beginner to Pro
Error #6: #DIV/0!
This is probably the most intuitive error message in Excel. It presents itself when you are attempting to divide a number (the numerator) by a zero (the denominator).
Remember: empty cells are treated as zero.
Handling Errors via Additional Functions
If you don’t want to see the #DIV/0! error messages, don’t delete the formulas from those cells displaying errors. Instead, wrap your existing formulas inside an IFERROR function.
The IFERROR function tests a formula to see if it results in an error, any error.
If an error occurs, the error message is captured and replaced by something of your choosing.
You can replace ugly Excel error messages with any of the following:
- A 0 (zero)
- Nothing (nothing is represented as two sets of double-quotes with nothing between them)
- A user-friendly error message (text contained within double-quotes, like “Item not found”)
Error #7: #N/A!
The #N/A! error message is commonly seen when working with lookup functions.
#N/A! is displayed when the value being searched for is not located in the data being searched.
In the formula above, the text “Limbo” is not located in the “Customer” column of the data.
We could suppress this error message using the IFERROR function seen earlier, but the IFERROR function may be too broad in its scope when it comes to error notification. In other words, you may want to know if there is a reference error (#REF!) or a misspelling in a function name (#NAME!).
To be more selective as to which errors are captured and which are displayed, we can use the IFNA function instead of the IFERROR function.
The IFNA function only captures #N/A! errors and allows all other error messages to be displayed.
Practice Workbook
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
