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.
How to Use ISBLANK Function for Conditional Formatting in Excel: 3 Suitable Methods
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.
- Then, select the Home tab.
- After that, go to Conditional Formatting > New Rule.
- Now, from the New Formatting Rule dialog box, click on Use a formula to determine which cells to format.
- Subsequently, type the following formula into the formula box:
=ISBLANK(D5)- After that, click on the Format.
- 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.
- Then, the dialog box will appear again. You need to click on OK to apply the formatting to the selected cell.
- Finally, you will see the cell filled with your selected color, if the cell is blank.
- 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.
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.
- Secondly, go to Conditional Formatting and click on Manage Rules.
- Thirdly, from the menu bar select This Worksheet.
- Then, select the up-arrow sign beside the range bar.
- Now, you will be asked to define the range of cells where this formatting will be applied. We are selecting the cells D5:F9.
- After that, click on the down arrow sign beside the range bar.
- Now, click on Apply.
- Subsequently, press OK.
- Finally, you can see the result. All the empty cells on the specified range will be filled with your chosen color.
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.
- Then, select the Home tab.
- After that, go to Conditional Formatting > New Rule.
- Now, from the New Formatting Rule dialog box, click on Use a formula to determine which cells to format.
- Subsequently, type the following formula into the formula box.
=ISBLANK(D5:F9)- After that, click on the Format.
- 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.
- Then, the dialog box will appear again. You need to click on OK to apply the formatting to the selected cell.
- Finally, you will see the cells filled with your selected color, if the cells are blank.
Read More: How to Use ISBLANK Function to Check If Cell Is Blank in Excel
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.
- Then, select the Home tab.
- After that, go to Conditional Formatting > New Rule.
- Now, from the New Formatting Rule dialog box, click on Use a formula to determine which cells to format.
- Subsequently, type the following formula into the formula box:
=OR(ISBLANK(D5),ISBLANK(E5),ISBLANK(F5))
- After that, press Format.
- Now, Click on Fill and select any color of your choice and then press OK.
- Then, the dialog box will appear again. You need to click on OK.
- 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.
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.
- Then, select the Home tab.
- After that, go to Conditional Formatting > New Rule.
- Thirdly, from the New Formatting Rule dialog box, click on Use a formula to determine which cells to format.
- Subsequently, type the following formula into the formula box:
=NOT(ISBLANK(D5:D9))
- Then, press Format.
- Now, Click on Fill and select any color of your choice and then press OK.
- After that, the dialog box will appear again. You need to click on OK.
- Finally, you will see that the non-empty cells will be highlighted.
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.
- Then, select the Home tab.
- After that, go to Conditional Formatting > New Rule.
- Thirdly, from the New Formatting Rule dialog box, click on Use a formula to determine which cells to format.
- After that, type the following formula into the formula box:
=AND(NOT(ISBLANK($F5)), $F5<75)
- Then, press Format.
- Now, Click on Fill and select any color of your choice and then press OK.
- After that, the dialog box will appear again. You need to click on OK.
- At the end, you will see only those cells highlighted that have a numerical value less than 75.
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.
Download Practice Workbook
You can download this practice workbook while going through this article.
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.
Related Articles
- How to Use ISBLANK Function in Excel for Multiple Cells
- How to Use Excel ISBLANK to Identify Blanks in Range
- Excel ISBLANK vs IsEmpty
<< Go Back to Excel ISBLANK Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
