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
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
You can use the ‘Formula Auditing’ group on the ‘Formulas’ tab of the ribbon to find all the circular references within the workbook. Click on the ‘Error Checking’ icon and then on ‘Circular References’ in the drop down, and you will see a list of circular references.
You may be using a spreadsheet that has cells linked to values in another spreadsheet. The problem here is that if you have been emailed the spreadsheet but not the one that it links to, or someone has removed or changed the name of the linked spreadsheet, then this can cause problems
As soon as you load a spreadsheet with links in it you will see this warning message which gives you the option to update the link or not as the case may be.
Whichever option that you take, you will get a #REF! error if the linked file cannot be found.
If this is on a spreadsheet that you are not familiar with and do not have access to the linked file, you may wish to dispense with the links, since every time the file is opened you will get a message regarding updating of the links. This can be extremely irritating for users if they do not have the file
You can see all the links in a spreadsheet by using ‘Edit Links’ on the ‘Queries & Connections’ group of the ‘Data’ tab on the ribbon
You can use the ‘Break Link’ button to remove the link. However, this will still leave the error #REF! as a text string in the cell and it will not show as an actual error so cannot be tracked. You can use ‘Find and Replace’ (CTRL-F) to find this cell but the problem is that it no longer has the data in it.
You can do a Copy and Paste Special (values only) to leave only the number, but how do you find where the linked cells are? The ‘Edit Links’ pop up does not give this information
Linked cells always use square brackets ([ and]) to define the linked filename. Use ‘Find and Replace’ (CTRL-F) to search for a square bracket within the workbook, and this will lead you to where the linked cells are. You can then edit change the cell to a value instead of a linked formula
Tracking the errors
In all these errors, with the exception of the multiple hash signs, you will see an exclamation mark on a yellow background adjacent to the error cell.
Hover your cursor on this and a pop-up menu will appear where you can obtain more information on the error.
This will give you options to help you sort the error out, including ‘Ignore Error’ which is not recommended!
You can also change the Error Checking Options from this menu:
You can use the ‘Error Checking’ icon in the ‘Formula Auditing’ group in the ‘Data’ tab of the ribbon to check for errors.
This will allow you to step through the errors on a selected tab, giving information about the error, and showing the calculation steps.
Note that this only works on the selected tab, not the whole workbook.
You can also use ‘Trace Error’ on the Error Checking drop down. This will place arrows to indicate the cells that affect the error value
If you find this article is helpful and you want to help others too, just share it in any social media (such as Facebook, LinkedIn).
Got it Pass Team is happy to work with Richard in presenting this article.
About Richard Shepherd-
Richard is an expert in MS Excel and Access with extensive experience of developing MI reporting systems, databases and dashboards using VBA or RAD for major companies such as Santander, Nationwide Building Society and Zurich Insurance. He is also the author of Access 2010 VBA Macro Programming and Excel VBA Macro Programming. He was graduated in University of the Thames Valley, United Kingdom.
Note: Some of the links in this page contain affiliate links. I will earn a small commission for any purchases you make, though there is no extra cost to you.
The products or services I recommend only I use them myself or after my investigation and truly believe they are useful to you. Don’t forget there are many other options out there – these are simply my preferred choices.