Using Formula Tracing in Excel

Formula tracing is a fantastic visual tool in Excel you can use to understand 1) which cells a given formula is referencing and 2) whether a given cell (or range of cells) is used by a formula elsewhere. Both have their uses, but it’s certainly the latter I get the most use from.

Read on to learn more about this feature and how to use it to ‘debug’ and clean-up your Excel files.

Trace Precedents

Trace Precedents is the method we use to understand which cells will impact a given formula.

In my sample data, I have some raw data in the form of annual house building rates, and a few arbitrary summary boxes.

In my summary boxes, I select the cell I want to trace, then from the Ribbon tab, select FormulasàTrace Precedents

You will now see a series of blue arrows indicating which cells – or ranges – are feeding into that formula

And you can layer these – I can see that there’s an arrow pointing to a value on the top-right summary box (Max value for All). Selecting this value will add additional arrows relevant to this cell.

The Bold blue lines denote that a range is referenced (with a blue box around the range), whereas a slim blue line denotes a single cell is referenced.

If your formula references locations in different sheets / files, you will see a dashed black line

Double-Clicking this line will bring up a Jump-Box listing the external references, and allows you to jump to those locations

This feature is fantastic for understanding, or diagnosing, more complex formulas in your workbook

Selecting ‘Remove Arrows’ clears the screen of all arrows, or allows you to remove just Dependants / Precedents

Trace Dependants

Whilst Trace precedents allows you to know which cells impact a given formula cell, Trace Dependants does the opposite – it tells you which cells reference a given cell in its formula.

This is useful, as it tells you if a given cell – either on its own, or as part of a range, is referenced in any formulas

Select your cell, and select ‘Trace Dependants’ from the Formula tab

This will now show you any cells with formulas references this cell, even if that formula is actually referencing a range of cells, not necessarily that cell explicitly.

As with Precedents, if a reference exists on another sheet, you will see a dashed line

And again, to remove the arrows, select Remove Arrows from the Formulas tab

That’s all there really us to it. Incredibly simple, but immensely powerful tool to help debug, test, clean-up and audit your (or someone else’s!) spreadsheet.

As far as i can tell, this functionality is not native to Google Sheets, but can be replicated by creating a script – but that’s very out of scope for this article! (maybe another day!)