Conditional Format Blank Cells

ker9

Anyone have ideas on how to use one rule to conditionally format all blank cells in a row if "x" is true?

Paul Newcome

You would have to set up multiple rules as conditional formatting is applied at the column level.

Paul Newcome

It is in the hamburger menu (three horizontal lines) in the top left corner. Click on that and then on "Product Feedback".

In the meantime... You could insert a helper row (not a helper column) and then enter the column names into this row in each column. Then you can use a JOIN/COLLECT in a helper column to list out all columns that have a blank. Once you have the list on each row that shows which (if any) columns are blank, you can use a CTRL+F to search that helper row to jump to the appropriate column.

(Example formula below assumes "helper row" is on row 1)

=JOIN(COLLECT([First Column]$1:[Last Column]$1, [First Column]@row:[Last Column]@row, @cell = ""), "delimiter of choice")

Use conditional formatting based on this column to highlight which rows have a blank, or even apply a filter based on this column to only show rows that have data in it (meaning some other column in that row is blank).

Paul Newcome

You would have to set up multiple rules as conditional formatting is applied at the column level.

ker9

@Paul Newcome - thank you. Unfortunately there are over 50 columns, which is why I would like to highlight those that are blank. I'll send an enhancement request if/when I figure out where they moved it to.

Paul Newcome

It is in the hamburger menu (three horizontal lines) in the top left corner. Click on that and then on "Product Feedback".

In the meantime... You could insert a helper row (not a helper column) and then enter the column names into this row in each column. Then you can use a JOIN/COLLECT in a helper column to list out all columns that have a blank. Once you have the list on each row that shows which (if any) columns are blank, you can use a CTRL+F to search that helper row to jump to the appropriate column.

(Example formula below assumes "helper row" is on row 1)

=JOIN(COLLECT([First Column]$1:[Last Column]$1, [First Column]@row:[Last Column]@row, @cell = ""), "delimiter of choice")

Use conditional formatting based on this column to highlight which rows have a blank, or even apply a filter based on this column to only show rows that have data in it (meaning some other column in that row is blank).

ker9

@Paul Newcome - very helpful, thank you!!

Paul Newcome

Happy to help. 👍️

Ashley Heath

Paul -- could you provide screenshots of your example? I'm trying to put this in place using the helper row & column and having issues with the Join(Collect) formula

Paul Newcome

@Ashley Heath You would insert a new row at the top of the sheet. In this row you would manually enter each of the column names. You would then use the above formula to pull in the list of columns that have a blank cell on that row.