Conditional Formatting for Calculated Cells in Google Sheets
Want to provide at-a-glance conditional formatting on a Google Sheet to indicate which cells contain formulae and which contain static values, for example to different “default” calculated values from those which have been manually overridden? Here’s a tip for you.
-
Add the following Custom Function to your sheet:
function ISFORMULA() { return SpreadsheetApp.getActiveCell().getFormula().length > 0; }
-
Use this formula with Google Sheets’ Conditional Formatting “Custom Formula” mechanism.
Example
For example, to have calculated cells containing a “default” value fade away, which manually overridden cells are accentuated:
- Select the cells to be formatted, and click Format > Conditional formatting
- Under the “Format cells if” drop-down menu, click Custom formula is.
- Apply formatting to calculated cells:
- Click Value or formula and add the formula:
=ISFORMULA(A1)
, adjust the associated formatting properties to format the content in light grey italics. =NOT(ISFORMULA(A1))
, if you want to highlight cell which are not calculated from a formula.
- Click Value or formula and add the formula:
- Choose your preferred formatting properties.