Conditional Format Blank Cells
Anyone have ideas on how to use one rule to conditionally format all blank cells in a row if "x" is true?
Best Answers
-
You would have to set up multiple rules as conditional formatting is applied at the column level.
-
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).
Answers
-
You would have to set up multiple rules as conditional formatting is applied at the column level.
-
@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.
-
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 - very helpful, thank you!!
-
Happy to help. 👍️
-
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
-
@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.
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives