In a perfect world, nobody would ever make a mistake in Excel. Every formula would be flawless, and every bit of data would be valid. Unfortunately, that’s not the case. When mistakes happen, there are built-in errors, like #N/A, but why not create your own Excel formula error values, and show those too?

Excel Error Values

I’m sure you’re familiar with most of the built-in Excel formula error values.  Microsoft calls them hash errors, because of the character at the beginning.

• #NUM!
• #VALUE!
• #N/A
• #DIV/0!
• #REF!
• #NAME?
• #NULL!

Most of the hash errors end with an exclamation mark, but #NAME has a question mark, and poor old #N/A doesn’t get any punctuation. So sad.

Correcting Formula Errors

You can find troubleshooting tips for all the error values on the Microsoft site. Here’s a brief note on each hash error, and a link to the related Microsoft help page

#NUM! – Numeric values aren’t valid, or an iteration function can’t find a result, or  the formula result is a number that’s too large or small to be shown. #NUM!  Help

#VALUE! – Something is wrong with the formula or the cells that it references, or it could be something else, who knows? Microsoft admits that this one is vague, but has some common fixes #VALUE! Help

#N/A – The formula can’t find the thing it was asked to look for, usually with VLOOKUP, MATCH, etc.  #N/A Help

#DIV/0! – The divisor is a zero, and Excel can’t divide by zero.  #DIV/0!  Help

#REF! – The formula refers to a cell that isn’t valid. Perhaps a column or row is deleted, or a VLOOKUP range has 3 columns, and you ask for a result from column 4.   #REF!  Help

#NAME? – Usually caused by a type in a function name or a defined name. Or, you put text into a formula, but didn’t enclose it with double quote marks #NAME?  Help

#NULL! – The formula refers to a range that doesn’t exist. Often caused by the intersection operator (the space character) #NULL!  Help

Create Your Own Hash Errors

Instead of using Excel’s built-in hash errors,  it might be fun (and helpful) to create your own hash errors. Thanks to UniMord for this suggestion!

Here’s one example – a 6 month budget, with a limit of 600. In the total cell, an IF function checks the sum, and if it’s over the limit, a custom hash error appears – #OVER!

=IF(SUM(B2:B7)>D2, “#OVER!”, SUM(B2:B7))

It looks like an official warning from Excel, so perhaps your co-workers will pay attention to it!

Warning: Use this technique with discretion, because your co-workers might not like it! Be sure to add notes to the workbook, or cell comments, explaining what your custom hash errors mean.

Use a Custom Number Format

Another way to show a custom hash error is with conditional formatting.

In this example, there is a conditional formatting rule on the Total cell (B8)

=B8>D2

If that condition is met, the cell shows a custom number format:

“#OVER!”

Get the Sample File

I’m sure that you can think of other hash errors to show in your workbooks! What suitable-for-work errors would you add to your Excel files?

To see the custom hash errors from this blog post, go to the Excel Sample Files page on my Contextures site.

In the Functions section, look for FN0060 – Create Custom Hash Errors in Excel.

The zipped file is in xlsx format, and does not contain any macros.

________________________

Show Your Own Excel Formula Error Values

________________________

Original source: https://contexturesblog.com/archives/2019/02/14/show-your-own-excel-error-values/