How to Diagnose and Fix Formula Errors Step by Step in Excel

We will use formulas in Excel to solve a variety of problems. And in this article, we will demonstrate how to how to diagnose and fix formula errors step by step in Excel.

Most of the time, you will apply special function to solve certain Excel problems. And formula can handle most of the complex calculations. However, once the formula is complex, errors in formula can easily appear. Most of the time, one formula will contain several different functions, which add difficulties into the formula. Thus, it is difficult for us to check which step is wrong. Therefore, in the following part, we will show how to diagnose and fix errors step by step in complex formulas.

Diagnose and Fix Formula Errors

Here is an example of a wrong formula.An Example of Wrong Formula

You want to judge the sales volume of each sales representative. And it will return true if the number is larger than the target “650”. In this formula, it combines the IF function and VLOOKUP function. However, there is still a mistake. And now we will try to find out the reason.

  1. Click the target cell with a wrong formula.
  2. Now click the tab “Formulas” in the ribbon.
  3. And then click the button “Evaluate Formula”.Evaluate Formula
  4. And then you will see a new window.Click Step In

Because there are 2 functions in the formula, you can click “Step In” and show the first step.Step Out

  1. You can see that the result of the first step is right. And then click “Step Out”.
  2. Next click the button “Evaluate”. And you can see the result in the “Evaluation” box.The Error Show

In this example, the error appears in this step. The original name of worksheet is wrong and it shows “#NAME?” in the formula. Thus, you will know the reason.

  1. Now close the “Evaluate Formula” window and correct the formula. Here you need to add the apostrophe for the worksheet name.The Right Result

On the other hand, if in your actual formula, the mistake may appear in next step. And you can continue click the button “Evaluate” to check.

In addition, if you come across a complex formula, you can also use this feature to know how it works.

Excel Errors will also Damage Formulas

Almost all the software have errors. And Excel is no exception. Some errors will cause unexpected results. And the formulas are also victims of those errors. To protect those important elements, you can also prepare a useful tool. It can recover Excel in a quick time and the formulas will also remain in the file.

Author Introduction:

Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including word recovery and outlook repair software products. For more information visit www.datanumen.com

Comments are closed.