The conditional formatting feature in Microsoft Excel can be a very helpful feature for identifying and sorting data.
But if something changes, or you are working on someone else’s file, it might be necessary to remove all of those conditions.
Our tutorial below will show you how to clear rules from an entire sheet in Excel 2010.
How to Get Rid of All Rules in Microsoft Excel
- Open the spreadsheet.
- Select the Home tab.
- Click Conditional Formatting.
- Choose Clear Rules.
- Click Clear rules from entire sheet
Our guide continues below with additional information on how to clear rules from an entire worksheet in Excel, including pictures of these steps.
Different people use Microsoft Excel spreadsheets for different things, and many users find their own ways to achieve their desired results
One commonly used aspect of Microsoft Excel 2010 is conditional formatting, which allows you to change elements and formatting rules for a specific cell or set of cells based on values in another cell.
As a result of someone using this feature, you may have difficulty changing the appearance of cells that contain conditional formatting.
This is particularly true of Excel 2010 spreadsheets that are sent to you by another person or that you are working on collaboratively.
If it becomes necessary for you to change a cell that contains some conditional formatting rules, you might become frustrated as you search for ways to remove these settings.
Fortunately, Excel includes an option that allows you to clear all of the conditional formatting rules from an entire spreadsheet, allowing you to freely change the formatting and appearance of the cells in your spreadsheet.
Related: Our how to strikethrough in Excel guide will show you how to draw a line through text in a cell.
How to Remove all Conditional Formatting Rules From an Excel 2010 Sheet (Guide with Pictures)
While conditional formatting is useful for the person that sets the rules upon which it is based, those rules are almost meaningless to a second person that has to work on the spreadsheet.
As a result of this, removing some of the formattings could thereby make the rest of the formatting obsolete, or the conditional formatting might conflict with appearance changes that you want to apply to the document.
Rather than removing formatting from individual cells, the simplest solution is to remove all of the formatting from the entire sheet.
In the example image below, I have created a very small set of data.
The company wants to evaluate regions where the offices are producing a per employees revenue that is lower than a specified threshold. Conditional formatting has been applied to highlight the offices that are below that threshold.
I have received this spreadsheet without any explanation of the formatting, so the highlighted cells mean nothing to me. Repeated attempts to remove the background fill color have been unsuccessful because that fill color is set by conditional formatting.
Step 1: I can find a utility to remove all of the conditional formatting rules from this entire sheet by clicking the Home tab at the top of the window.
Step 2: Next, I will click the Conditional Formatting drop-down menu in the Styles section of the ribbon, then I will click Clear Rules, followed by Clear rules from entire sheet.
This will remove all of the pre-existing rules and formatting from the spreadsheet, allowing you to make your own changes to the appearance and formatting of your cells.
If you have made the conditional formatting rules that are applied to the sheet and you use the method outlined above, all of the rules you created will be gone. You will need to recreate them and reapply them to the cells that you require.
Now that you know how to clear rules from an entire sheet in Microsoft Excel, you will be able to resolve similar issues that you may encounter in the future.
Matthew Burleigh has been writing tech tutorials since 2008. His writing has appeared on dozens of different websites and been read over 50 million times.
After receiving his Bachelor’s and Master’s degrees in Computer Science he spent several years working in IT management for small businesses. However, he now works full time writing content online and creating websites.
His main writing topics include iPhones, Microsoft Office, Google Apps, Android, and Photoshop, but he has also written about many other tech topics as well.