How to Audit Formulas in Excel: The Formula Auditing Toolbar

The formula function in Excel is one of the most useful things any business owner or employee can use, especially when large volumes of data need to be evaluated. Sometimes you are given completed worksheets that you need to do extra analysis on. It is sometimes difficult to see where all of the functions in the file are and where they are coming from. When this happens, knowing how to use the Formula Audit toolbar is essential.

Formula Audits can be done several different ways. If you click on the Excel ribbon tab called Formulas, you can see the section labeled Formula Auditing. You may have to customize the ribbon to see this option. Below are the various ways that you can audit a formula.

Trace Precedents

Trace Precedents shows you all of the cells used to calculate a certain cell’s value. When active, you see a blue box around the cells and an arrow showing the direction of information flow.

To use this function, click on the cell that has the formula in it and hit the Trace Precedents button. All of the cells used in the formula of that cell will be outlined in blue. You can use the Remove Arrows button in this same section to get rid of the blue arrows.

If there are no trace precedents, then you will receive an error message from Excel.

Using Trace Dependents Function

This function allows you to see all of the formulas that particular cell is used in. For example, if you have a value that is used in multiple formulas in your spreadsheet, you can click on that cell, hit the Trace Dependents button, and all of the formula cells where that value is used will show up in blue with arrows pointing from that cell to the formulas that it is used in. You can use the Remove Arrows button in this same section to get rid of the blue arrows.

Using Show Formulas Function

This function is very useful when you want to see which cells are formula driven, as well as when you want to do a thorough review of all of your formulas at a glance. Instead of selecting each individual cell and look at the function bar, all you have to do is click on the Show Formulas button in the Formula Auditing section and all of the cell formulas appear instead of their values.

Error Checking

Error checking is useful if you have an extremely large spreadsheet with multiple tabs and you are not sure if all of the formulas are pulling through to the end correctly. By clicking on the Error Checking button, you can trigger Excel to look at all formulas and show any errors that need to be debugged.

Formula auditing is extremely useful if you are a given a spreadsheet with large amounts of important data. It could take a long time to review each individual formula separately, so using this function in Excel is a must.