23 August 2021

How to expose inputs (editable values) in a Google Sheets model

Excel is the most successful programming environment in the world. No other program has had more people write code in it than Excel (or more generally spreadsheet software). It’s deceptive, because you can write all sorts of things, but even =SUM(A1:A10) is code!

This also means that it’s a real wild west of both visual design and coding style. As a result, whenever someone sends me an Excel model, I load it up in Google Sheets and then click around to figure out how they put it together, what’s a function, and what’s an input (i.e., the values I’m meant to change). A really thoughtfully built model will have all the inputs styled in the same way, so you can quickly see which numbers are for changing and which get computed for you, but in my own experience this is not super common.

Here’s a quick way to highlight potential inputs in a Google Sheets model by yourself!

1. Highlight all the cells (ctrl+a on windows or cmd+a on mac)
2. From the menu click Form > Conditional formatting
3. This will open up a “Conditional format rules” sidebar (you may have to click “+ Add another rule” if there are pre-existing ones)
4. Enter the following
   1. Apply to range: should be pre-filled with something like A1:Z1000
   2. Format cells if: “Custom formula is”
   3. Formula: =AND(NOT(ISFORMULA(A1)), ISNUMBER(A1))
   4. Formatting style: up to you and this may depending on existing styling, but I like italics, red, no fill
5. Hit done!

👁 conditional formatting example

Tada, that’s it! Some extra notes:

• The formula for this rule matches cells that are numbers, but not formulas.
• The ISNUMBER specifically matches numbers, which includes dates, but does not include numbers in quotes, e.g., ISNUMBER("23") evaluates to false.
• The usage of “A1” in this formula is basically a placeholder and it checks all cells. This feels clumsy and confusing to me, but it works!
• I generally use Google Sheets, but Excel has a bunch of other bells and whistles like “Go to > special” and an option to highlight formulas or also a “Trace precedents” and “Trace dependents” option.

I hope this helps you out the next time someone sends you an Excel model!

Peter Coles

is a software engineer living in NYC who is building Superset 💪 and also created GoFullPage 📸
more »