Conditional Formatting for Blank Cells

How to Apply Conditional Formatting for Blank Cells?

Conditional formatting for blank cells can be accessed from the new rules of conditional formatting tab and then in the cells that contain blank value which is the second option. In other words it means a certain cell which doesn’t have a value assigned to it. It doesn’t have a numeric, alphabetic or any sort of character as value. It is just 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

Consider the following data given below,

data

In the above data, we can see that we have marks for five students but not for every subject. Some of the cells are left blank.

  1. Select the data range in which we want to use conditional formatting; in the Home tab, under the styles section, click on conditional formatting, Click on New rule.


    conditional formatting To blank cell 1-4

  2. When we click on the new rule, another wizard box appears, which is a new formatting rule tab,


    conditional formatting To blank cell 1-5

  3. Click on the second option, which is to format cells which only contain,


    New format rule

  4. Click on Format cells only with on the left-hand side below and select the option for blanks.


    New formatting Rule

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


    Format cells

  6. I have selected a blue color as my formatting option. As we click on Ok, the result is shown below.


    conditional formatting To blank cell 1-9

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

  • Step #1 – We repeat the same steps go to conditional formatting in the styles section under the home tab.
conditional formatting To blank cell 1-10
  • Step #2 – Now click on New rules from the wizard box and then select the option which is on the last of the option from the rule types.
New formatting Rule
  • Step #3 – In the formula, Box write the following formula,
New Formatting Rule
  • Step #4 – Click on Format and select the desired formatting; for instance, I am choosing the blue color this time.
Format cells
  • Step #5 – When we click on OK and see the result that the blank cell has been identified and has been formatted to blue color.
conditional formatting To blank cell 1-14

Things to Remember

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

This has been a guide to Conditional Formatting For Blank Cells. Here we discuss how to Apply Conditional Formatting For Blank Cells along with practical examples and a downloadable excel template. You may learn more about excel from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>

Reader Interactions

Leave a Reply

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