Encountering Excel error messages are frustrating whether if you’re a beginner, intermediate or advanced level Excel user. The more often you use elaborate formulas, the more errors in Excel you will come across.
In financial modeling and analysis, the more complex the model is the more prone to errors. Excel functions like RATE, XIRR, VLOOKUP, and INDEX are useful tools. But if not used properly, these result in errors that bring huge consequences on financial models. So much so if #REF, #VALUE, #NA errors in Excel appear in end-user reports or presentations.
Identifying what these errors in Excel mean, using built-in Excel error checking functions as well as integrating your error checks in Excel can save you time and not to mention headaches. In this article, we will walk through Excel’s error-checking functions, discuss common Excel error messages we run into, and go over the ways to fix and handle these errors.
Why do errors in Excel occur? Formulas and functions in Excel have arguments and inputs that must follow certain characteristics. For example, for the COUNT function to work properly, the values to be counted must be numerical values (meaning no counting of text values) or else result in an error in Excel. Similarly, for the SUMIF function to add values based on given criteria, the parameters must be clearly defined. Errors also occur when Excel formula performs impossible calculations such as dividing a number by zero, finding the square root of a negative number, or multiplying a number with a text value.
Errors in Excel are displayed using 3 to 5 letters preceded by a pound sign (#) such as #REF, #VALUE, #NUM. Excel error messages may seem very cryptic to the untrained eye but once you understand what these errors mean, you can quickly debug these in no time.
Table of common Errors in Excel
Every time you open an Excel spreadsheet, Excel’s error checking capability is on the background constantly detecting any potential errors. If an error is spotted, Excel flags the cell with a green triangle at the upper left corner.
However, you can control error checks in Excel. You can enable or disable Excel’s error checking capability, change the color of the green triangle to your color preference and its checking rules. Numbers formatted as text or preceded by an apostrophe, formulas referring to an empty cell, or formulas inconsistent with other formulas are just a few of Excel’s error checking rules that can be disabled.
You can also access Excel’s error checking options by using the drop-down menu from the caution symbol called a “Smart Tag” attached to the cell with an error.
A quick way to find all errors in Excel is by pressing F5 in your keyboard (or CONTROL + G), this will open the Go To Special tool. Once you click “Special”, a dialogue box appears and under Formulas select Errors.
After clicking OK, cells with errors will be highlighted. The multi-year Balance Sheet example below highlights the #DIV/0! and #REF! Errors.
Performing error checks in Excel financial models are critical so modelers can see if the formulas are calculating properly. There are times where your model requires error checks specific to a business, industry, sector, budget, or project. Below are common error checks to incorporate in your financial model:
- Do percentages in the model sum up to 100%
- Did the user enter valid inputs? For example, corresponding start and end dates for the different phases of the project, growth rate input match model assumptions, etc.
- Do the company’s Total Assets equal its Total Liabilities & Equity in the Balance Sheet?
- Does the company’s Cash Flow Statement reflect the Cash activity in the Balance Sheet?
For example, you can spot errors in Excel’s multi-year Balance Sheet below by a simple Excel error checking formula that identifies the difference between Total Assets and Total Liabilities & Equity.
The worksheet’s 18th row is a simple and discreet error check in Excel to determine if Total Assets equal Total Liabilities & Equity. The value in cell C18 is the difference between Cell C9 and Cell C16. If the difference is zero, it tells the financial model’s calculations are correct (Total Assets equal Total Liabilities & Equity). Any other figure shows there is something wrong with the calculations.
Using conditional formatting in your financial model to highlight errors is also helpful as you can spot the error in a glance.
You can sum up the error checks in Excel for all your financial statements and other reports by summarizing it in one Excel error checking report.
Excel Error Checking Tool
The Excel error checking tool is found under the Formula Auditing group in the Formulas tab and is used to check common formula errors.
Using the same multi-year Balance Sheet example, there are 2 error types; #DIV/0! And #REF! errors in Excel.
To fix these errors one by one, simply click on the Excel Error Checking button and a dialogue box appears. The Error check in Excel explains the reason for the error and offers 4 available options on what to do next.
Options 1 and 2 give you more information about the error such as the meaning, what caused it, ways to avoid it, how to correct it, and other related errors in Excel. Option 3 allows you to simply ignore the error, selecting this will remove the green flag on the top-leftmost of the cell. Option 4 is where you can fix the formula using the formula bar.
You can also access the same dialogue box by clicking the drop-down menu from the caution symbol attached to the cell.
The below screenshots show the dialogue box for the #REF! error in Excel
It’s important to keep in mind that regardless of the type of error, the format and options in the dialogue box remain unchanged.
To fix the #DIV/0! and #REF! errors in Excel, simply choose option 4 to edit and correct the formula in the formula bar.
The #DIV/0! Error in Excel is fixed by using a non-zero divisor.
Most of the time mistakenly deleting or moving a range of cells that serves as reference cells for a formula is the main reason why #REF! error in Excel appears. The #REF! Error in Excel is fixed by 1st identifying the correct reference cell or range of cells, 2nd updating the formula with the correct reference cells.
When there are no other errors found in the worksheet, the dialog box below will be displayed.
Excel’s error-checking tools and functions help you spot and fix errors, however, you can also catch and manage Excel error messages before these are displayed using the 4 functions below:
Even if the error checks in Excel is complete it does necessarily mean it is 100% error-free, even an advanced level Excel user can still at times commit a few errors here and there. Here are 2 essential ways to help you:
- The best way to do Excel error checking is to avoid committing formula Errors in Excel from the very start. Keep in mind the below steps when using formulas and functions:
- Every function should start with an equal sign ( = )
- All open and closing parenthesis should match
- When indicating a range of cells, use a colon ( : ). It is also highly advisable to use named ranges as reference cells.
- Enter all required arguments of the function.
- Enter the correct type of arguments in the function. For example, check there are no numbers formatted as text, make sure text string is enclosed in double quotation marks (“ “), dates are entered in the correct format, etc.
- Incorporate your Error checks in Excel as you go along building a financial model to prevent unintentional errors. Keep in mind that not all models are the same, it depends on user preference, business, industry, etc. Doing this will also help you differentiate between actual error checks and model outputs.
Here’s a brief video that you can use as a reference on how to avoid #DIV/0 Erro in Excel.