Table of Contents
Excel Conditional Formatting for Blank Cells
Conditional Formatting for Blank Cells is the function in excel which is used for creating inbuilt or customized formatting. From this, we can highlight the duplicate, color the cell as per different value range, etc. It has also a way to highlight blank cells.
How to Apply Conditional Formatting for Blank Cells?
To Apply Conditional Formatting for Blank Cells is very simple and easy. Let’s understand how to apply in excel.
Conditional Formatting for Blank Cells – Example #1
We will be covering the small portion of Conditional Formatting which Highlights the Blank Cells. For this consider a blank sheet. This is the best way to see and apply conditional formatting for a blank sheet or some of the cells of a blank sheet. If we apply the conditional formatting to complete blank sheet or some cells of it then we will be able to see how the cell gets highlighted. For this, we have selected a small portion of the sheet covered with a thick border as shown below.
- We will apply the conditional formatting in a defined region only. For that go to the Home menu and select Conditional Formatting under Styles section as shown below.
- Once we do that, we will get the drop-down list of Conditional Formatting. From that list select New Rule.
- A window of New Formatting Rule will open up. There we have a different rule to apply conditional formatting. But for Blank cell, select the second option which is Format only cells that contain.
- And below in Edit the Rule Description box, we have different criteria to define. Here, from the very first box of drop down select Blanks as cell value.
- Once we select the Blanks as cell value, all rest of the drop down boxes will be eliminated from the condition. And we will get conditions related to Blank cells.
- Now for further, Click on Format option from the same window as highlighted in below screenshot.
- The Format option will take us to the customize windows where we can change the text fonts, shaped or we can define or change border or fill as well. For highlighting blank cells go to Fill tab and select any desired color as per requirement. We can also change the pattern as well. Once done, click on Ok as shown below.
- After clicking on Ok, it will take us again back to the previous window, where we will get the preview of selected color and condition as shown below.
- If the selected condition suits and matches with the requirement then click Ok or select go back to Format, update the conditions. Here we got to apply conditions and rule as per our need. Now click on Ok.
- After clicking on Ok, we will get all the blank cells highlighted with Yellow color as shown below.
- Now to test, whether the condition which we have selected is applied properly or not, go to any of those cells and type anything to see if the cell color changes to No Fill or White background. As we can see, the cells with any value are now changed to No Fill cell as shown below.
Conditional Formatting for Blank Cells – Example #2
There is another way to apply conditional formatting to blank cells. And this method is quite easy to apply. For this, we have another set of data as shown below.
Now for applying Conditional Formatting, first select the data and follow the same path as shown in example-1.
- Go to Home menu, under the Styles section, select Conditional Formatting.
- Once we do that, we will get the drop-down list of all the available options under it. Now select the New Rule from the list.
- Once we do that, we will get the New Formatting Rule box. There we have a different rule to apply conditional formatting. But here we have to select the last option which is “Use a formula to determine which cells to format”.
- Now in the Edit the Rule Description box, write the syntax of a function ISBLANK and select the complete range of data and after that click on Format tab, as shown below.
- Now a Format Cells window will open. Go to Fill tab.
- And select the color of your choice to highlight the blank cells. Here we have selected the color as shown below. Once done. Click on OK to apply.
- After clicking on Ok, it will again take us back to the same previous window, where we will get the preview of the selected color and condition. Now click on Ok to apply.
- To test the applied condition, delete any of the cell data and see the result. For testing, we have deleted some cells data and the color of those cells change from No Fill to Red Peach. Which shows our selected and applied conditions are working properly.
Pros of How to Apply Conditional Formatting for Blank Cells
- It is very quick and easy to apply.
- We can select any range and type of data to highlight blank cells.
- This is quite useful when we are working on data validation work. By this, we can highlight the cell and attributes which are left blank.
Cons of How to Apply Conditional Formatting for Blank Cells
- Applying conditional formatting to large data set such as complete sheet may make excel work slow while filtering.
Things to Remember
- Selection of proper rule and formatting is very important. Once we select, it will always check and look for the preview before applying the changes.
- Always use limited data, if you are dealing and applying bigger conditional formatting to avoid excel getting freezed.
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 can also go through our other suggested articles –