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.

1. Add the following Custom Function to your sheet:

   function ISFORMULA() {
       return SpreadsheetApp.getActiveCell().getFormula().length > 0;
   }
   

2. 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:

1. Select the cells to be formatted, and click Format > Conditional formatting
2. Under the “Format cells if” drop-down menu, click Custom formula is.
3. 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.
4. Choose your preferred formatting properties.