Go Back

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.

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

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

array projects information from one sheet to another, conditional formatting has been painted from master sheet to slave, and works in any differential situation where cell =1x>2x highlight red.. but i have a rule to highlight all blank cells blue.. and it will not apply to the sheet!
Solved by K. B. in 20 mins
Formula help: =COUNTBLANK(offset(Inglewood!C$2,,MATCH(D3,Inglewood!$C$1:$N$1,0)-1,counta(Inglewood!$A:$A)-1)) This is helping me have conditional formatting based on empty cells from another tab. I want to change the range of data that is being seen. (i.e. instead of C2:C47 I want it to be C11:C47)
Solved by V. U. in 22 mins
I need a formula for conditional formatting a cell based on a percentage of another cell, This is for a project Timeline Manager chart so would like the formula to auto copy when adding new projects. C1 = blank H3 = percentage data bar 0-100% I would like: C1 to input "Not Started" if Percentage of H3 is 0% C1 to input "In Progress" if percentage of H3 is between 1-99% C1 to input "complete" if percentage of H3 is 100%
Solved by I. J. in 21 mins
I want to white out 3 cells, based on the value in another cell. E.G., if the "other" cell is blank, then white out the 3 cells. The cell value can be "Y" or blank, as shown below. I have tried using conditional formatting using a formula and cannot get it to work. Here is a subset of the spreadsheet. Quickbooks y 2,000 1,500 150 350 SAGE 80 2,000 1,500 150 350 SAGE 2 2,000 1,500 150 350 0 Netsuite Y 3,000 2,000 150 850 Distribution One
Solved by I. D. in 29 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
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!