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.
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
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.