• 394K Members
• 11K Online
• 426K Conversations
SOLVED

Highlighted
New Contributor

# 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 highlight the cell A2 as row A has 3 or more blanks in a row. Following this A6 will highlight but the remaining A cells will remain not highlighted. If any advice can be given for the formula to use it would be amazing! Thanks E.g. below
8 Replies

# Re: Conditional format cells only that contain a sequence of blanks

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

# Re: Conditional format cells only that contain a sequence of blanks

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

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

# Re: Conditional format cells only that contain a sequence of blanks

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

# Re: Conditional format cells only that contain a sequence of blanks

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.

# Re: Conditional format cells only that contain a sequence of blanks

Sergei, you are brilliant! Thank you this is perfect

# Re: Conditional format cells only that contain a sequence of blanks

This involves a bit of lateral thinking to transform your problem into a form that is easy to solve.  Given the range, I defined a Named Formula 'signature' using Name Manager to refer to

= CONCAT( N(ISBLANK(range)) )

That gives a binary string with 1 for a blank cell and 0 otherwise.

The next step was to search the string for the occurrence of "111" using the named formula 'format?' which refers to

= IFERROR( SEARCH("111", signature ), 0 )

The conditional format then is based upon

= format?

A side benefit is that

= INDEX( range, format? )

is the first cell of the three or more consecutive blank cells.

# Re: Conditional format cells only that contain a sequence of blanks

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.

# Re: Conditional format cells only that contain a sequence of blanks

Peter, great idea with binary string

Related Conversations