Conditional Formatting not applying to entire column

Ashely B

I have a sheet where I want to highlight the entire column if there are blank cells within that column. My current rule is only applying to the first few rows even though the rule is set to apply to the column. I can't find any hidden data so I am not sure why the rule isn't being applied to the entire column.

Nick Korna

The conditional formatting only applies to rows with data in - in this case it will only show the rows with the blank NSE Requestor in yellow.

In terms of finding them, a filter will likely be more useful to find the blanks if you have many rows once data is entered.

Ashely B

I'm not sure I understand. I guess I should mention that the goal of this sheet is to highlight all cells in selected columns to indicate to the NSE Requestor that they must enter data into the field. Essentially, all but 3 columns will need to be highlighted.

Andrée Starå

Hi @Ashely B

I hope you're well and safe!

To make that work, you would need to use a so-called helper column (or multiple, depending on the specifics) and have a formula looking for blank cells and then populate the whole row if there are any blank cells. Then we'd reference that in the Conditional Formatting Rule.

Make sense?

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Ashely B

@Andrée Starå I can try it. How would that work?

Nick Korna

For a sheet like this:

Checking only the columns after NSE requestor:

=IF(OR(ISBLANK([Request Date]@row), ISBLANK([Project LLC Name]@row), ISBLANK(Portfolio@row), ISBLANK([Asset Manager (Completed by Asset Manager)]@row)), "Yes", "No")

Yes/No in the formula can be other values, these are just then used in the conditional formatting:

Which will then give you something like this:

Obviously you can modify the formula to check more/less columns, and/or have the conditional formatting highlight more/less as well (or the whole row should you prefer)

Hopefully this is enough to demonstrate how a helper column would work though.

If your NSE Requestor column is a contact list, it can be used in automation to send people a reminder that details need filling in.

Andrée Starå

@Ashely B

Happy to help!

I saw that Nick had answered already!

Let me know if I can help with anything else!

Best,

Andrée

Mattisphere

If there is one thing Smartsheet is particularly good at, it's making you appreciate the basic things we do in Excel every day, like conditional formatting with relative cell references or formatting cells based on an absolute cell reference without creating helper columns for everything.