Trace precedents is one of the most important auditing tools in spreadsheets today. Trying to figure out how someone elses’s model works? Trace precedents. Your own analysis REF’ing out? Trace precedents. Knowing if your formulas are correct and how they relate to each other is paramount for a spreadsheet user.
In this article, I’ll discuss how you should be tracing precedents in Google Sheets. There are two main ways to do trace precedents in Sheets, one that you should use for simple formula checks, and the other for complex formulas. Both do not require the use of a mouse to use and should cover 100% of your formula auditing needs. The two methods are:
Use “F2” for simple formula checks (< 2 precedent cells on other Sheet tabs)
Use the free SheetWhiz Chrome Extension for complex formulas with 2+ precedent cells on other Sheet tabs. Complex formulas also include when you need to dive multiple precedents deep (e.g., circular reference auditing)
If you use the framework above, you will be taking, in my humble opinion, the best approach to trace precedents in Google Sheets, using the right tool in the right situation that will enable you to be both fast and accurate when auditing your formulas.
Use the “F2 Method” for Simple Formula Checks (< 2 Precedent Cells on Other Tabs)
Pros:
It’s fast
Partial formula evaluation is a plus
Cons:
You can only look at one cell at a time
You cannot jump from precedent cell to precedent cell quickly
You cannot edit cells that are not the one that you’re on
You cannot view an additional precedents level deep (i.e., the precedents of the precedents)
Lots more arrow key clicks to evaluate complex formulas vs. SheetWhiz
Using “F2” is a powerful tool in Sheets. Similar to Excel, if you hit “F2” you can enter into the formula and all cells mentioned in that formula will be highlighted.
For many, this is where their use and knowledge of “F2” ends. However, if you hit “F2” when inside a formula and on top of a cell, it’ll take you to that cell. You’ll even be able to change the cell that you’re currently on by using the arrow keys to select the correct cell if the initial cell was wrong.
Hit “F2” again to refocus onto the formula and check other precedent cells. Super fast and super easy!
This also works on a Mac if you hit the “Fn” key and then the “F2” key on the upper row of the keyboard. You can also hit “Control + e” to do the same thing, though you’ll have to be clicked into the formula before you can use “Control + e”.
Here’s a short video of trace precedents in action (apologies in advance — video editing is not my strong suit!):
Finally, you can also evaluate portions of a formula once in the formula bar by highlighting certain sections of your formula. Handy if you have a lot of nested IFs and you’re trying to figure out which IF is miscalculating.
When to use:
Use this approach when you are evaluating simple formulas where you have 1 or fewer precedent cells located on another Sheet tab. It’s easy to hit “F2” to look into a formula that references cells only on the tab you’re on and you can hit “F2” on the cell not on the tab you’re on to jump to that cell if needed
Use this approach when wanting to evaluate certain aspects of a formula to understand where something is miscalculating
When not to use:
There are better ways to trace complex formulas (5+ precedent cells located on 2 or more tabs) with many precedent cells referencing multiple tabs. Using the “F2” method does not give you a full picture of the formula that you’re auditing, does not give you the ability to navigate quickly between cells in large formulas, nor does it give you the ability to edit cells beyond the one that you’ve chosen to look into. If you use this “F2” method for complex formulas, it will take you much longer than if you are using the tool mentioned below
Use SheetWhiz For Complex, Multi-Tab and Multi-Layer Formulas (2+ Precedent Cells on Other Tabs)
Pros:
View and navigate to every precedent cell in your formula, including arrays
Dive a level deeper to view the precedents of the precedent cells without having to rerun a command
Triggered using shortcut and navigable without using your mouse (up down arrow keys and Esc to close)
Cons:
Slower than the “F2” method
Pop-up may block some cells
Need to install the SheetWhiz Chrome Extension to run it
While the F2 method is great for quickly checking formulas, you’ll need the SheetWhiz Trace Precedents tool to do a comprehensive check
How to trigger trace precedents on SheetWhiz:
First, install the SheetWhiz Chrome Extension.
Second, reload a Sheet — the SheetWhiz logo should appear in the upper righthand corner of your screen:
Next, click the logo to view your shortcuts. You’ll see “Trace Precedents” as the first shortcut, triggered by holding down “Ctrl + Shift + [” on a PC or “Control + Shift + [” if on a Mac.
When you run the shortcut, you’ll see a window pop up with the cell that you’re on as the “home” cell with a list of all precedent cells beneath it. It may take some time to pop up if it is a large sheet.
You can then navigate this pop-up using the up and down arrow keys. You can also click on the “+” buttons located next to some of the cells to view the precedents of that cell (this requires a mouse click). Hit “Esc” to exit the view
Here’s a demo of Trace Precedents in action:
When to use:
While you can use SheetWhiz for all occasions, it is best suited for complex formulas (2+ precedent cells located on other tabs than the one you’re on) because it enables you to view all cells in the formula and navigate to each one quickly. It also allows you to dive deeper into specific precedent cells by viewing the precedents of those precedents
When not to use:
Simple formulas referencing cells in the same tab that you are in
In conclusion, the best way to trace precedents on Google Sheets is to use the F2 Method for simple & quick checks + SheetWhiz Trace Precedents for more complex auditing. Using a combination of tools to audit your formulas and debug your analyses will yield the best results because each has their pros and cons and knowing when to use which tool will help you level up your speed and effectiveness in Sheets