WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Excel Troubleshooting

Excel Troubleshooting

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.

Excel Troubleshooting

How to Troubleshoot Excel Formulas?

Below are various ways to troubleshoot the excel formula.

You can download this Troubleshooting Formulas Excel Template here – Troubleshooting Formulas Excel Template

#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, HLOOKUP, 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:

Excel Troubleshooting Example 1

Step #2

You can eliminate this error by combining IFERROR in Excel 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.

Excel Troubleshooting Example 1-1

#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.

sales Example 2

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:

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Excel Troubleshooting Example 2-1

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:

Excel Troubleshooting Example 2-2

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.

Given data Example 3

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.

Excel Troubleshooting Example 3-2

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.

Format Example 3-3

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.

Excel Troubleshooting Example 3-4

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:

sales and Margin values Example 4

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.

Excel Troubleshooting Example 4-1

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.

Excel Troubleshooting Example 4-2

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.

Excel Troubleshooting Example 4-3

Change the calculation option from manual to automatic, and you will be able to get the value for the exact margin% for 2019.

Excel Troubleshooting Example 4-4

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 –

  • Protect Formulas in Excel
  • Add Error Bars in Excel
  • Use IFERROR with VLOOKUP
  • On Error Resume Next VBA
13 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Troubleshooting Formulas Excel Template

New Year Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More