What Is Conditional Formatting For Blank Cells?
Conditional formatting for blank cells is an Excel option that enables us to use predefined or customized rules to highlight empty cells in a chosen cell range. Users can use the Conditional Formatting feature for blank cells in a spreadsheet to distinguish empty cells in massive and complex financial statements.
For example, the table below lists students and their test scores in different subjects.
And the requirement is to accentuate empty cells in the cell range B2:E11 to cite cells that do not contain test scores.
Then, we can apply the Excel conditional formatting for blank cells formula in the cell range B2:E11 to achieve the required outcome.
In the above example, we use the Excel ISBLANK function as the Excel conditional formatting for blank cells formula in the cell range B2:E11.
The function checks each cell in the chosen cell range if it is empty. And if the condition holds in a cell, the cell gets accentuated in the specified format. In this case, we have set the Red cell background color as the conditional format.
Table of contents
Key Takeaways
- The conditional formatting for blank cells enables one to accentuate empty cells in a chosen range in a worksheet.
- Users can conditionally format blank cells spread randomly across a massive dataset. It helps draw viewers’ attention to empty cells in a worksheet and use the data accordingly.
- We can use the Blanks option under the second predefined rule type in the New Rules option in the Conditional Formatting feature to conditionally format blank cells. Otherwise, use the ISBLANK function to create a customized formatting condition for blank cells under the last rule type.
How To Apply Conditional Formatting For Blank Cells?
We can apply conditional formatting for blank cells using the following two methods:
- Using the Conditional Formatting Feature Inbuilt Rule (The General Method)
- Using ISBLANK Function
Method #1 – Using The Conditional Formatting Feature Inbuilt Rule (The General Method)
The steps to use the Conditional Formatting feature inbuilt rule to format blank cells conditionally are as follows:
- Choose the cell range where we must accentuate empty cells and then the Home tab → Excel Conditional Formatting drop-down option.
- Select New Rule from the drop-down list to open the New Formatting Rule window.
Otherwise, we can use the excel keyboard shortcut Alt + H + L + N to access the New Formatting Rule window.
Alternatively, we can use the shortcut keys, Alt + O + D, to open the Conditional Formatting Rules Manager window. And then, choose the New Rule option to open the New Formatting Rule window. - Choose the second rule type to select the required predefined formatting condition in the New Formatting Rule window.
- Choose Blanks as the Format only cells with field setting in the Edit the Rule Description section to accentuate the blank cells in the chosen range.
- Choose Format to open the Excel Format Cells window.
- Click the different tabs in the Format Cells window to access the various formatting options to highlight the empty cells.
- Click OK to exit from the Format Cells window.
- Click OK to exit from the New Formatting Rule window. And we can view the empty cells accentuated in the chosen cell range based on the specified predefined formatting condition.
Method #2 – Using ISBLANK Function
The steps to use the ISBLANK() as the conditional formatting rule in the Conditional Formatting feature to conditionally format blank cells are as follows:
- Choose the cell range where we must accentuate empty cells and then the Home tab → Conditional Formatting drop-down option.
- Select New Rule from the drop-down list to open the New Formatting Rule window.
- Select the last rule type to enter the customized formatting condition in the New Formatting Rule window.
- Enter the ISBLANK()– bases formatting condition expression in the field in the Edit the Rule Description section to accentuate the blank cells in the chosen range.
- Choose the Format button to access the Format Cells window.
- Click the different tabs in the Format Cells window to access the various formatting options to highlight the empty cells.
- Click OK to exit from the Format Cells window.
- Click OK to exit from the New Formatting Rule window. And we can view the empty cells accentuated in the chosen cell range based on the specified ISBLANK()-based formatting condition.
On the other hand, we can remove conditional formatting for blank cells using the Clear Rules option in the Conditional Formatting feature in the Home tab.
We can choose the cell range where we applied the conditional formatting rules for blank cells. And then, we can use the Clear Rules option → Clear Rules from Selected Cells to remove the applied conditional formatting for empty cells in the chosen range.
And if we must remove conditional formatting for blank cells in the whole worksheet, we must choose the Clear Rules option → Clear Rules from Entire Sheet.
Furthermore, the keyboard shortcut to access the Clear Rules option is Alt + H + L + C. And then, we can press S or E depending on whether we need to choose the Clear Rules from Selected Cells or Clear Rules from Entire Sheet option.
Examples
The following examples explain the two methods for applying conditional formatting for blank cells, discussed in the previous section.
Example #1 – General Method To Conditional Format For Blank Cells
The table below lists the top 10 stocks in the US stock markets and their price details.
But many cells in the table appear blank. And the requirement is to highlight them using the Conditional Formatting feature in the Home tab.
- Step 1: Select the cell range A2:E11 and choose Home → Conditional Formatting → New Rule to open the New Formatting Rule window.
- Step 2: Click on the second rule type in the list.
And then, click on the Format only cells with field drop-down button to choose Blanks from the list.
Next, select Format to open the Format Cells window.
- Step 3: Click the Fill tab in the Format Cells window to choose the required cell background color.
And click OK.
Finally, clicking OK will close the New Formatting Rule window. And we will obtain the below output.
Since we chose the predefined rule to format only blank cells and highlight them in Yellow, the empty cells in the chosen cell range appear in Yellow.
Example #2 – Conditional Formatting For Blank Cells Using ISBLANK Function
The table below lists the top smartphone models in the US in column A and their sales at a store from Jan-May in the cell range B2:F8.
However, the data appears incomplete, and the requirement is to highlight the empty cells in the cell range B2:F8.
Then, here is how to apply the ISBLANK() as a customized conditional formatting rule in the Conditional Formatting feature in the Home tab.
- Step 1: Choose the cell range B2:F8 and select Home → Conditional Formatting → New Rule to open the New Formatting Rule window.
- Step 2: Choose the last rule type from the list.
And enter the ISBLANK() in the field specified in the Edit the Rule Description section to update the customized blank cells formatting condition.
=ISBLANK(B2)
And then, click Format in the New Formatting Rule window to access the Format Cells window.
- Step 3: Go to the Fill tab in the Format Cells window and choose the cell background color required to accentuate the blank cells in the chosen cell range.
And click OK.
Next, click OK in the New Formatting Rule window to exit from the window and achieve the below output.
The ISBLANK() checks every cell, one after the other, in the cell range B2:E8, whether it is blank. And if the function output is TRUE in a cell, the cell gets accentuated in the chosen format, Red cell background color.
Furthermore, assume we must remove the applied conditional formatting rule.
Then, we can select the cell range B2:F8 and choose Home → Conditional Formatting → Clear Rules → Clear Rules from Selected Cells.
And once we choose the above option, we get the unformatted, original dataset.
Important Things To Note
- The conditional formatting for blank cells highlights cells containing zeros and empty cells. So, we can apply a separate rule with a different format again to accentuate empty cells and distinguish them from cells containing zeros.
- If we update data in a blank cell after conditionally formatting it for blanks, the formatting will get removed automatically.
Frequently Asked Questions (FAQs)
We can apply conditional formatting that distinguishes blank cells and cells with zeroes using the predefined conditional formatting rules in the Conditional Formatting feature.
For example, the table below contains a dataset with zeros and empty cells.
Here is how to highlight them uniquely.
• Step 1: Choose the cell range B2:E8 and select Home → Conditional Formatting → New Rule.
• Step 2: The New Formatting Rule window opens, where we must choose the second rule and set the predefined conditional formatting rule as shown below.
And then, choose Format to open the Format Cells window.
• Step 3: Choose the Fill tab to select the required cell background color.
And click OK.
And choose OK in the New Formatting Rule window.
The rule checks every cell in the chosen cell range, whether it contains zero or is blank. And if the condition holds, the cell gets accentuated in the specified format.
Next, we shall modify the format settings for the blank cells.
• Step 4: Again, choose the cell range B2:E8 and select Home → Conditional Formatting → New Rules to open the New Formatting Rule window.
• Step 5: Click on the second rule in the list to set the field Format only cells with as Blanks.
And select Format to open the Format Cells window.
• Step 6: Choose the Fill tab to select the required cell background color.
And click OK.
And choose OK in the New Formatting Rule window.
So, the newly-set rule will check every cell in the chosen range if it is blank. And if a cell is blank, the cell gets accentuated in the chosen format, Red cell background color.
You can conditional format a cell if another cell is blank using the following steps:
1) Choose the cell you want to format conditionally if another cell is blank.
2) Select Home → Conditional Formatting → New Rule to open the New Formatting Rule window.
3) Select the last rule type.
4) Enter the ‘=’ sign and the reference to the cell you need to verify if it is blank in the field specified in the Edit the Rule Description window.
And then, enter the ‘=’ sign and ‘” “’ to complete the rule expression.
5) Choose Format to open the Format Cells window.
6) Use the options in the Format Cells window to set the required format for the cell highlighting requirement.
7) Finally, click OK in the Format Cells and New Formatting Rule windows to view the conditionally formatted cell based on the condition that another cell is blank.
Conditional formatting can ignore blank cells.
And for that, we must check the Stop if True box against the predefined Cell contains a blank value rule in the Conditional Formatting Rules Manager window.
Download Template
This article must be helpful to understand the Conditional Formatting For Blank Cells, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
This has been a guide to What Is Conditional Formatting For Blank Cells. We learn to do it using Conditional Formatting & ISBLANK(), with examples. You can learn more from the following articles –
Leave a Reply