Google Sheets Conditional Formatting – Conditional Format for Column Headers Where All Cells Are Numbers

conditional formattinggoogle sheets

I'm trying to color the header if all of the cells in that column are numbers. Blank or non-numbers should break this.

I have:

custom formula: =ISNUMBER(A3:A)

But it styles the cells instead. Not sure how to specify the header cell within this formula.

Best Answer

Apply conditional formatting with custom formula

=arrayformula(sum(N(not(isnumber(A3:A)))))=0

This can be applied even to a range of headers such as A1:E1; the formula, entered as above, will be interpreted in a relative way for other columns.

Explanation

  • not(isnumber(...)) is true if the entry is not a number
  • N converts logical true/false to integers 1 or 0
  • sum adds these.
  • arrayformula makes sure the entire range is handled
  • the sum is equal to 0 if all entries are numbers.

Note

Dates and times are considered numbers, because internally, they are. For example, the date 05/26/2016 is the number 42516 formatted as a date.

Related Topic