# 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.

# 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.

# 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.

# 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.