What Is Conditional Formatting For Blank Cells In Google Sheets?
Conditional formatting for blank cells in Google Sheets is a feature that enables us to use predefined or customized rules to highlight empty cells in a chosen cell range.
Users can use the Google Sheets Formatting blank cells in a spreadsheet to distinguish empty cells in massive and complex financial statements.
For example, we have a list of grocery items and their quantities in the table below. We will highlight the blank cells using the conditional Formatting option.
Select cell A1:B9 and apply the Google Sheets conditional format for blank cells.
We will get the output shown above. In the selected dataset, there are 4 empty or blank cells highlighted as per the formatting rule set.
Table of contents
Key Takeaways
- The Conditional Formatting for Blank Cells in Google Sheets 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 Is empty option under the second predefined rule type 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 In Google Sheets?
We can apply the Conditional Formatting For Blank Cells In Google Sheets using the following two methods:
- Using the inbuilt Conditional Formatting Feature (The General Method)
- Using the ISBLANK Function.
#Method 1 – Using the inbuilt Conditional Formatting Feature 🡪
First, choose the dataset or the required cell range 🡪 select the “Format” tab 🡪 click the “Conditional formatting” option, as shown below.
The “Conditional format rules” pane appears on the right side. Here, click the “Add another rule” option, as shown below.
We see the “Single Color” and the “Color scale” tabs. Here, click the “Single color” tab, select the “Is empty” option from the “Format cells if…” drop-down and the required highlight color from the “Formatting style”, as shown below.
Finally, click the “Done” option. Then, we will have the Google Sheets Conditional Formatting blank cells highlighted as the output.
Method #2 – Using the 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:
First, choose the dataset or the required cell range 🡪 select the “Format” tab 🡪 click the “Conditional formatting” option, as shown below.
The “Conditional format rules” pane appears on the right side. Here, click the “Add another rule” option, as shown below.
We see the “Single Color” and the “Color scale” tabs. Here, click the “Single color” tab and select the “Custom formula is” option from the “Format cells if…” drop-down, as shown below.
When we select the “Custom formula is” option, an empty field appears below. There, enter the “=ISBLANK()” formula and select the exact cell address [ i.e., the first cell in the selected cell range] from which the formula has to check for blank cells. Then, select the required color, as shown below.
Finally, click the “Done” option. Then, we will have the Google Sheets Conditional Formatting blank cells highlighted as the output.
Examples
We will consider some examples to apply the Conditional Format for Blank Cells discussed in the previous section using both the methods.
Example 1 – General Method To Conditional Format For Blank Cells
The dataset given below has the marks scored by a student in an exam. We must highlight the empty cells where the marks are not found or entered.
The procedure to format the blank cells is as follows:
- First, choose the cell range A1:C9 🡪 select the “Format” tab 🡪 click the “Conditional formatting” option, as shown below.
- The “Conditional format rules” pane appears on the right side. There, click the “Add another rule” option, as shown below.
- We see the “Single Color” and the “Color scale” tabs. Here,
- Click the “Single color” tab, select the “Is empty” option from the “Format cells if…” drop-down and the desired color from the “Formatting styles” option.
- Click the “Done” option, as shown below.
We get the output shown above where the cells where the marks are not entered, are empty, and are highlighted.
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 from January to March. However, the data appears incomplete and we must highlight the empty cells.
The steps to use the ISBLANK() function to apply the Conditional Formatting feature are as follows:
Step 1: First, choose the cell range A1:D7 🡪 select the “Format” tab 🡪 click the “Conditional formatting” option. The “Conditional format rules” pane appears on the right side. There, click the “Add another rule” option, as shown below.
Step 2: We see the “Single Color” and the “Color scale” tabs. Here, click the “Single color” tab, select the “Custom formula is” option from the “Format cells if…” drop-down, as shown below.
Step 3: When we select the “Custom formula is” option, an empty field appears below. There, enter the “=ISBLANK(A1)” formula and select the required color, as shown below.
Finally, click the “Done” option. Then, we will have the Google Sheets Conditional Formatting blank cells highlighted as the output, as shown below.
Example 3 – Multiple Conditional Formatting to distinguish blank cells and cells with zeros.
We can apply conditional formatting that distinguishes blank cells and cells with zeroes using the predefined multiple conditional formatting rules.
For example, the table below contains a dataset with zeros and empty cells regarding the stationary items purchased from June to August. We will highlight the cells with 0 and the blank cells with different rules.
The steps to apply multiple formatting rules are as follows:
Step 1: Choose the cell range A1:D6 🡪 select the “Format” tab 🡪 click the “Conditional formatting” option. The “Conditional format rules” pane appears on the right side. There, click the “Add another rule” option, as shown below.
Step 2: We see the “Single Color” and the “Color scale” tabs. Here, click the “Single color” tab, select the “Is empty” option from the “Format cells if…” drop-down, select the desired color from the “Formatting styles” option and click the “Done” option, as shown below.
The cells that are blank are highlighted, as shown above.
Step 3: The set rule gets saved in the “Conditional format rules” pane. Now, to apply another rule, select the “Add another rule” icon just below the first rule, as shown below.
Step 4: As always, we see the “Single Color” and the “Color scale” tabs. Here,
- Click the “Single color” tab, select the “Is equal to” option from the “Format cells if…” drop-down.
- When we select the “Is equal to” option, an empty field appears below. There, enter the number 0, select the desired color from the “Formatting styles” option and click the “Done” option, as shown below.
Finally, we can see both the rules applied and the output, as shown below.
Important Things To Note
- We must use multiple rules for conditional formatting for cells to highlight 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.
- Any modification of the dataset where the conditional formatting for blank cells are applied, the formatting will also get modified or updated automatically.
Download Template
This article must help understand Conditional Formatting for Blank Cells in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Frequently Asked Questions (FAQs)
Let us consider example 3, once again, to see how we can clear conditional format for blank cells in google sheets.
The output is multiple rules applied, as shown in the image below.
To clear the rules that are not required, just hover around the set rules and we will see a delete option. Click it, as shown below.
Then we will get the output, as shown below.
The google sheets conditional formatting if cell is blank may not work for the following reasons, namely:
The formatted dataset has been modified or updated and did not get refreshed to display the updated data.
We have not set the conditional formatting rules or the set rules are deleted.
Also, since Google Sheets are online, it might not work due to network issues.
We have another alternate way to apply the “Conditional formatting” as follows:
First, select the dataset 🡪 right-click on the selected dataset 🡪 scroll to the end of the list and click the “View more cell actions” right arrow 🡪 select the “Conditional formatting” option, as shown below. 
Recommended Articles
Guide to Conditional Formatting for Blank Cells In Google Sheets. We learn to apply conditional formatting to blank cells with examples. You can learn more from the following articles –
Leave a Reply