Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
All articles CONDITIONAL FORMATTING How to Apply Conditional Formatting to Blank Cells

How to Apply Conditional Formatting to Blank Cells

Read time: 32 minutes

We can use the conditional formatting approach for “cells that do not contain any value or for blank cells”. This step by step tutorial will assist all levels of Excel users on how to apply conditional formatting to blank cells.

Apply conditional formatting to blank cells

The aim of this exercise is apply formatting to the cells in Excel that are blank. For example, in Figure 1, the final result is to have all of the blank cells highlighted yellow. Here is how you can accomplish this:

Figure 1 – Final result

Setting up the Data

  • First, we will set up our data by inputting the values for the items in the cells we choose.
  • Our data is shown below.

Figure 2- Setting up the Data

How to Apply Conditional Formatting To Blank Cells Only

We can see from our data in Figure 1 that the following cells are blank:

  • A5 , A9, B4, B7, B11, C7 and C8

We can apply conditional formatting to all of these blank cells to show certain colors, patterns, etc. We can do this by following the simple steps outlined below.

  • First, we have to select the data range of interest. The data range is from Cell A4 to Cell C12.
  • Next, we have to click on conditional formatting as shown in figure 2 and click on the drop-down arrow.

Figure 3- How to Apply Conditional Formatting for Only Blank Cells

After we have clicked on the drop-down arrow, we will see a New Rule. Click on the New Rule and a dialog box called New formatting Rule will show up as shown in Figure 3.

Figure 4- How to Apply Conditional Formatting for Only Blank Cells

  • Where you see Select a Rule Type as shown in Figure 3, click on Format only cells that contain.
  • We also need to change the Rule Description to format only cells with blanks by clicking the drop-down arrow.
  • We can use a range of conditional formatting options for the blank cells by clicking on Format adjacent to the No Format Set. For this example, let us use the Yellow color to fill cells that are blank. We will click on OK after selecting our choice.

Figure 5- How to Apply Conditional Formatting for Only Blank Cells

As we can see from Figure 5, all of the blank cells are now conditionally formatted in Yellow color.

Figure 6- Output Showing the Result of Conditionally Formatted Blank Cells

How to Apply Conditional Formatting Using a Custom Formula

  • Syntax

=ISBLANK(VALUE)

  • Syntax

=LEN(VALUE)

Explanation of formulas

A blank cell can contain a character such as space (just like the spaces we give when typing). We will type a space character into one of the blank cells (Cell A9).

The ISBLANK function returns as TRUE when a cell doesn’t contain “anything” and as FALSE when a cell contains at least one character.

The LEN function returns as TRUE for blank cells without any character and FALSE for cells with a character.

When using a custom formula for conditional formatting, it is essential that we enter the formula relative to the active cell in the selection. In this case, the cell is Cell A4. This will automatically update across the range where conditional formatting is intended to be applied.

Still applying the steps above, in figure 3, we will click on Use a formula to determine which cells to format.

Figure 7- How to Use a Custom Formula for Conditional Formatting

We will click on OK afterward with the result being Figure 7. Remember that Cell A9 IS NO LONGER EMPTY BUT CONTAINS A SPACE CHARACTER.

Figure 8 – Output Showing the Result of Conditional Formatting with a Custom Formula

Using the same approach, we can input this string =LEN(A4)=0 into the Rule Description of figure 6 and we will arrive with the same result as figure 7.

How to Conditionally Format a Column Based on the Result from another Column

We can conditionally format the car number column based on the presence of a blank or visually empty cell (cell with at least a character). Cell A9 is visually empty because it has a space character. To do this, we can use the string below:

=$B4=””

We will select the car number column range (Cell A4 to Cell A12), follow the same procedure, and just like figure 6, we will input the string.

=$B4=””

The result in Figure 8 shows that cells in Column A have been conditionally formatted if they have corresponding cells on Column B (Date of Arrival) that are blank.

Figure 9- Output of Conditional Formatting Based on the Blank Cells in Column B

If you are having any difficulty applying this, we have experts who are willing to help.

Are you still looking for help with Conditional Formatting? View our comprehensive round-up of Conditional Formatting tutorials here.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Solution examples
I have a cell that has a working vlook formula in it. When it looks up source data it returns blank. That is fine. I now want to use an IF formula to say if that cell is blank leave the other cell blank and if not then show value in different cell. The issue I have is that when the IF formula looks up the cell it reports back FALSE due to the vlook up formula being in the cell. I want the IF formula to show nothing instead of FALSE. Can you help?
Solved by Z. E. in 60 mins
Use the Vlookup Function to complete the "employee" column of table 2. Use "job Id" from table 2 as your lookup_value(s) and table 1 as your reference.
Solved by C. H. in 16 mins
If a cell in another sheet is populated I need a vlookup done. If the cell is not populated I need the cell to return blank.
Solved by T. D. in 60 mins
I am trying to make a chart that turns a week range red if nothing is entered in the range. If something is entered then I would like it to turn green. Please Help
Solved by E. U. in 43 mins
I need a check box to show/hide an answer of an if function
Solved by Z. U. in 23 mins

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
Admin
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!

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co

Did this post not answer your question?
Get a solution from connecting with the expert

Another blog reader asked this question today on Excelchat:

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc