While working with huge chunks of data we might have some cells which are blanks, these cells come in our tables and reports too and it is hard to identify them manually, we use conditional formatting for blank cells which 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.
How to Apply Conditional Formatting for Blank Cells?
The conditional formatting for blank cells in excel 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.
Conditional Formatting in excel means we change the format of a certain cell in excel based on certain conditions. But that means the cell in any data table or excel worksheet contains any value. Then onwards we set a certain condition in it to change the format of it. But what if the cell is blank? How do we change the format of a blank cell? This we will learn about this topic.
There are two different ways to format a blank cell in excel. They are:
- Using Conditional Formatting Tab.
- Using ISBLANK Function.
The first method is the pretty simple process from where we do the normal conditional formatting in any scenarios while the second method is a function provided by excel is a function which checks whether the given reference cell is blank or not and then we apply conditional formatting to it.
Steps to Conditional Format for Blank Cells
Let us learn to use these methods by a few examples and it will be more clear to us.
Consider the following data given below,
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. Now we will use both methods to do conditional formatting on the blank cells.
Step #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.
Step #2 – When we click on new rule another wizard box appears, which is a new formatting rule tab,
Step #3 – Click on the second option which is to format cells which only contain,
Step #4 – Click on Format cells only with on the left-hand side below and select the option for blanks.
Step #5 – Click on Format and select any option we desire to format the cells.
Step #6 – I have selected a blue color as my formatting option. As we click on Ok and see the result.
The above method was the general method to use conditional formatting. Now we will use the Isblank function to do the same. We repeat the same steps go to conditional formatting in the styles section under the home tab.
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.
In the formula Box write the following formula,
Click on Format and select the desired formatting, for instance, I am choosing the blue color this time.
When we click on OK and see the result that the blank cell has been identified and has been formatted to blue color.
Let us use another example to understand the basics once again of how to use conditional formatting in blank cells. Consider the following data given below.
We have some of the cells which are blank and we will highlight them to different colors by using the conditional formatting option given by excel.
Select the data range where we want to apply conditional formatting, Now click on Conditional formatting from the styles section in the home tab,
From the wizard box that appeared click on the new rule and another dialog box appears.
Click on the second option given from the rule type options.
In the format cells with the select blank as the option,
Click on Format and select the desired color for the formatting, for instance, I have chosen a red color.
Click on OK and see the result for the blank cells.
Now let’s try to put a value in one of the blank cells and see the result. For example in Cell C3 which is blank put a value “SP”.
When we click anywhere in the excel we see that the color has changed to white again as the cell is not blank anymore.
Explanation of Conditional Formatting for Blank Cells
Conditional Formatting of Blank Cells basically means changing the colors of the blank cells if they are blank.
It helps us to identify which cells are blank in a large sum of data rather than applying the auto filter to each column in excel we can easily see through the different colors.
Things to Remember
There are a few different things we need to remember in conditional formatting in blank cells:
- Conditional Formatting to blank cells means changing the color of the blank cells.
- 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.
You can this Conditional Formatting Blank Cells template here – Conditional Formatting Blank Cells Template
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 downloadable excel template. You may learn more about excel from the following articles –
- Highlight Dates using Conditional Formatting
- Conditional Formatting in VBA
- VBA COUNTIF Function
- Insert Image in the Excel
- Format Number in VBA
- How to Apply Conditional Formatting Based on Another Cell Value?
- How to Apply Conditional Formatting in Pivot Table
- Apply Format Numbers to Millions in Excel
- How to Countif not Blank in Excel?