Audit Your Excel Formulas Easily

Hello Excellers and welcome back to another #Excel #FormulaFriday blog post in my 2020 Excel series. This method will show you how to trace which cells contain the values to specific formula and which Excel formulas depend on values calculated by other cells.

If your workbook contains more than just a couple of formulas then it can eventually create a web of relationships that can soon become somewhat confusing.

Tools To Track And Audit Excel Formulas.

You can however use a couple of tools within Excel to track your formulas. These commands are Trace Precedents and Trace Dependents. Pretty self explanatory as to their function. Let’s take a look how we can use these to untangle and web of formulas we have in an Excel workbook.

The tools you need are in the Formula Auditing Group on the Formulas Tab.

So, our PRECEDENTS are cells that provide a value for a formula in another cell. So for example if we have a formula in cell C4 that is =A4+B4, both cells A4 and B4 are PRECENDENT cells for C4, just as C4 is a DEPENDENT of cells A4 and B4.

When we click the Trace Precedents command Excel displays blue arrows that point to your related cells.
Here is the same screen shot with the Trace Precedents selected.

We can look at a more involved example below where we can see some more Precedents and Dependents. In my example of expenses claim I have clicked Trace Precedents on cell J7. You can see the cells that provide the values for the formula in cell J7.

If we select cell F16 and hit Trace Dependents we can see which other cells contain a reference to the active F16 cell. In this case is it all of cells J4:J6.

Formula References On Another Worksheet.

Your formulas may not always be on the same worksheet. If you double click on the dotted arrow then the GOTO box will detail the source reference. You will also notice that there is a worksheet icon at the start of the arrow line.