Troubleshooting Excel Formulas
We all work with Excel, and it’s a formula. It is always a case that we tend to get some errors or unexpected results associated with the formula we use in Excel. Sometimes, we might get errors such as #N/A, #VALUE! etc. Sometimes we may want a date but getting numbers as a result. Or sometimes, We apply a formula, but it is not calculating the results throughout the sheet cells. To avoid such a situation, we troubleshoot those excel formulas.
All these are the cases in which you will have formula errors. Finding the bug/error in Excel formula is a tedious job sometimes, which can cause you a lot of time as well as money (Since you might be unproductive until the formula error gets resolved if working in an organization and ideally sitting idle which causes loss of money for organization).
However, there are some checkpoints; you know the same as everywhere around the globe? If you follow those methods, some of the formula errors can be eliminated. We call such checkpoints as formula troubleshooting methods. In this article, we will make you go through with some of the Excel’s most common formula errors and how to troubleshoot those to eliminate the errors.
You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Excel Troubleshooting (wallstreetmojo.com)
How to Troubleshoot Excel Formulas?
Below are various ways to troubleshoot the excel formula.
#1 – Troubleshooting the Error Value #N/A
These types of formula errors are so common. I mean, remember how many times you got #N/A error while working on any excel formula? Many of the times, yeah? We will see how this can be eliminated. Each of the error itself gives a clue/hint about how to eliminate it. See the steps below:
Step #1
If you get #N/A error, which most possibly occurs under lookup formulas such as VLOOKUP, HLOOKUPHLOOKUPHlookup is a referencing worksheet function that finds and matches the value from a row rather than a column using a reference. Hlookup stands for horizontal lookup, in which we search for data in rows horizontally.read more, etc. Now, if you see the screenshot below, I have a data of Name, Age, and Salary with me. Through that data, I am trying to figure out what could be the salary details for the Name “Sonal.” Since the actual data where I am looking the value up does not contain details of Sonal, we will get a #N/A error. See the screenshot below:
Step #2
You can eliminate this error by combining IFERROR in ExcelIFERROR In ExcelWhen an error occurs in any formula or argument, the IFERROR excel function is used to display the result. This function can be used in conjunction with other functions to alert the user of any errors in the formula.read more in a given formula and add a nice message instead of #N/A. See the screenshot below:
IFERROR allows you to add a text whenever there is an error occurred.
#2 – Troubleshooting Logical Formulas
You must have used some logical formulae within Excel, such as IF(). This formula checks for the logical condition and gives any of the two outputs (associated with the True and False value of the logical test). It is critical to define the logic behind such formulas; otherwise, they will give you unexpected results.
Suppose we have data, as shown below, which provides the total sale happen for 2018 as well as 2019.
We would like to check if the sale is going up or going down for 2019 with the help of the conditional IF statement.
We have tried conditional IF and got the result as shown in the screenshot below:
You can see under cell A5; we are getting a result as “Sales Up.” However, is that correct? We can still see the sales for the year 2019 is lesser than that of 2018. Where we possibly made a mistake?
Here, it becomes very important to evaluate your formula. Let’s check it one by one.
Logical Condition – B3 < B2 (Sales for the year 2019 < Sales for the year 2018) – Logical condition gives TRUE as an output.
Therefore, if the condition is TRUE, we need to supply the output as “Sales Down” instead of “Sales Up,” and if the condition is FALSE, we should get “Sales Up.”
What we can do is, we can interchange value_if_true and value_if_false under the logical statement and eliminate this error.
See the screenshot below:
Now we are getting the result as expected. Therefore, you can also eliminate the misleading results provided by formula by doing step by step evaluation of your formula. It becomes a necessity to evaluate the formula you typed so that you get the results as expected.
#3 – Is it Number or Date or not Both?
Suppose we are trying to add some sales values under B2 and B3 of the sheet for years 2018 and 2019, respectively.
However, as soon as we type the values, those are being reflected as date values.
Why should this happen? Is Excel gone mad? Isn’t it able to identify what I am trying to add to?
Well, this has just happened because the format of the cell has been unfortunately set to dates.
You can navigate to the format of the cell, and through the list of the dropdown, we can select general instead of date to get the desired output.
As soon as you set the cell number format as general, you can see the dates are converted into numbers, which were what you were ideally expecting.
This can be done reversely. Meaning, you want dates, but those are stored as numbers. You can change the cell number formatting from numbers to Date.
#4 – What if the Formula doesn’t Recalculate
What if you drag the formula across the rows and expecting Excel to make calculations on its own but not getting the updated answer across the different cells? We will take an example of this.
We have sales and Margin values for the year 2018 and 2019, as shown in the image below:
We wanted to capture the Margin%, which can be formulated as Margin/Sales (Margin divided by Sales). Let’s do this calculation across cell D2.
We can see that 0.82% or almost 1% margin we are making for the year 2018. Now drag the same formula across D3.
Since excel is smart and versatile, we expect it will consider the pattern and find out Margin% for 2019.
However, excel was not able to give you the exact answer. See, the answer is the same as that for 2018. This is happening because you must have set the calculation options to Manual under the Formula section.
Change the calculation option from manual to automatic, and you will be able to get the value for the exact margin% for 2019.
This is how we can troubleshoot the excel formulas as well as some of its predefined functions.
Things to Remember
- Not every error is a nuisance. In Excel, most of the time, the error itself gives you information about what has gone wrong with the formula (remember the #N/A)?
- Formula and Function troubleshooting can take a few minutes as well as can take an entire day and still not get resolved. We just tried some of the common issues and how to troubleshoot them in Excel. You might face some other issues.
- There is no such step by step troubleshooting since these are “as-such” instances.
Recommended Articles
This has been a guide to Excel Troubleshooting. Here we discuss how to troubleshoot excel formulas to eliminate the errors along with practical examples. You may learn more about excel from the following articles –
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
47.1K subscribers
Leave a Reply