Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Use ISBLANK Function for Conditional Formatting in Excel

Today we will discuss the use of the ISBLANK function in Excel for Conditional Formatting. In a large dataset with numerous entries, there may be a lot of blank cells as well. You may need to identify those empty cells. We can use the ISBLANK function to find blank cells in our dataset. On the other hand, Conditional Formatting is a strong tool of Excel which can be used to highlight cells according to our choice.

Overview of Using ISBLANK function for Conditional Formatting in Excel


Download Practice Workbook

You can download this practice workbook while going through this article.


3 Suitable Methods to Use ISBLANK Function for Conditional Formatting in Excel

In this article, we will demonstrate how the ISBLANK function can be used for Conditional Formatting. The ISBLANK function can be used to format a single cell. It can also be used to format a range of cells. Moreover, the ISBLANK function can be used with other functions like OR function to apply Conditional Formatting in Excel if another cell is blank.


Method 1: Using ISBLANK Function for Conditional Formatting in A Single Cell

In this method, we will show you how to apply the ISBLANK function to a single cell. This method is very easy and simple to use. Here in the data set, the marks obtained by a number of students in subjects like English, Physics and Chemistry have been entered. As you can see, there are blank cells in the data set. We will apply the ISBLANK function for Conditional Formatting and highlight those cells. To highlight a single cell, you just need to follow the steps below:

Steps:

  • To begin, select the cell where you want to apply the function. We are selecting the blank cell D5.

Selecting a Particular Cell to Apply the ISBLANK Function for Conditional Formatting

  • Then, select the Home tab.

Selecting Home Tab

  • After that, go to Conditional Formatting > New Rule.

Selecting the New Rule Command from Conditional Formatting Group

  • Now, from the New Formatting Rule dialog box, click on Use a formula to determine which cells to format.

Selecting a Rule Type to Apply the ISBLANK Function for Conditional Formatting

  • Subsequently, type the following formula into the formula box:
=ISBLANK(D5)
  • After that, click on the Format.

Clicking on Format Button

  • Now, you will have different options to highlight the selected cell if it is empty. If you want to fill the cell with any specific color, click on Fill.
  • Then, select any color of your choice and then press OK.

Selecting a Color to Highlight the Cells

  • Then, the dialog box will appear again. You need to click on OK to apply the formatting to the selected cell.

Pressing OK to Apply the ISBLANK Function for Conditional Formatting

  • Finally, you will see the cell filled with your selected color, if the cell is blank.

Using the ISBLANK Function for Conditional Formatting in A Single Cell

  • If you put any value into the cell and press the Enter key, the cell will lose its formatting as it is no longer a blank cell.

Using the ISBLANK Function for Conditional Formatting in A Single Blank Cell

Read More: How to Use SUMIF and ISBLANK to Sum for Blank Cells in Excel


Method 2: Applying ISBLANK Function for Conditional Formatting in Multiple Cells

In this method, we will use the ISBLANK function on a range of cells. Instead of a single cell, this operation can be performed on multiple cells at once. There are two ways to apply Conditional Formatting in Multiple Cells using the ISBLANK function. The data set we are using is the same as Method 1.

2.1 Using Manage Rules Command for Conditional Formatting

Manage Rules is a strong command for Conditional Formatting in Excel. It allows the users to change any existing formatting in the worksheet. Simply, follow the steps to use this command to apply Conditional Formatting in multiple cells using the ISBLANK function.

Steps:

  • First, follow Method 1 step by step to highlight a single cell with the ISBLANK function.

Using the ISBLANK Function for Conditional Formatting in A Single Blank Cell

  • Secondly, go to Conditional Formatting and click on Manage Rules.

Selecting the Manage Rules Command from Conditional Formatting Group

  • Thirdly, from the menu bar select This Worksheet.

Selecting the Worksheet to Apply the ISBLANK Function for Conditional Formatting

  • Then, select the up-arrow sign beside the range bar.

Selecting the Up Arrow Sign to Change the Range

  • Now, you will be asked to define the range of cells where this formatting will be applied. We are selecting the cells D5:F9.

