Forum Discussion

SeanC3's avatar
SeanC3
Copper Contributor
Aug 11, 2019
Solved

Conditional format cells only that contain a sequence of blanks

Hi all, I need some help trying to nut out a conditional formating formula. I need to only highlight cells that contain 3 or more blanks in sequence in a row (range E2:O2) The desired outcome is to hi...
excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Aug 16, 2019

    SeanC3 

    To count max number of sequential blanks you may use

    =MAX(FREQUENCY(IF(($E2:$O2=""),COLUMN($E2:$O2)),IF($E2:$O2<>"",COLUMN($E2:$O2))))

    see in Column P attached. In general it shall be array formula (Ctrl+Shift+Enter), but I'm on Dynamic Array Excel now and can't test. Column A is highlighted using this formula.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Aug 11, 2019

SeanC3 

You may apply to your range in column A, starting from A2, conditional formatting rule with formula

=COUNTBLANK($E2:$O2)>=3

 

SeanC3's avatar
SeanC3
Copper Contributor
Aug 16, 2019
Hi Sergei, This formula is identifying rows with the required amount of blank cells, but is there a way to identify blanks in a sequence? Say row 2 has 4 blanks cells (I2:L2) but row 4 only has a maximum is 2 blanks in sequence, I need to identify only the rows that have 4 or more blanks in sequence. Is this possible? Thanks for your help.
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Aug 16, 2019

    SeanC3 

    To count max number of sequential blanks you may use

    =MAX(FREQUENCY(IF(($E2:$O2=""),COLUMN($E2:$O2)),IF($E2:$O2<>"",COLUMN($E2:$O2))))

    see in Column P attached. In general it shall be array formula (Ctrl+Shift+Enter), but I'm on Dynamic Array Excel now and can't test. Column A is highlighted using this formula.

    • SeanC3's avatar
      SeanC3
      Copper Contributor
      Aug 17, 2019
      Sergei, you are brilliant! Thank you this is perfect
      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Aug 17, 2019

        SeanC3 

        You are welcome. Forgot to say, array formula (CSE) if only you use it for the calculation in the cell. Within the rule you may use it as regular one, Conditional Formatting is smart enough to recognise array calculation.

Resources