Conditional Formatting for Blank Cells

Updated on December 27, 2023
Article byWallstreetmojo Team
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

How to Apply Conditional Formatting for Blank Cells?

We can access the conditional formatting for blank cells from the “New Formatting Rule” of the “Conditional Formatting” tab and the cells containing blank values, the second option. In other words, it means a certain cell that does not have a value assigned to it. For example, it does not have a numeric, alphabetic, or character as a value. It is just a blank.

Examples

You can download this Conditional Formatting Blank Cells template here – Conditional Formatting Blank Cells template

Example #1 – General Method to Conditional Format for Blank Cells

We will consider the following data given below.

data

The above data shows that we have marks for five students but not for every subject. That is because some of the cells are left blank.

  1. We must first select the data range in which we want to use conditional formatting. In the “Home” tab, under the “Styles” section, click on “Conditional Formatting,” Then, click on the “New Rule.”


    conditional formatting To blank cell 1-4

  2. When we click on the “New Rule,” another wizard box appears, the “New Formatting Rule” tab.


    conditional formatting To blank cell 1-5

  3. Then, we must click on the second option, “Format only cells that contain.”


    conditional formatting To blank cell 1-6

  4. Click on “Format only cells with” on the left-hand side below and select the option “Blanks.”


    conditional formatting To blank cell 1-7

  5. Click on “Format” and select any option we desire to format the cells.


    conditional formatting To blank cell 1-8

  6. We have selected a blue color as a formatting option. As we click on “OK,” it will show the result like the one below.


    conditional formatting To blank cell 1-9

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Example #2 – Conditional Formatting for Blank Cells using ISBLANK Function

  • Step #1 – We will repeat the same steps and go to “Conditional Formatting” in the “Styles” section under the “Home” tab.
conditional formatting To blank cell 1-10
  • Step #2 – Now, we will click on “New rules” from the wizard box and then select the option on the last option from the rule types.
New formatting Rule
  • Step #3 – In the formula box, we must insert the following formula:

ISBlank($E$3)

New Formatting Rule
  • Step #4 – We must click on “Format” and select the desired formatting. For instance, we have chosen the blue color this time.
Format cells
  • Step #5 – Then, click on “OK.” We can see that the blank cell has been identified and formatted to blue.
conditional formatting To blank cell 1-14

Things to Remember

  • We can also use the ISBlank or IF function formula to format the blank cells.
  • Once the cells are no longer blank, the formatting is automatically removed from them.

This article is a guide to Conditional Formatting For Blank Cells. We discussed applying conditional formatting for blank cells, practical examples, and a downloadable Excel template. You may also learn more about Excel from the following articles: –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *