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.
Example #1 – General Method to Conditional Format for Blank Cells
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.
4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
- 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.
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.
- 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.
- Step #3 – In the formula, Box write the following formula,
- Step #4 – Click on Format and select the desired formatting, for instance, I am choosing the blue color this time.
- 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.
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 downloadable excel template. You may learn more about excel from the following articles –