Microsoft Excel – conditionally formatting a whole column range if any cell in that column is blank

This is a question that we were posed at training recently.

First select the data area to be formatted. In the example here, I have selected B2:D7.

Column conditional formatting

Then on the Home tab, in the Styles group, from the Conditional Formatting dropdown, click New Rule.

Conditional formatting dialog box

Select Use a formula to determine which cells to format.

The formula in my case is:


Click Format.

Select your required formatting, then click OK twice.

The columns containing blank cells will be highlighted.

column conditionally formatted

Find out more about our Microsoft Excel training in the London area.

Find out more about our Microsoft Excel training in Wiltshire, Dorset, Hampshire and Somerset.


About jdonbavand

I am a trainer of Microsoft Office, Microsoft Project and Crystal Reports. I have called my blog "If Only I'd Known That...." because I hear it so many times in training sessions. In fact, if only I had a £100 for every time someone says "If only I'd known that." ....
This entry was posted in Microsoft Excel, Microsoft Excel 2007, Microsoft Excel 2010, Microsoft Excel 2013, Microsoft Excel 2016 and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s