Highlight Blank Cells

ker9 · 04/22/19

Greetings,

Is there a way to highlight ANY blank cells on used rows or rows where Col A = No?

My sheet has more than 50 columns.

TIA

Paul Newcome · 04/22/19

The only thing I can think of is having a separate conditional formatting set up for each column.

ker9 · 04/22/19

Hi Paul,

Thank you for your response. That is what I was afraid of. I guess I'll need to do an enhancement request and wait forever.

Andrée Starå · 04/22/19

Hi,

Maybe you could use Highlight Changes to make it easier to see.

Hope that helps!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

ker9 · 04/22/19

Hi Andree, thank you for your response but I would like users to more easily see blank cells that need to be completed rather than those that have changed.

Andrée Starå · 04/22/19

Happy to help!

I just thought this might help. I'll get back to you if I come to think of anything else.

Is it all columns you'd want to check or a select few?

Best,

Andrée

ker9 · 04/22/19

Hi Andrée,

All columns, thank you!

Debbie Sawyer · 04/22/19

If you are happy to take a step back 1 level - you could highlight a row where there is a blank entry (somewhere along it). Then all the user just has to manually is look across the row to see where the data is missing and populate it? This would be quicker than a conditional rule per column.

To do this count your columns so that you know how many entries a complete record would have, then

1) add 1 more column

2) in your new column, top row enter the following function =Count([First Column]1:[Last Column]1) - Substituting [First Column] and [Last Column] with your column names

3) copy this formula all the way down the column to the end of the data

4) click on the conditional formatting button

5) Add a New Rule that looks at the Column Value that you set up in step1, and if the result is less than the number of columns you actually have; then colour the entire row Red (or whatever highlight colour you want).

Any good?

Kind regards

Debbie Sawyer Consultant & Training Manager

debbie.sawyer@smarterbusinessprocesses.com

Debbie Sawyer · 04/22/19

Sorry - forgot to add that you can use 2 conditions in the conditional format rule, use the dropdown arrow on the LHS of the rules dialog box to add Condition 2. i.e. the column count being lower than the number of columns AND Col A = No.

Kind regards

Debbie

ker9 · 04/22/19

Hi Debbie,

There are multiple users and they will look at only the row(s) belonging to them (mainly using a report filtered to their name). Since there are so many columns, I wanted to highlight just the missing items (blank cells).

Once a row is complete (all cells in the row have something in them), it is marked as complete and is removed from the report. Every cell must have something in it, even if it is NA, to ensure that it was reviewed.

Thank you for your response.

Debbie Sawyer · 04/22/19

Ah - then - your first assumption of a condition per column will be required... Just think - once it is set up you won't have to do it again!

To speed up the process, you can Clone a conditional rule. That way you are just ticking the next column in the list and setting "Is blank" as the criteria, then again ticking the next column in the list on the apply to part of the rule. This is quicker than adding each new rule from scratch as you don't have to keep selecting the actual format options each time.

Good luck

Paul Newcome · 04/23/19

I've been thinking on this a bit. What if your first column (which could be frozen so that it stays on the left of the screen no matter how far over they scroll) is a cell that contains a list of all of the columns that are still blank for that row or at the very least, the next blank column?

In the example below, you will see the top three rows list the next blank column, while the last three rows list out all columns (could get pretty bulky with 50 of them though).

Would either of these help?

This can be fully automated with minimal setup. One additional column and one additional row. A formula in the column and the column names in the row.

ker9 · 04/23/19

Hi Paul,

It's an interesting idea to list the blank columns and yes, it could get very bulky. I will play with that a bit.

Perhaps only list columns if there are less than 5 (maybe could work?).

Thank you!

Paul Newcome · 04/23/19

You could list the NEXT 1, 2, 3, 4, or 5. That way there will always be something listed if there is at least one blank in the row. The basic setup would involve a text/number column. Then a helper row (for this example I will just put it in row 1). In that row you would list each of the column names. The formula to show ALL blank columns would be something along the lines of...

=JOIN(COLLECT([Column1]$1:[Column50]$1, [Column1]@row:[Column50]@row, ISBLANK(@cell)), ", ")

I'll have to work on showing just the first x number of blank columns, but this should help get you started in the meantime.

Christina Reid · 08/04/20

I know I'm doing something wrong but I can't quite get this to work:

=JOIN(COLLECT([Rep #]$1:[Column206]$1, [Rep #]@row:[Column206]@row, ISBLANK(@cell)), ",")

But instead of the column names returning to the helper column, I just get a bunch of commas

Highlight Blank Cells

Comments

Categories