Excel — How to trace formulas on your spreadsheet

Did you inherit a complex payroll spreadsheet and you need to figure out what all the formulas are calculating? Do you have a template you want to customize but you don’t want to break the formulas?

See below for tips on displaying formula precedents.

Complex spreadsheet

You can use the old fashioned way and double-click into a cell with a formula and you’ll see the color coded formula with the corresponding colored cells to illustrate where the data is coming from and what actions are being taken. These can sometimes be difficult to read the formula path and actions clearly.

Click in cell to see formula references

OR you can use “Trace Precedents”. While selecting the cell with the formula, go to the top menu and select the Formulas TAB. From there select Trace Precedents from the Formula Auditing section.

Excel Header showing Trace Precedents

This results in a much easier to read path of calculation. Click on Remove Arrows when done.

Trace Precedents results

Are you still struggling to figure out the formula?

Try “Evaluate Formula”. While selecting the cell with the formula, go to the top menu and select the Formulas TAB. From there select Evaluate Formula from the Formula Auditing section. This opens up a window where you can explore the steps of the formula one at a time.

Evaluate Formula Window

Clicking on Evaluate will replace the cell reference with the corresponding contents. As you continue to click on Evaluate, you will end with your cell contents. In this case 533.50. Below are a few of the steps.

Step 1

Step 2

Step 3

Step 4

In this case it is a simple formula of multiplication and addition, but this tool could be handy with longer and more complex formulas.

This tip might not save the day, but it can save you a whole lot of time when you need to understand how a spreadsheet you’ve inherited works.