Excel Troubleshooting (Table of Contents)
What is Troubleshooting in Excel?
Sometimes, in Excel, we get errors such as #N/A or #VALUE! even though we have applied formulae correctly (at least for ourselves, we feel we have applied those correctly). These kinds of issues might be due to different reasons such as having used the same formula multiple times as a reference, getting dates in number format, getting #N/A or #VALUE! errors etc. All these cases are such were finding what is going wrong with the formula is a bit difficult job for naked eyes. However, if we follow some checkpoints for such errors, there are ample possibilities that we will be able to eliminate those. This process of rectifying the errors from a formula and nullifying them is called troubleshooting. We will see some methods to troubleshoot the formula errors in Excel.
Examples of Troubleshooting in Excel
Lets us discuss the examples of troubleshooting in excel.
Example #1 – Troubleshooting Formula that gives #N/A Error
#N/A errors are so common. You might have come across with this specific error many of the times while working with spreadsheet formulas. Specifically, this error can widely be considered as associated with lookup formulae such as VLOOKUP, HLOOKUP or simply LOOKUP formula under Excel.
Consider an example as shown below. In this example, we have Name of person, their Age, and Location where they work. See the screenshot below:
We want to check the Age details for a person named “Kanchan”. We will use the VLOOKUP to check whether any age details for Kanchan are available with us.
Step 1: In cell F2, use the VLOOKUP formula to get the details for Kanchan. We’ve used E2 as a lookup_value parameter under VLOOKUP as it is the cell where the name “Kanchan” is stored (which we want to find Age for) A2:C4 is the range for table_array, since we wanted to check the Age value for Kanchan under this table. Col_index_num should be 2 as we have Age as a second column of the table. Finally, we want to have an exact match for lookup_value, therefore we use FALSE, zero as a [range_lookup] parameter.
Now, if you Press Enter key, you’ll get a #N/A error as shown below:
This is happening because the lookup_value we are trying to search under a given table “Kanchan” is not present in the table and hence no age details for “Kanchan” could be found.
This type of error can be neglected with the help of the IFERROR function which can be used in combination with any function.
4.9 (2,356 ratings)
View Course
Step 2: Use IFERROR in a combination of VLOOKUP to get the result as “No Value” if there is any error with VLOOKUP formula. See the screenshot below:
This function IFERROR allows us to have a nice message under excel for any formula where we get an error value for any error such as #N/A, #VALUE!, #DIV/0! error etc.
Example #2 – Troubleshooting “Numbers Stored as Dates” Error
In most of the cases, we come up with a situation where we try to input a number value for specific criteria but get a weird date value instead. We may usually think that there must be something going wrong with Excel or it might be going mad. However, that is not the case and we can troubleshoot this error as well with a minimal hassle. This error is called a Number Stored as Dates” error which can be eliminated. Follow the steps below:
Step 1: Consider the same data as in the previous example. However, this time we would like to add the Salary details for these three employees. However, as soon as I enter the salary value as 15000 for Patrick, we are getting a weird date value within the cell as shown in a series of screenshots below:
This issue we may get for all the cells if we add the salary details as 12000 and 10000 respectively for Martha and Amanda.
This is a super weird situation as we may not be able to understand why the dates are reflecting instead of number values 15K, 12K and 10K respectively.
This is just because the column C for Salary has number property, unfortunately, being set as Date. Due to which whatever value you’ll input will be reflected as a date value. This issue can be troubleshot by changing the value formatting for the cell.
Step 2: Select the entire column C and navigate towards Number group under the Home tab, where we can change the number formatting for cells. You can see the value format for the selected column is Date.
Step 3: Select the cell value format as Number through the dropdown list to convert all those date values into Number values.
Now, you can see that the column named Salary is having all numeric values as expected.
Example #3 – Troubleshooting Formula Auto Calculating Error
Sometimes, we may face up a situation where we have applied formulae however, it is not calculating across cells.
Now, we want to capture the incentive as 10% on current salary. This can be done with the formula as shown below in cell D2.
If you drag this formula across the cells up to D4, then we could see the value for incentive is the same as $1500. Which ideally should be $1200 and $1000 respectively.
This is because the formula calculations option under your Excel file is on Manual instead of Automatic. You either can select the cell, press F2 to edit it and press Enter key to manually calculate the Incentive values for another employee. Or else, you can set the calculation options to Automatic instead of manual to automatically calculate the formulae.
Navigate to the Formulas tab. Under the Calculation group, you can see a Calculation Options dropdown as shown in the screenshot below:
Under Calculation Options dropdown, you’ll see three options namely – Automatic, Automatic Except for Data Tables and Manual (this one will be ticked). Out of all three options available under dropdown click on Automatic and you can see the formulae are calculated automatically.
These are some of the methods which can be used to troubleshoot the problems with Excel Formulae. Let’s wrap the things up with some points to be remembered.
Things to Remember
- Troubleshooting cannot be performed step by step. As the issues which we face are “as-such” issues. Meaning, any issue can occur at any point of the time and hence has no step by step solution for the same.
- Most of the times, error you are getting itself informs you about the issue with formula and what can be done to get it resolved. For Ex. #N/A tells you that the value in formula is not present under given range of table, #VALUE! error tells you that at least one cell which should have numeric value is containing a text, #DIV/0! Error tells you that, you tried dividing the numerator with zero. Etc.
- It may take few minutes in some cases for troubleshooting whereas some cases might take the entire day still not get resolved.
Recommended Articles
This is a guide to Excel Troubleshooting. Here we discuss How to Troubleshoot Excel Formulas along with practical examples and downloadable excel template. You can also go through our other suggested articles –
