Conditional format for column headers where all cells are numbers

Question

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.

Answer 1

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.

Answer 2

The way I managed to do this, even though it will be very cumbersome if you have multiple rows that you need to check, is by using the following equation coupled with the Conditional Formatting feature (Format > Conditional Formatting):

=IF(AND (ISNUMBER(n), ISNUMBER(n1), ISNUMBER(n2)), True, False)

Where "n" is whatever cell you're checking.