< Go Back

How to Apply Conditional Formatting to Blank Cells

★ 25 minute read

Using the conditional formatting rule you can easily consistent formatting across a large number of cells/ rows/columns. One such example is a conditional formatting rule when the cells do not contain any value. There are two methods to conditionally format blank cells in Excel, which we are going to discuss here.

Using Built-in Rule

You can easily format blank cells in Excel by choosing a built-in rule type. Select your data range, click on Conditional Formatting Menu and select “New Rule”.

Choose the rule type “Format only cells that contain” and then select “Blanks” from Format Only Cells with options. Select the Format type to preview, like Fill, Font or Border, and apply to take effect, and you will get all blank cells formatted in selected data range as you can see the result in the second picture.

Using this method you can highlight both “blank” and “visually empty” cells in your data range.

For example, If your cell contains any formula that returns in an empty string = IF(A2=1,”OK”,” ”), then such a cell will be “visually empty”, but not “blank” as it returns an empty string with space. Whatever, the cell is blank or empty, you can highlight these cells by applying this method.

Using Custom Formula

You can also format blank cells in selected data range by using a formula to determine which cells to format. There are multiple ways to conditionally highlight the desired data range using this rule type.

If you want to highlight blank cells in selected data range A2:C13 using formula, then apply any of the following formulas in conditional formatting.

=LEN(A2)=0

The LEN function returns the length of the text as a number. The formula LEN(A1)=0, will return TRUE for “blank” cells only, and not for “visually empty” cells

= ISBLANK (A2)

The ISBLANK function returns the TRUE where cell blank. The formula ISBLANK(A2) will return TRUE for “blank” cells only, and not for “visually empty” cells.

For example, if your cell contains any formula that returns in an empty string = IF(A2=1,”OK”,” ”), then such a cell will be “visually empty”, but not “blank” as it returns an empty string with space. Therefore, both LEN and ISBLANK functions will not see these cells as blank, and will not highlight those cells.

When you use a formula to apply conditional formatting it’s important that conditional formatting formulas be entered relative to the active cell in the selection, which is A2 in this case. This address will be updated when the formula is applied, so conditional formatting will be applied to each cell of the selected range.

If you want to highlight cells of a certain column if any corresponding cell in another column is blank, first you need to select the data range that you want to conditionally highlight and then use following the formula in conditional formatting.

=$A1=""

This formula will format selected cells if a corresponding cell in a Column is blank.

In below example, if you want to highlight those invoices in column A, where corresponding Date cell in Column B is blank, then select data range in column A, and apply following formula in conditional formatting. This formula will highlight those cells in column A only where corresponding Date cell in column B is blank as shown below.

=$B2=""

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

9
Leave a Comment

avatar
8 Comment threads
1 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
9 Comment authors
RaghavEmmaJamesAnnaSteph Recent comment authors
newest oldest
Jon
Guest
Jon

I need to input dates in the dates column and name in the other I would like the color to highlight rows for quarters per the 2018 color chart, can this be done with conditional formatting?

Kerri
Guest
Kerri

Hi! I am trying to do a conditional formatting where if one column doesn’t follow the in another column to the value in the third column it will highlight it

Bob
Guest
Bob

I am having an issue trying to figure out some conditional formatting for a spreadsheet. It is more than just one function. It’s either an ifs string or and/if string I think. But you are the experts. Can you help??

Cammy
Guest
Cammy

Yes hello Im looking for help on conditional formatting I currently have a conditional format for D9>D32 and so on from D9:S20. I need to add an if statement that references cell D4 if the first four characters are 1358 for the rule to apply can you please assist?

Steph
Guest
Steph

Is there an actual function that changes cell colour rather than Conditional Formatting? It wouldn’t work in this situation. I basically have an IF statement and I want the TRUE result to be the colour of the cell changing. Ive tried setting it to 1, then setting a rule where all 1 become that colour, but nothing happens. I’ve tried it with and without the quotations.

Anna
Guest
Anna

This was helpful! I’m looking for a formula that I can set up for conditional formatting to highlight an entire row of cells based on the condition of two specific values. I need this rule to have 8 variant outcomes as well, differentiated by color.

James
Guest
James

Hello. I’m looking for a conditional formatting formula to highlight a range of cells based on value of another cell range. I’m using Google Sheets.

Emma
Guest
Emma

Hi,

I have a 6 month calendar which goes across in 1 row (September- February). I am trying to highlight certain blank cells underneath a certain date. Is this possible?

Raghav
Editor
Raghav

Hey Emma, thanks for you question! Myself and other Excel experts who write these blogs are also online and available to help. Ask me your question here: http://www.excelchat.co and I’ll be happy to help out!