Excel Troubleshooting

We all work with Excel and its formula. However, it is always the 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 get numbers as a result. And sometimes, we apply a formula, but it does not calculate the results throughout the sheet cells. To avoid such a situation, we troubleshoot those Excel formulas.

All these are the cases in which we may have formula errors. Finding the bug/error in an 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. Do we know the same as everywhere around the globe? If we follow those methods, we can eliminate some formula errors. We call such checkpoints formula troubleshooting methods. This article will make you go through some of Excel's most common formula errors and how to troubleshoot those to eliminate the errors.

Below are various ways to troubleshoot the Excel formula.

#1 - Troubleshooting the Error Value #N/A

These types of formula errors are so common. Remember how often you got a "#N/A" error while working on an Excel formula? Many of the times, yeah? We will see how we can eliminate this. Each error itself gives a clue/hint about how to stop it. See the steps below:

Step #1

Suppose we get a #N/A error, which most possibly occurs under LOOKUP formulas such as VLOOKUP, HLOOKUP, etc. For example, if we see the screenshot below, we have "Name," "Age," and "Salary" data with us. Through that data, we are trying to figure out what could be the salary details for the name "Sonal." Since the actual data where we are 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 Excel in a given formula and adding a nice message instead of "#N/A." See the screenshot below:

The Excel IFERROR function allows us to add a text whenever an error occurs.

#2 - Troubleshooting Logical Formulas

We 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). Therefore, it is critical to define the logic behind such formulas; otherwise, they will give us unexpected results.

As shown below, suppose we have data that provides the total "Sales" for 2018 and 2019.

We want to check if the sale is going up or 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:

We can see that under cell A5, we got a result as "Sales Up." However, we can still see the "Sales" for 2019 are lesser than that of 2018. So, where do we possibly make a mistake?

Here, it becomes very important to evaluate the formula. So let us 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." If the condition is "FALSE," we should get "Sales Up."

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, we can also eliminate the misleading results provided by the formula by doing a step-by-step evaluation of the formula. Consequently, it becomes necessary to evaluate the formula you typed to get the results as expected.

#3 - Is it Number or Date or not Both?

Suppose we want to try to add some sales values under B2 and B3 of the sheet for 2018 and 2019, respectively.

However, as soon as we type the values, those are being reflected as date values.

Why should this happen? Isn't it able to identify what we are trying to add?

Unfortunately, that happened because the cell format has been set to dates.

We can navigate to the format of the cell. For example, we can select "General" instead of date through the drop-down list to get the desired output.

As soon as we set the cell number format as "General," we can see the dates are converted into numbers, which we were ideally expecting.

We can do it reversely. As we want dates, we mean those are stored as numbers. So we can change the cell number formatting from "numbers" to "date."

#4 - What if the Formula doesn’t Recalculate

What if we drag the formula across the rows and expect Excel to make calculations independently but not get the updated answer across the different cells? We will take an example of this.

We have "Sales" and "Margin" values for 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). So let us do this calculation across cell D2.

We can see that we are making a 0.82% or almost 1% margin for 2018. Now, drag the same formula across D3.

Since Excel is smart and versatile, we expect it to consider the pattern and determine the "Margin%" for 2019.

However, Excel was unable to give the exact answer. So see, the answer is the same as that for 2018. That is happening because we must have set the calculation options to "Manual" under the "Formulas" section.

Change the "Calculation Options" from "Manual" to "Automatic" so we may get the value for the exact "Margin%" for 2019.

That is how we can troubleshoot the Excel formulas and some of their predefined functions.

• Not every error is a nuisance. For example, in Excel, the error may give us information about the formula that is gone wrong (remember the #N/A error?).
• The formula and function troubleshooting can take a few minutes, take an entire day, and still not get resolved. We just tried some of the common issues and how to troubleshoot them in Excel. We might face some other issues.
• There is no such step-by-step troubleshooting since these are "as-such" instances.