Table of Contents
Excel Conditional Formatting for Blank Cells
Conditional Formatting for Blank Cells is the function in excel that is used for creating inbuilt or customized formatting. From this, we can highlight the duplicate, color the cell as per different value ranges, etc. It also has 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.
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 a blank sheet or some cells of it, we will 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 the 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 the 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 the Format option from the same window as highlighted in the below screenshot.
- The Format option will take us to the customize windows where we can change the text fonts, shape, or define or change the border or fill. For highlighting blank cells, go to the 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 the selected color and condition as shown below.
- If the selected condition suits and matches the requirement, click Ok or select go back to Format to 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 a No Fill cell, as shown below.
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 the 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: “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 the 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. We have deleted some cells data for testing, and the color of those cells change from No Fill to Red Peach. Which shows our selected and applied conditions are working properly.
- 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.
- Applying conditional formatting to large data set such as complete sheet may make excel work slow while filtering.
Things to Remember
- The selection of proper rules 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 to deal with and apply bigger conditional formatting to avoid excel getting freeze.
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 a downloadable excel template. You can also go through our other suggested articles –