Understanding the formulas can be challenging at times when our datasheets become complex. Fortunately, Excel has several formula auditing features that help us better understand them.

In this article, we'll examine how the evaluate feature helps you understand complex formulas by breaking them down into steps, and how other audit features simplify the process.

How Do Formula Auditing Features Help in Understanding Formulas Better?

The following are some benefits of using auditing features to understand formulas better:

  1. They are helpful when evaluating formulas used in pre-calculated sheets created by others.
  2. They make it easier to locate and remove errors, both from formulas and cells on which the execution of the formula depends.

How Does Evaluate Feature Work?

Here are a few things to keep in mind when using the evaluate feature in Excel:

  1. It underlines the part of the formula that will be evaluated in the next step.
  2. Every time you click Evaluate, the evaluation process will move to the next step by calculating the results from the previous evaluation.
  3. If you want to see which cell(s) the feature refers to in the underlined part, click Step In.
  4. Pressing Step Out will take you back to the evaluation process.

It is possible to stop the evaluation process by pressing Close, but you cannot skip any steps involved in the evaluation process.

For a clearer understanding of this, let's apply it to a dataset.

How to Use Evaluate Formula Feature to Understand Formulas Step By Step

As an example, consider the following situation where bonus eligibility was calculated using the Nested formula.

Calculation for Bonus Eligibility of Workers in Microsoft Excel

To see how Excel has implemented this formula, go to the cell where the formula has been implemented, click the Formulas tab, and click Evaluate Formula in the ribbon.

Evaluate Formula Option in Microsoft Excel in Microsoft Excel

Continue pressing the Evaluate button until the evaluation reaches the final result.

Evaluate Formula Box Evaluating the Formula in Microsoft Excel

Below is a list of all steps involved in the process.

All Steps Involved in Evaluation Process in Microsoft Excel

The more complicated the formula is, you'll find more steps in the evaluation process. However, each step is conducted separately, as seen here.

However, as with any formula or feature in Excel, the evaluate feature also has some limitations. To learn more about these limitations, visit the notes section of the Microsoft support page. If any of these limitations restrict you from doing your job, the option to shift to Excel alternatives is always available.

Other Auditing Features to Understand Formulas Better

A few other auditing features help better understand and analyze the formula besides the evaluate feature. Here are a few of them:

1. Trace Precedents

Trace precedents is a formula auditing feature that draws arrows indicating all the cells on which the final output of the selected cell depends.

Trace Precedents feature Drawing Arrow to Show Depenance of Formula on Other Cells in Excel

For the example shown above, cell D2's final output depends on cells B2 and C2. As soon as you select your desired cell and click the Trace Precedents option, Excel will draw arrows that refer to cells B2 and C2.

Therefore, when dealing with complex datasets with many formulas, you can quickly check the dependencies of all formulas and filter out cells to make the changes you need.

2. Trace Dependents

The trace dependents do the opposite of the trace precedents in Excel. It quickly shows you which formulas or cells will be affected if you change the values in your selected cells.

Therefore, this feature is also helpful while changing values in the dataset by tracing the arrows to avoid disrupting formulas already in place.

Trace Dependents Feature Showing Arrows Towards the Formula That Will Be Affected By Changing Values in Other Cells

As shown in the example above, changing the values in cell C2 will change the formula output in cell D2.

Hence, it's helpful to trace their dependents and precedents to better understand or evaluate formulas and their dependencies. Once you have analyzed the formula, click on the Remove Arrows to remove the traces.

3. Show Formula

Another feature, show formula, displays the formulas in each cell rather than the output values. In this way, if there are a lot of formulas spread across a spreadsheet, you can quickly look at each one for quick comprehension.

Show Formulas Feature Displaying All the Formulas in Microsoft Excel Sheet

4. Error Checking

Error Checking Feature Showing Details of Specific Error In Microsoft Excel

Using error checking, you can identify the type of errors in a formula quickly. It shows you the exact error, what type of error it is, and a short description to help you better understand it. Thus, you can quickly identify the errors and debug them with just a few clicks.

Furthermore, this feature helps locate errors in complex formulas that otherwise would be difficult to find. In addition, it helps when formulas executing on some cells fail to generate output on others.

Limitations of Formula Auditing Features

One of the significant limitations of all four formula auditing features is that they only work on a single sheet. While the trace dependents and precedents show that the formula depends on cells in another sheet, it can't locate them as it can in the same sheet.

Trace Precedents Showing Dependence of Formula in Another Microsoft Excel Sheet

Therefore, if you want to check the dependence of cells in one sheet or workbook with values in other sheets or workbooks, they won't help much, and you will have to locate them manually.

Watch window, another auditing feature in Excel, allows you to cross-check information across different sheets without switching sheets repeatedly. Let's see how it helps you in understanding formulas.

How to Use Watch Window Feature to Understand Formulas Better

The watch window acts as a small window containing the information you've added and remains visible when you change sheets in the same workbook. You can use it to analyze the dependence of formulas in one Excel sheet by changing values in another worksheet and observing the effect without switching between sheets.

Let's use it on a dataset to make the point clear. Below, you can see that the formula in cell F3 is dependent on a cell in Sheet 2. As it's showing an error, let's analyze the formula better by creating a Watch Window.

  1. Choose cell F3 and click the Watch Window.
    Watch Window Open Containing the Data of Cell in Microsoft Excel
  2. Go to Sheet 2.
  3. You can fix the error in Sheet 2 by using the Error Checking feature.
    Using Error Checking to Find Error in the cell in Sheet 2 of Microsoft Excel
  4. Enter a new value in cell B4 in Sheet 2.
  5. Hit Enter.
    Value in Watch Window Changed After Changing Value in Cell in Sheet 2 of Microsoft Excel Workbook

The above steps will alter the data in Sheet 1, which can be viewed in the Watch Window box without switching to Sheet 1.

Therefore, while analyzing complex formulas with multiple dependencies across several sheets, you can create numerous watch windows, change values across different sheets, and see the resulting impact on your formulas. Hence, the watch window feature simplifies formula analysis for us.

Excel features help us understand formulas better, but you should also organize your spreadsheets better, so they are easier to navigate.

Use Formula Auditing Features to Better Understand Formulas

The formula auditing features will come in handy when you analyze and understand the formulas in your spreadsheets. Utilize them at your convenience to simplify handling spreadsheets.

Excel has many advanced features that simplify data management, like formula auditing features that help you understand formulas better. These include fuzzy matching, importing statistics from websites, removing duplicates, etc.