Forum Discussion
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...
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.
UPDATE: incorrect information below: "The desired outcome is to highlight the cell A2 as row A has 3 or more blanks in a row. " Row 2 is the correct reference. Thanks
You may apply to your range in column A, starting from A2, conditional formatting rule with formula
=COUNTBLANK($E2:$O2)>=3
- 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.
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.
- Sergei, you are brilliant! Thank you this is perfect