Selecting the Range of Dataset to Apply the ISBLANK Function for Conditional Formatting

  • After that, click on the down arrow sign beside the range bar.

Applying the Formula to the Chosen Range

  • Now, click on Apply.

Applying the ISBLANK Function for Conditional Formatting in a Newly Selected Range

  • Subsequently, press OK.

Applying the ISBLANK Function in a Newly Selected Range

  • Finally, you can see the result. All the empty cells on the specified range will be filled with your chosen color.

Using Manage Rules Command to Apply the ISBLANK Function for Conditional Formatting in Multiple Cells

2.2 Selecting a Range of Data

If you find Method 2.1 a bit tiresome, you are going to love this method. This one is very much like Method 1, but we will select a range of data here instead of selecting a single cell. Just follow the steps below.

Steps: 

  • First, select the range of cells where you want to apply the function. We are selecting cells D5:F9.

Selecting the Range of Cells to Apply the ISBLANK Function for Conditional Formatting

  • Then, select the Home tab.

Selecting Home Tab

  • After that, go to Conditional Formatting > New Rule.

Selecting the New Rule Command from Conditional Formatting Group

  • Now, from the New Formatting Rule dialog box, click on Use a formula to determine which cells to format.

Selecting a Rule Type to Apply the ISBLANK Function for Conditional Formatting

  • Subsequently, type the following formula into the formula box.
=ISBLANK(D5:F9)
  • After that, click on the Format.

Clicking on the Format Button

  • Now, you can choose a color to fill in the blank cells with, or, you may choose other operations as well. For filling empty cells with a color, click on Fill.
  • Then, select any color of your choice and press OK.

Selecting a Color to Highlight the Cells

  • Then, the dialog box will appear again. You need to click on OK to apply the formatting to the selected cell.

Pressing OK to Apply the ISBLANK Function for Conditional Formatting

  • Finally, you will see the cells filled with your selected color, if the cells are blank.

Using the ISBLANK Function for Conditional Formatting in Multiple Cells

Read More: How to Use ISBLANK Function to Check If Cell Is Blank in Excel


Similar Readings


Method 3: Combining ISBLANK and OR Functions for Conditional Formatting in Excel

We can use the ISBLANK function with other functions for Conditional Formatting . If another cell is blank, we can use ISBLANK along with OR function to indicate it. In the data set, there are students who have not received their grades in all subjects. If there is even one empty cell in any of the fields (English, Physics and Chemistry), it has to be noted in Missing Data. Now, we want to highlight cells of range G5:G9 that have at least one blank cell of all fields using the combination of ISBLANK and OR functions. This method is simple and it will give you an edge over Excel. Follow the steps below.

Steps:

  • To begin, select the range where you want to apply Conditional Formatting. We are selecting G5:G9.

Selecting the Range of Cells to Apply ISBLANK and OR Functions for Conditional Formatting

  • Then, select the Home tab.

Selecting Home Tab

  • After that, go to Conditional Formatting > New Rule.

Selecting the New Rule Command from Conditional Formatting Group

  • Now, from the New Formatting Rule dialog box, click on Use a formula to determine which cells to format.

Selecting a Rule Type to Apply ISBLANK and OR Functions for Conditional Formatting

  • Subsequently, type the following formula into the formula box:

=OR(ISBLANK(D5),ISBLANK(E5),ISBLANK(F5))
Inserting a Formula Combining ISBLANK and OR Functions
  • After that, press Format.

Clicking on the Format Button

  • Now, Click on Fill and select any color of your choice and then press OK.

Selecting a Color to Highlight the Cells

  • Then, the dialog box will appear again. You need to click on OK.

Pressing OK to Apply ISBLANK and OR Functions for Conditional Formatting

  • Finally, you will see the results. If any data is missing in the subject fields, the corresponding cell in the Missing Data field will turn into your chosen color.

Combining ISBLANK and OR Functions for Conditional Formatting

Read More: How to Use ISNUMBER Function in Excel (7 Examples)

Nesting ISBLANK, NOT, and AND Functions to Ignore Blank Cells with Conditional Formatting

The ISBLANK function can be used with the NOT function to avoid empty cells in a worksheet. To ignore blank cells, simply follow the steps below.

