Skip to main content

How to remove conditional formatting from blank cells in Excel?

Author: Siluvia Last Modified: 2020-06-17

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:

1). In the Select a Rule Type section, select Format only cells that contain;
2). In the Edit the Rule Description section, select Blanks in the first Format only cells with drop-down list;
3). Keep the format in the No Format Set status;
4). Click the OK button. See screenshot:

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.

1). Select Use a formula to determine which cells to format in the Select a Rule Type section;
2). Copy and paste the formula =ISBLANK(A1)=TRUE into the Edit values where this formula is true box;
3). Keep the No Format Set status;
4). Click the OK button. See screenshot:

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:

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

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...

Description


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!
Comments (9)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
This doesn't help at all for Excel Online (Sharepoint/Teams) and its absolutely horrendously crap formatting tool.
This comment was minimized by the moderator on the site
I have been trying to figure the 2nd one - how to exclude formatting from cells that contain formulas for several years.  Thanks for such explicit instructions!
This comment was minimized by the moderator on the site
amazing thanks so much! :)
This comment was minimized by the moderator on the site
Hi,I use excel 2016 and while using pivot and applied the conditional formating which i used the criteria 0 - 100 values in the cell to be shown as red colour but am getting red in blanks cell where there is no value. Please suggest
This comment was minimized by the moderator on the site
Perfect, thank you
This comment was minimized by the moderator on the site
Thanks! I've been trying this for the last two days. A life saver, just in time for a presentation.
This comment was minimized by the moderator on the site
Thank you - I've been trying to do this for ages - so much help!!
This comment was minimized by the moderator on the site
thank you! you just saved me so much time!
This comment was minimized by the moderator on the site
Office 365. How do I remove conditional format from a range of cells (not next to each other) from multiple worksheets. Urgent help required.
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations