Instead of repeating the same value multiple times, sometimes we may leave blanks in spreadsheets. In such a case you may face issues in highlighting. I mean you may not be able to highlight the blank cells using a value from the cell above.

For example cell A1 contains the value “Cucumber” and cell A5 contains the value “Lettuce”. In this case actually the values in A2:A4 is “Cucumber” but left blank to avoid repetition.

When I want to highlight all the rows containing “Cucumber” in column A, actually I want to highlight the rows A1:A4. It’s not normally possible as some of the cells are blanks.

In this post, how to highlight blank cells using a value from the cell above, you are going to learn to code the conditional format custom rule (formula) for this.

There are a total of 4 steps involved. Before coming to that here is the sample data and the highlighting that we expect.

The conditional format rule for the range A1:A10 highlights the rows including blanks based on the criterion in the drop-down in cell D1. Instead of drop-down, you can directly type the criterion in cell D1.

The formula that we are going to code is for infinite (open) range, for example, A1:A, not limited to A1:A10.

Step # 1: Replacing Blank Cells with the Text from the Cell Above

Normally we can’t highlight blank cells using the values from the cell above. There is no such magical formula.

We should virtually replace the blank cells using the text from the cell above. Here we must replace A2:A4 with “Cucumber” and A6:A10 with “Lettuce”. How?

Insert the following formula in cell B1.

STEP 1 FORMULA

=ArrayFormula(
     if(row($A$1:$A)<=MATCH(2,1/($A$1:$A<>""),1),
        lookup(
           row($A$1:$A),
           row($A$1:$A)/if($A$1:$A<>"",TRUE,FALSE),
           $A$1:$A
        ),
     )
)

For formula explanation, read this post – Array Formula to Fill Blank Cells With the Values Above in Google Sheets.

Actually, the formula above will only fill “Cucumber” in A2:A4. Because the formula considers cell A5 as the end of the range.

Double click cell A11 and insert a white space by tapping the space bar key. So the formula can understand that the range is A1:A10 and fill “Lettuce” in A6:A10.

Note: If your data is in the cell range A1:A100 and the last value is in cell A95, insert a white space in cell A101.

For example, the value in the cell A95 is “Beetroot”. To highlight the range A95:A100, you should insert any character (for example a white space) in cell A101.

Step # 2: List Row Numbers of Matching Criterion

Some of you may think that you can now highlight A1:A4 if the criterion is “Cucumber” and highlight A5:A10 if the criterion is “Lettuce”.

If you use column B as a helper column, then you are right. But without using column B, as far as I know, it’s not possible in conditional formatting.

We can’t highlight certain rows matching an expression (output of another formula [step 1 formula]).

But we can do one thing. We can adopt row numbers matching in conditional formatting. Please follow the below steps.

Steps:

Enter the criterion in cell D1. I have used the menu Data > Data validation and the “criteria” in that as the following “List of items”.

Cucumber,Lettuce

Note: You may just type the criterion “Cucumber” in cell D1. That will be enough for our test. It’s not necessary to create a drop-down.

In cell E1, enter the following IF logical test to return the row numbers of the rows containing the criterion “Cucumber” in A1:A10.

STEP 2 FORMULA

=ArrayFormula(if($B$1:$B$10=$D$1,row($B$1:$B$10),))

To highlight blank cells using the value from the cell above, we can now follow row number matching. The next step explains this.

Step # 3: Row Number Matching to Highlight Blank Cells Using Value from Cell Above

We have multiple row numbers (1 to 4) to match in cell range E1:E4 as per the current criterion in cell D1. If the criterion is “Lettuce” then the row numbers (5 to 10) to match will be in E5:E10.

To match multiple row numbers and return the Boolean TRUE value, we can use the Regexmatch function.

Syntax: REGEXMATCH(text, regular_expression)

The “regular_expression” is the row numbers above. But it should be formatted as a “regular_expression”.

I mean format the row numbers as ^1$|^2$|^3$|^4$ for “Cucumber” and ^5$|^6$|^7$|^8$|^9$|^10$ for “Lettuce”. The following formula in cell F1 does that.

="^"&TEXTJOIN("$|^",true,$E$1:$E$10)&"$"

The “text” argument is row number formatted as text by adding a null character to it;

row(A1)&""

Here is the Regexmatch formula. Insert it in cell F1 and to test it, drag down (copy-paste) until F10.

STEP 3 FORMULA

=REGEXMATCH(row(A1)&"","^"&TEXTJOIN("$|^",true,$E$1:$E$10)&"$")

The formula returns TRUE against all the rows containing the criterion “Cucumber” in B2:B.

Actually, the above formula in cell F1 is enough to highlight blank cells using the value from the cell above in Google Sheets.

Because in the Regexmatch formula the cell reference in the “text” argument is relative and all the cell references in the “regular_expression” are absolute.

That means in Conditional formatting, relative reference has the drag-down effect in the selected range.

I mean if the range is A1:A10, the F1 formula will be enough for all the rows. No need to use the formulas in F2, F3…F10 individually.

Related: Relative Reference in Conditional Formatting in Google Sheets.

The formula will highlight the rows return TRUE. But we won’t use the F1 formula as it is to highlight blank cells using the value from the cell above in Google Sheets! Why?

Let’s combine the formulas in the above steps and use them to avoid helper columns.

Step # 4: Combo Formula Rule

Let’s replace the cell reference in step 3 formula with the corresponding step 1 and step 2 formulas.

Replace $E$1:$E$10 with the step 2 formula. While replacing, we can exclude the ArrayFormula from the step 2 formula.

=REGEXMATCH(row(A1)&"","^"&TEXTJOIN("$|^",true,if($B$1:$B$10=$D$1,row($B$1:$B$10),))&"$")

Replace row($B$1:$B$10) with row($B$1:$B).

=REGEXMATCH(row(A1)&"","^"&TEXTJOIN("$|^",true,if($B$1:$B$10=$D$1,row($B$1:$B),))&"$")

Then in $B$1:$B$10=$D$1, replace $B$1:$B$10 with the step # 1 formula. Here also we can exclude the ArrayFormula.

FINAL FORMULA (wrapped with ArrayFormula to compensate the earlier removed Array Formulas).

=ArrayFormula(REGEXMATCH(row(A1)&"","^"&TEXTJOIN("$|^",true,if(if(row($A$1:$A)<=MATCH(2,1/($A$1:$A<>""),1),lookup(row($A$1:$A),row($A$1:$A)/if($A$1:$A<>"",TRUE,FALSE),$A$1:$A),)=$D$1,row($B$1:$B),))&"$"))

Only keep the values in A1:A10 and the criteria in cell D1. Remove all the formulas.

Just copy the above formula. Let’s see how to use it to highlight blank cells using the value above in Google Sheets.

Final Step – Inserting the Combo Formula to Highlight Blank Rows Using Value Above

Steps:

1. Select A1:A or for entire row select A1:1000. I’m selecting A1:A10.
2. Open Format > Conditional formatting.
3. You would see your selected range in the field below “Apply to range”. In my case, it’s A1:A10.
4. Under “Format rules”, open the drop-down menu to select “Custom formula is”. Below that you will see a blank field. Insert our final formula and click “Done”.

Change the criterion/condition in cell D1 and see how the highlighting shifts rows!

That’s all about how to highlight blank cells using the value from the cell above in Google Sheets.

Thanks for the stay, enjoy!

Example_18720