Steps:

  • First, select the range where you want to apply Conditional Formatting. Here, we want to ignore the empty cells from D5:D9.

Selecting the Range of Cells to Apply ISBLANK and NOT Functions for Conditional Formatting

  • Then, select the Home tab.

Selecting Home Tab

  • After that, go to Conditional Formatting > New Rule.

Selecting the New Rule Command from Conditional Formatting Group

  • Thirdly, from the New Formatting Rule dialog box, click on Use a formula to determine which cells to format.

Selecting a Rule Type to Apply ISBLANK and NOT Functions for Conditional Formatting

  • Subsequently, type the following formula into the formula box:

=NOT(ISBLANK(D5:D9))
Inserting a Formula Combining ISBLANK and NOT Functions
  • Then, press Format.

Clicking on the Format Button

  • Now, Click on Fill and select any color of your choice and then press OK.

Selecting a Color to Highlight the Cells

  • After that, the dialog box will appear again. You need to click on OK.

Pressing OK to Apply ISBLANK and NOT Functions for Conditional Formatting

  • Finally, you will see that the non-empty cells will be highlighted.

Combining ISBLANK and NOT Functions for Conditional Formatting

You may also wish to highlight cells with specific criteria and ignore empty cells at the same time. For instance, In cells F5:F9, there is one empty cell and four non-empty cells with different numerical values. We want to highlight only those values that are less than 75. Follow the steps below to do so.

Steps:

  • Initially, select the range where you want to apply Conditional Formatting. Here, we are selecting from F5:F9.

Selecting the Range of Cells to Apply ISBLANK, NOT and AND Functions for Conditional

  • Then, select the Home tab.

Selecting Home Tab

  • After that, go to Conditional Formatting > New Rule.

Selecting the New Rule Command from Conditional Formatting Group

  • Thirdly, from the New Formatting Rule dialog box, click on Use a formula to determine which cells to format.

Selecting a Rule Type to Apply ISBLANK, NOT and AND Functions for Conditional Formatting

  • After that, type the following formula into the formula box:

=AND(NOT(ISBLANK($F5)), $F5<75)
Inserting a Formula Nesting ISBLANK, NOT and AND Functions
  • Then, press Format.

Clicking on the Format Button

  • Now, Click on Fill and select any color of your choice and then press OK.

Selecting a Color to Highlight the Cells

  • After that, the dialog box will appear again. You need to click on OK.

Pressing OK to Apply ISBLANK, NOT and AND Functions for Conditional Formatting

  • At the end, you will see only those cells highlighted that have a numerical value less than 75.

Nesting ISBLANK, NOT and AND Functions for Conditional Formatting

Read More: How to Use NOT and ISNA Functions in Excel (2 Examples)


Things to Remember

Using the ISBLANK function to do Conditional Formatting is very easy. This is a great way to highlight cells that are empty or non-empty. However, applying Conditional Formatting to large data sets may sometimes make Excel work a bit slowly. As a note, there are a few things to remember.

  • The selection of the proper cell or range of cells at the very first step is very important. After entering the formula, the command will always check and look for a preview before applying the formula.
  • While selecting a single cell or a range of cells in the formula bar, be aware of the associated dollar ($) sign.
  • Be careful of selecting a specific formula while using the Manage Rules command, if there are multiple formulas in the worksheet.

Conclusion

In this article, we have talked in detail about Conditional Formatting using the ISBLANK function in Excel. This article will allow users to use Excel more efficiently and effectively. If you have any questions regarding this essay, feel free to let us know in the comments. Also, if you want to see more Excel content like this, please visit our website Exceldemy.com and unlock a great resource for Excel-related content.


Related Articles

Md. Abu Sina Ibne

Md. Abu Sina Ibne

This is Abu Sina, a fresh Engineering Graduate and currently an Excel and VBA content developer at SOFTEKO. I did my bachelor’s in Mechanical Engineering from BUET. My passion is to build new skills, gather experience and apply them to optimize and balance various trade-offs for a better future. Besides, I am fond of travelling, hanging out with friends, and watching movies.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo