I am making a spreadsheet with a bunch of variables I want to change and some formulas that use those variables. I want to use conditional formatting (probably?) to color any cells which are "standing alone" or not affecting any other cells or not referred to in any other cells. Basically, I know the variables I need to include to make the calculations I want to make for this particular spreadsheet, but I don't yet know how each of those variables factors into the calculations I want to make. And I want to tackle the problem of how to incorporate those variables one by one. So I want to color the cells that represent "unused" variables so I can work my way through each variable and figure out how it needs to be used in the calculation.

Here's an overly simplistic and perhaps quite odd example:

  • Bunnies I have now: 4
  • Time horizon (in years): 5
  • Bunnies I want to have in time horizon: 20
  • Bunnies I need to acquire per year: X

Let's just say I have X as a formula properly set up to use the "Bunnies I have now" and "Bunnies I want to have" factored in, but I'm not sure how to factor in the time horizon just yet, so I want to color the time horizon cells in order to remember that I need to factor them in. This example has three variables and one calculation, but pretend I have 20 variables (which are consequently harder to track).

Does anyone know how to do this? How do you color a cell if it is not referred to in any other cells? Thanks in advance.

Here is the history of the searching I did before asking this question (per request from commenter):

search history

  • I'm not sure that this question is on-topic since it essentially a question about how to use Google Sheets. But if you have a cell that you want to set the background colour, then select the cell and use the "Fill color" icon in the menu bar.
    – Tedinoz
    Apr 12 at 0:41
  • @Tedinoz thanks. I'm aware I can do it that way, and if I were only dealing with a few variables then I might do it that way. But I'm pretty sure there's a way to do this with a formula, and doing it "by hand" sort of defeats the purpose of gaining confidence that my formulas are actually using the data I put in other cells. Apr 13 at 1:29
  • Please show what you tried and add a brief description of your search efforts as is suggested in How to Ask.
    – Rubén
    Apr 13 at 15:31
  • @PeterBentley In the Excel world, this is known as "auditing dependants". To the best of my knowledge it is not a feature of Google Sheets per se. OTOH, there appear to be some Apps Script solutions, but you would know this from your research.
    – Tedinoz
    Apr 13 at 19:39
  • Does this answer your question? Test if value of cell is used in a formula
    – Tedinoz
    Apr 13 at 19:40