MS Excel Error Checking and Validation

In complex spreadsheets it is very easy for error values to creep into a function or formula that you have used.

These can occur by accident such as simply deleting a tab or column that the function refers to. One moment, all is well on your spreadsheet, the next you see error values appearing. How did they get there?

It is important to understand why the errors can occur and the various types that you may see in a spreadsheet:

#VALUE! – This error occurs when you use the wrong data type as a parameter in a function e.g. you enter text into the parameter of a function when the parameter expects a numeric value or a cell reference.

=SUM ("A1:A10") will produce this error because the function is expecting a valid cell reference, whereas the cell reference has been entered in quotes which designates it as a text string.

The formula should read =SUM (A1:A10)

Note that a green triangle appears in the top left of the cell, and an exclamation mark which if you hover your cursor over this will give you a pop-up menu. This will be discussed further in the article

#NAME? – this occurs if you spell a function name incorrectly or enter a function that does not exist. It can be very easy to make a typo when entering a function. In this example, SIM is a non-existent function.

The easiest way to get around this error is to use the handy drop down that appears when entering a function, so you will always get the name right.

#DIV/0 – if you have a number that is being divided by another number and the divisor is zero, then you will see this error. Note that the divisor could easily be to a cell reference on another sheet and because of imported data being used this could have a zero value when it had a number before

This error will also occur if the divisor refers to a blank cell.

You can use the IFERROR function to ensure that a zero value is returned for an error value in this case where a zero divisor has occurred. However, it could be dangerous to suppress other error types with this function, since they may need properly correcting!

#NULL! – This error comes about usually because you have specified a range without using a colon between the start and finish or you have ranges as parameters that do not intersect.

The formula should read =SUM (A1:A10)

The easy way to avoid this error is to use your cursor to select the range required instead of manually typing in the cell references

#REF! – this error comes about because you are refencing a cell or range location that no longer exists. It is usually because you have deleted a tab in the workbook, or a column in the spreadsheet which contained a cell that was referenced by your formula

The formula at cell A1 refers to the cell K14 on Sheet7. If column K on Sheet7 gets deleted, then the cell reference no longer exists and this error will appear.

#N/A! – This error is caused by using a function such as a lookup formula and searching for a value that does not exist.

In this example, the search criterion is a number, but it has quotes round it which designates it as text. Column B has numeric values not text, so the value cannot be found and return N/A

#NUM! – if your formula contains a numeric value which is not valid, then you will see this error

For example, using the square root function for the value of -1 will produce this error, since the square root of -1 does not exist

####### - when you see a row of hash signs displayed in a cell, it simply means that the value in the cell is too large for the column width

Either adjust the column width to show the full value or use alignment formatting to wrap the text or merge it into the next cell

Circular Errors

These come about, often inadvertently, because your formula refers to a cell which, possibly by a convoluted route, is taking the value from your current cell reference. Circular references can give incorrect results and should be removed as soon as possible

If you do try to enter a circular reference, you will get a warning message:

Whatever you do on this warning message, your circular formula will still be entered, but you will see a warning at the bottom left hand corner of the sheet. Also, the cell with the circular reference will be highlighted on each sheet in Excel 2019