7 Common Errors in Microsoft Excel

If you’ve spent more than five minutes using Excel, you’ve encountered an error message.

Error messages are Excel’s way of telling us that we’ve done something it’s not happy about.

The problem with most Excel error messages is that they can be a bit cryptic if you haven’t been told what they mean.

It’s time to demystify these error messages and bring light to the darkness.  We’ll also show how to deal with what we’ll call “natural” or “expected” errors so we can either hide them or change them to something more meaningful to our users.

Error #1: “Hastag, 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.

Excel basics course Leila Gharani

Excel Essentials for the Real World - Complete Course

Learn Excel from Scratch or Fill in the Gaps.

Become Confident.

Packed with Challenges & Files (Excel 2016, 2019, Office 365).

GET ACCESS

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.

POWER EXCEL BUNDLE

Everything you need to master Excel’s Business Intelligence tools

GET ACCESS

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.

Power Query course Leila Gharani

Master Excel Power Query Course

Beginner to Advanced (including M) 

Collect, Combine and Analyze Data with Ease - Create Pivot Tables with Data Model.

GET ACCESS

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.

Excel Download Practice file

Learn Excel from a Microsoft MVP

Check out my bestselling Excel Courses

Learn anytime that fits your schedule.

Download files. Practice. Apply.

Visit Courses
Free Tutorials