I have a Table (as opposed to a Range) defined in Excel 2010, named Table1.

The table has blank rows separating groups of 3 to 5 rows with data. Note that not every column in every "non-blank" row contains data. However data exists in column B (named Table1[IdMap]) for every non-blank row.

I want to format the table (either using Conditional Formatting or static formatting) such that all of the all-blank rows have an off-white background color.

So far I've tried the following Conditional Formatting rules with no luck:

  1. Select Table1 (using Ctrl+A while the cell-marquee is in the table)
  2. Conditional Formatting > Rules > New Rule > Use a formula...
  3. "Format values where this formula is true":
    • =ISBLANK( $B:$B )
    • =ISBLANK( INDIRECT("Table1[IdMap]"))
    • =ISBLANK( INDIRECT("RC[-2]",0) )

I found the answer also on superuser: Using IF and ISBLANK statement to determine a range of cells if any of them have information in them.

Just use in conditional formatting =NOT(COUNTA($B:$B))

  • 2
    You actually don't need the IF, just =NOT(COUNTA($B:$B)) as you're just saying IF true, then true, else false. :) – Jonno Feb 25 '16 at 8:49
  • I'll edit the answer :). – Michthan Feb 25 '16 at 12:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.