Trace dependents is a key part of your spreadsheet auditing toolkit. Practicing good spreadsheet hygiene and trying to figure out if you can delete some cells? Trace dependents. Your teammate’s model has a random cell and you don’t know what it does? Trace dependents. Knowing how cells tie together is critical to building efficient, well-structured models and analyses.
In this article, I’ll discuss how you can run trace dependents in Google Sheets. There are two ways to do it:
Use the “Find and replace” tool method. This method takes significant time to do and will not find all edge-case dependents in a Sheet, but is native to Sheets.
Use Google Apps Script to automate the search. Don’t code? Use a free extension like the SheetWhiz Chrome Extension pre-built with trace dependents. This tool will return all dependents, including all edge-cases described below.
Out of the two options above, I would recommend using Google Apps Script or a tool like SheetWhiz since it’s much faster and more comprehensive vs. the “Find and replace” method, but I understand some folks may not be able to code or install Chrome extensions so I wanted to provide both methods as options in this article
Option #1: Use the “Find and replace” tool method (Sheets-native solution)
The idea behind the “Find and replace” method is that you will need to run “Find” multiple times with different variations of searches to find all dependents within a Sheet. There are a number of searches you’ll need to run to be comprehensive. Below are all the steps you’ll need to do and all the search variations you’ll need to run to do a thorough search:
1. Press “Cmd + Shift + H” on a Mac or “Ctrl + H” to bring up the “Find and replace” tool.
2. Put the cell you want to look up including the name of the Sheet (e.g., Sheet1!A1) into the Find cell space
3. Make sure you’re searching “All sheets” and that “Also search within formulas” is checked
4. Click “Find” to find all instances of that cell. You’ll have to hit “Find” multiple times if there are multiple instances of the cell in your search. Click “Find” until “No more results, looping around” is displayed at which point you’ll have gone through all the cell instances. All the cells that are returned are dependent on the cell you’re looking up
5. Rerun variations of how this cell might appear to capture all dependent cells. Specifically in my example, you’d want to run the following searches
Sheet1!A1
The name of the cell if it is a named cell
A1 (note you should select “This sheet” instead of “All sheets” in the window to avoid A1 cells in other sheets)
Rerun all of the above but with all iterations of column and row locking (i.e., Sheet1!$A1, Sheet1!$A$1, Sheet1!A$1, $A1, $A$1, A$1)
6. Check for your cell within arrays
If steps 1–5 return no results, you may opt to check if the cell you’re on appears in any arrays. This will take longer but will be more comprehensive. My perspective is if you have a result just by searching for the cell (i.e., Sheet1!A1, A1, name of the cell + cell locking variations), you may not need to pull ALL the dependents as you already know you can’t delete that cell unless you rework your analysis. However, if you need to get all dependents or don’t return any dependents in the searches above, you can check to see if your cell appears in any array in your spreadsheet
The way you go about checking arrays depends on your analysis.
If your analysis references the tab you’re on in many other sheets without too many arrays, you should also search for “:” to find all arrays in the spreadsheet and go through each result individually to see if Sheet1!A1 appears in the array. You would go this route because there’s less arrays in your analysis than sheet references meaning there’s less results to parse through
If instead your analysis has a bunch of arrays, you might instead want to look for the name of your sheet (i.e., Sheet1!) in the entire spreadsheet and view each instance where this appears. Similar to the prior bullet, this way you minimize results you’ll need to go through in the instance you have more arrays in your sheet than Sheet1! references
Note, you’ll still have to search for “:” in the sheet you’re currently on as arrays in this sheet won’t have the sheet name (e.g., Sheet1!A1 is abbreviated as A1 when in Sheet1).
Additionally, sometimes arrays don’t have letters or numbers which is why you must search only the sheet name or “:”. For example, Sheet1!A:C or Sheet1!1:3 are possible cell names . Both include Sheet1!A1 but won’t be returned just by searching for the cell
Of the two approaches, in my experience, searching for the sheet name is often faster as arrays are a pretty common Google Sheets concept!
Once you’ve done this, you’ll have executed trace dependents on Google Sheets using native functionality! You should now have returned all dependents of the cell you’re on
As you can see this is a fairly convoluted way to trace dependents, which is why I actually recommend the 2nd option which is using Google Apps Script (via a Chrome extension) to automate the process
Option #2: Leverage Google Apps Script via an Add-On (e.g., SheetWhiz)
As a better alternative to “Find and replace,” you can use a Chrome extension that leverages Google Apps Script to search and return all the dependent cells in your spreadsheet. The add-in I’d recommend is called SheetWhiz. SheetWhiz runs trace dependents for you. You can install it directly from the Chrome store here. Their website is https://sheetwhiz.com/ if you need more information on privacy / security policies, etc.
Basically, the tool runs through the logic described in Option #1 above automatically and returns the results in a window so you can easily see all the results (vs. having to click through each result like when using “Find and replace” natively)
Additionally, you can trigger and navigate the tool without using your mouse. The default command to run trace dependents is Ctrl + Shift + ] on a PC and Control + Shift + ] on a Mac, which is similar to the Excel shortcut for trace dependents. 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 dependent cells beneath it. You can even navigate to the dependent cells by using the up and down arrow keys. Close the window by hitting “Esc”. You’ll never have to use your mouse!
I’ve attached an image of the trace dependents window to my response. As you can see, it’s much easier to see all dependents vs. using “Find and replace” and is, in my opinion, the best way to trace dependents in Google Sheets
Below is a sample video of the tool in action:
In conclusion, there is a native way to trace dependents on Google Sheets and there is a way to do it via Google Apps Script. You can use a tool like SheetWhiz to run the script for you, which also provides you convenient shortcuts for you to trigger the script (e.g., Ctrl + Shift + ] on a PC). I would 100% recommend the App Script method given the difficulty of using the native method in Google Sheets. However, I understand some folks are not able to use extensions and do not have the time to write a Google Apps Script, which is why I went into explicit detail of how to do this through Google Sheets’s interface. Good luck on your tracing and I hope you never experience deleting a cell and breaking your entire model!