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.