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.
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.
- Click the target cell with a wrong formula.
- Now click the tab “Formulas” in the ribbon.
- And then click the button “Evaluate Formula”.
- And then you will see a new window.
Because there are 2 functions in the formula, you can click “Step In” and show the first step.
- You can see that the result of the first step is right. And then click “Step Out”.
- Next click the button “Evaluate”. And you can see the result in the “Evaluation” box.
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.
- Now close the “Evaluate Formula” window and correct the formula. Here you need to add the apostrophe for the worksheet name.
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.
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