With conditional formatting in MS Excel, you can change a cell’s appearance with specified conditions.
While this feature helps to visualize data more efficiently, it can also overcrowd the sheet and reduce its readability. Fortunately, there are multiple ways to remove the useless formatting.
Throughout this article, I will discuss the basics of conditional formatting and demonstrate five methods to delete the formatting from a particular cell or the entire sheet.
So, let’s get started!
What is Conditional Formatting, and Why Should You Remove It?
Conditional formatting is a unique feature of SpreadSheet programs that assists in applying special formatting to selected cells based on specific criteria or rules. It helps users to highlight and differentiate data stored in a spreadsheet. By creating a rule, you can apply it to any cell.
While this feature can dramatically enhance the cell’s appearance regarding your specified conditions, it can make it harder to navigate through data because of excessive formatting.
Moreover, analyzing and reading the data will be challenging if you apply too much conditional formatting to an Excel spreadsheet.
In such scenarios, removing the conditional formatting will help to keep the spreadsheets clean and readable. When you apply a ton of conditional formatting to an enormous data set, you will experience Excel freezing or slow performance.
Fortunately, you can effortlessly delete only conditional formatting in Excel and leave all other formattings intact by performing the procedures of the following section.
How to Remove Conditional Formatting in Excel
To remove the conditional formatting from individual cells, select all the cells with formatting, click on the Quick Analysis icon from the bottom, and choose Clear Format. To remove from the entire sheet, navigate to Home > Conditional Formatting > Clear Rules > Clear Rules from Entire Sheet.
You can accomplish the same using keyboard shortcuts, the Clear Formats option, and VBA code. For step-by-step instructions, go through the following methods.
Here are the methods to delete conditional formatting in Microsoft Excel:
1. Use the Quick Analysis Tool
You should use the Quick Analysis tool to remove the conditional formatting from a cell or multiple cells.
To do so, select the cell or the group of cells, and you will see an icon at the bottom right of the selected cells. Simply click on that analysis tool icon and choose the Clear Format option.
It will instantly clear all the conditional formatting from the highlighted cells. However, it will remove only the conditional formats but not the effects.
2. Use Keyboard Shortcuts
Most Excel folks use keyboard shortcuts instead of navigating through a ton of options for their basic and advanced tasks.
You can also use keyboard shortcuts to delete conditional formatting. Before applying the shortcuts, highlight the range of cells and press the following shortcut keys sequentially.
Alternatively, if you need to clear conditional formatting from the entire Excel Sheet, keep the mouse cursor anywhere inside the sheet and press the following buttons one after another.
It will immediately delete all the conditional formatting from the selected sheet. If it bothers you to press the buttons consecutively or is hard to remember, the following method is the best option.
3. Use Clear Rules Function
The built-in Clear Rules feature of Microsoft Excel can delete conditional formatting from selected cells or the whole sheet. For step-by-step instructions, perform the following steps.
- Highlight the cells that contain unnecessary formatting.
- Navigate to Home > Conditional Formatting > Clear Rules.
- Choose Clear Rules from Selected Cells to delete formatting from the selected cells.
- Choose Clear Rules from Entire Sheet to clear formatting from the entire worksheet.
If you select the second option, you don’t need to choose the cells before applying the action.
However, you must select the cells if you choose the first option for clearing the conditional formatting.
4. Use Clear Formats Option
The Clear (eraser shape) button in the Editing group under the Home tab can remove content, comments, and hyperlinks. This option is also used to remove conditional formatting from the selected cells.
Here’s how to clear conditional formatting using the Clear Format option:
- Select all the cells with formattings.
- Switch to the Home tab.
- Click the Clear option under the Editing group.
- Select Clear Formats from the menu.
It will remove only the formatting that is applied to the selected cells but keep other cell information intact, such as fill color or text details.
5. Use Visual Basic for Applications
If you’re more comfortable executing Excel VBA codes, I have a short code for you to delete unnecessary conditional formatting.
Here are the steps to clear formatting using VBA code:
- Navigate to Developer >> Visual Basic.
- Click the View tab and select Immediate Window or press Ctrl + G.
- Paste the following command inside the window:
- For Each ws In Worksheets: ws.Cells.FormatConditions.Delete: Next ws
- Keep the cursor at the end of the line and press Enter.
Now close the VBA, and you will see the conditional formatting is removed from the entire worksheet.
Wrap Up
All the aforementioned methods will do the same (remove the conditional formatting). You can choose the one that suits your requirements and is easy to follow.
If you prefer to use the VBA code for deleting the formatting, ensure to create a backup before executing the code. Otherwise, you won’t be able to go back to the previous version of your sheet.
Do you know any other method that can clear the conditional formatting? If so, let me know in the comment section.
