Errors in Excel and how to use them effectively

Making use of errors

OK, so logic says that you don’t want to have any errors in your spreadsheet & broadly speaking, that’s correct… however, there can be occasions when an “error” can be helpful.

As we saw in last month’s tip, the #N/A error in a VLOOKUP Function means that the item that you’re looking for can’t be found in the list that you’re checking. If you’re using Excel to help with a bank, supplier or other sort of reconciliation, then that can be actually quite useful as it shows the items that are in one list & not the other. We can then use an IF or IFERROR Function to decide what to do with the result; if you can’t remember, then check here:

Error types

#NAME? error

The #NAME? error occurs when Excel does not recognise text in a formula.

Simply correct SU to SUM.

#VALUE! error

Excel displays the #VALUE! error when a formula has the wrong type of argument.

Change the value of cell A3 to a number or use a function to ignore cells that contain text.

#DIV/0! error

Excel displays the #DIV/0! error when a formula tries to divide a number by 0 or an empty cell.

Change the value of cell A2 to a value that is not equal to 0 or prevent the error from being displayed by using the logical function IF.

Explanation: if cell A2 equals 0, an empty string (“”) is displayed. If not, the result of the formula A1/A2 is displayed.

#REF! error

Excel displays the #REF! error when a formula refers to a cell that is not valid.

For example, cell C1 references cell A1 and cell B1.

If column B is deleted (to achieve this, right click the column B header and click Delete.)

In the “new” cell B1 (which was C1 before we deleted column B) the reference to cell B1 is not valid anymore.

To fix this error, you can either delete +#REF! in the formula of cell B1 or you can undo your action by pressing CTRL + z

Using the error type in a formula

Here’s a short table with the “odd” calculation or two

As you can see, I’ve used the ERROR.TYPE Function to display which error is showing. We can then use this, perhaps with a VLOOKUP to generate an informative answer…

When is an error not an error?

Sometimes, when entering data (especially if it’s a date) into a cell, you’ll see ########

There are two reasons for this. The first is that the cell column is too narrow to display the value. The fix is simple: just expand the column to fit.

The second reason you could see ######## is if a date-formatted cell becomes negative. Usually this happens when dates or times are subtracted from each other (for example if you’re working out the difference between two dates. Excel will usually initially format the calculation cell to match the date formats; you’ll just have to change it back to something more useful.