Excel Functions Tutorials
- Excel Tools
- Excel Ribbons and Tabs
- Quick Access Toolbar in Excel (QAT)
- "Save As" Shortcut in Excel
- Accounting Number Format in Excel
- Add-Ins in excel
- Add Filter in Excel
- Advanced Filter in Excel
- Auto Filter In Excel
- Auto Format Excel
- AutoFill in Excel
- Analysis ToolPak in Excel
- ANOVA in Excel
- Border in Excel
- Checkbox in Excel
- Check Mark in Excel (? Tick Symbol)
- Combo Box in Excel and VBA
- Conditional Formatting in Excel
- Conditional Formatting with Formulas
- Conditional Formatting for Blank Cells
- Conditional Formatting Based on Another Cell Value
- Conditional Formatting in Pivot Table
- Consolidate Data in Excel
- Comma Style in Excel
- CSV vs Excel
- Data Bars in Excel
- Data Table in Excel
- Data Validation Excel
- Data Model in Excel
- Developer Tab in Excel
- Descriptive Statistics in Excel
- Dynamic Named Range in Excel
- Drawing in Excel
- Excel Fill Handle
- Excel Fill Down
- Error Bars in Excel
- Excel Forms for Data Entry
- Excel Tables
- Excel Power View
- Exponential Smoothing in Excel
- Filters in Excel
- Flash Fill in Excel
- Freeze Panes in Excel
- Freeze Columns in Excel
- Freeze Cells in Excel
- Format Painter in Excel
- Shortcut for Format Painter in Excel
- F-Test in Excel
- Goal Seek in Excel
- Gridlines in Excel
- Heat Map in Excel
- 3D Maps in Excel
- Header and Footer in Excel
- Insert Button in Excel
- Insert / Draw Line in Excel
- Insert Function in Excel
- List Box in Excel VBA
- Lock Cells in Excel
- Macros in Excel
- Enable Macros in Excel
- Merge and Center in Excel
- Merge Cells in Excel
- Merge Tables in Excel
- Name Box in Excel
- Name Range in Excel
- Null in Excel
- One Variable Data Table in Excel
- OneDrive Excel
- Protect Workbook in Excel
- Pivot Table in Excel
- Pivot Table Examples
- Pivot Table Filter
- Pivot Table Slicer
- Paste Special in Excel (With Top 10 Shortcuts)
- Quick Analysis Tools in Excel
- Radio Button in Excel
- Recording Macros in Excel
- Regression Analysis in Excel
- Scenario Manager in Excel
- Scroll Bars in Excel
- Scroll Lock in Excel
- Slicers in Excel
- Solver in Excel
- Sort by Color in Excel
- Sort by Number in Excel
- Sort Data in Excel
- Sparklines in Excel
- Spell Check in Excel
- Split Panes in Excel (Horizontally, Vertically, Cross Split)
- Status Bar in Excel
- Text to Columns in Excel
- Timeline in Excel
- Toolbar on Excel
- Track Changes in Excel
- Trend Line in Excel
- Two-Variable Data Table in Excel
- Watch Window in Excel
- Wrap Text in Excel
- XML in Excel
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tips (178+)
- VBA (162+)
While working with huge chunks of data we might have some cells which are blanks, these cells come in our tables and reports too and it is hard to identify them manually, we use conditional formatting for blank cells which can be accessed from the new rules of conditional formatting tab and then in the cells that contain blank value which is the second option.
How to Apply Conditional Formatting for Blank Cells?
The conditional formatting for blank cells in excel means a certain cell which doesn’t have a value assigned to it. It doesn’t have a numeric, alphabetic or any sort of character as value. It is just blank.
Conditional Formatting in excel means we change the format of a certain cell in excel based on certain conditions. But that means the cell in any data table or excel worksheet contains any value. Then onwards we set a certain condition in it to change the format of it. But what if the cell is blank? How do we change the format of a blank cell? This we will learn about this topic.
There are two different ways to format a blank cell in excel. They are:
- Using Conditional Formatting Tab.
- Using ISBLANK Function.
The first method is the pretty simple process from where we do the normal conditional formatting in any scenarios while the second method is a function provided by excel is a function which checks whether the given reference cell is blank or not and then we apply conditional formatting to it.
Steps to Conditional Format for Blank Cells
Let us learn to use these methods by a few examples and it will be more clear to us.
Consider the following data given below,
In the above data, we can see that we have marks for five students but not for every subject. Some of the cells are left blank. Now we will use both methods to do conditional formatting on the blank cells.
Step #1 – Select the data range in which we want to use conditional formatting, In the home tab, under the styles section click on conditional formatting, Click on New rule.
Step #2 – When we click on new rule another wizard box appears, which is a new formatting rule tab,
Step #3 – Click on the second option which is to format cells which only contain,
Step #4 – Click on Format cells only with on the left-hand side below and select the option for blanks.
Step #5 – Click on Format and select any option we desire to format the cells.
Step #6 – I have selected a blue color as my formatting option. As we click on Ok and see the result.
The above method was the general method to use conditional formatting. Now we will use the Isblank function to do the same. We repeat the same steps go to conditional formatting in the styles section under the home tab.
Now click on New rules from the wizard box and then select the option which is on the last of the option from the rule types.
In the formula Box write the following formula,
Click on Format and select the desired formatting, for instance, I am choosing the blue color this time.
When we click on OK and see the result that the blank cell has been identified and has been formatted to blue color.
Let us use another example to understand the basics once again of how to use conditional formatting in blank cells. Consider the following data given below.
We have some of the cells which are blank and we will highlight them to different colors by using the conditional formatting option given by excel.
Select the data range where we want to apply conditional formatting, Now click on Conditional formatting from the styles section in the home tab,
From the wizard box that appeared click on the new rule and another dialog box appears.
Click on the second option given from the rule type options.
In the format cells with the select blank as the option,
Click on Format and select the desired color for the formatting, for instance, I have chosen a red color.
Click on OK and see the result for the blank cells.
Now let’s try to put a value in one of the blank cells and see the result. For example in Cell C3 which is blank put a value “SP”.
When we click anywhere in the excel we see that the color has changed to white again as the cell is not blank anymore.
Explanation of Conditional Formatting for Blank Cells
Conditional Formatting of Blank Cells basically means changing the colors of the blank cells if they are blank.
It helps us to identify which cells are blank in a large sum of data rather than applying the auto filter to each column in excel we can easily see through the different colors.
Things to Remember
There are a few different things we need to remember in conditional formatting in blank cells:
- Conditional Formatting to blank cells means changing the color of the blank cells.
- We can also use the formula of Isblank or If the function to format the blank cells.
- Once the cells are no longer blank then the formatting is automatically removed from them.
You can this Conditional Formatting Blank Cells template here – Conditional Formatting Blank Cells Template
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 may learn more about excel from the following articles –
- Highlight Dates using Conditional Formatting
- Conditional Formatting in VBA
- VBA COUNTIF Function
- Insert Image in the Excel
- Format Number in VBA
- How to Apply Conditional Formatting Based on Another Cell Value?
- How to Apply Conditional Formatting in Pivot Table
- Apply Format Numbers to Millions in Excel
- How to Countif not Blank in Excel?