How to remove conditional formatting from blank cells in Excel?
When there are blank cells in the selected range, the blank cells will get formatted when you use formula in the conditional formatting rule. For removing the conditional formatting from blank cells (do not show conditional formatting in the blank cells) in Excel, there are two methods for you. Please browse the below tutorial for more details.
Remove conditional formatting from blank cells with adding a new rule of blanks
Remove conditional formatting from blank cells with formula
Remove conditional formatting from blank cells with adding a new rule of blanks
In this section, you will learn how to create a new rule with blanks for removing conditional formatting from blank cells. Actually, this cannot really remove the format from the blank cells, it just adds a new rule with no format set for the blank cells.
1. Select the range with the conditional formatting you have created before.
2. Click Conditional Formatting > Manage Rules under Home tab. See screenshot:
3. In the Conditional Formatting Rules Manager dialog box, click the New Rule button.
4. In the New Formatting Rule dialog box, you need to:
5. When it returns to the Conditional Formatting Rules Manager dialog box, check the Stop If True box for the new Blanks rule, and then click the OK button.
6. Then you will see all blank cells in the selected range are added with no format set.
Remove conditional formatting from blank cells with formula
Besides the above method, here I will show you a formula for removing conditional formatting from blank cells. The same as above, it gives the blank cells a no format set conditional rule.
1. Select the range you have added conditional formatting.
2. Then go to the Home tab, click Conditional Formatting > Manage Rules.
3. In the Conditional Formatting Rules Manager dialog box, click the New Rule button.
4. In the New Formatting Rule dialog box, please do as follows step by step.
Note: The A1 in the formula is the first cell in your selected range. You need to change to your selected one.
5. In the Conditional Formatting Rules Manager dialog box, check the Stop If True box for the new created rule, and then click the OK button.
Related articles:
- How to remove (temporarily hide) conditional formatting when printing in Excel?
- How to ignore blank or zero cells in conditional formatting in Excel?
- How to conditional format dates less than/greater than today in Excel?
- How to conditional format negative percentage in red in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
