What is Conditional Formatting in Excel?
Conditional formatting in Excel makes it easy to highlight values in your worksheet, making that cell easy to identify. Formatting changes the appearance of the cell range either based on condition or criteria. Basically, you can use conditional formatting to highlight cells containing values that meet certain conditions.
Types of Conditional Formatting in Excel
Conditional formatting allows you to apply formatting to the cells in your worksheet, there are formatting presets available in Excel. They include Highlight cell rules, Top/Bottom rules, Data bars, Color scales, Icon sets.
- Highlight Cell Rules: These rules allows you to change the appearance of the cells in a range based on specified conditions. They are mostly based on certain rules such as specified numerical values, matching text, calendar dates, unique values, etc.
- Top/Bottom Rules: These rules are used to apply specific formatting to the cells in your worksheet.
- Data Bars: These are horizontal bars added to a cell, they are like bar graphs.
- Color Scales: To change the color of each cell based on its value. There is a two-three color gradient on the color scales. The color scales has high value and a low value on the color scale.
- Icon Sets: They helps users to add a specific icon to each cell based on its value.
How to Remove Only Conditional Formatting in Excel
Most times, while working in your Excel sheet, you might not need the conditional formatting rules, hence the need to remove conditional formatting. In this article, we will share with you a step-by-step guide on how to remove only conditional formatting in Excel.
One way to remove conditional formatting from Excel is by using the clear function in your Excel sheet. With that, you can remove from both single cells and multiple cells.
How to Remove Conditional Formatting from a Single Cell in Excel
Remove the conditional formatting from a single cell:
- First select the cell you want to remove conditional formatting from.
- Then access the Conditional Formatting Menu by clicking on the Home tab.
- Then Click on Conditional Formatting from the option that appears.
- Then click on Clear Rules.
- This will clear the rules from selected cell.
How to Remove Conditional Formatting from Multiple Cells in Excel
To remove all conditional formatting from the multiple cells:
- Click on the Conditional Formatting button on the HOME tab.
- Click on Clear Rules.
- Then click Clear Rules from the Entire Sheet.
How to Remove Only Conditional Formatting in Excel Using VBA
Visual Basic for Applications(VBA) is a programming language owned by Microsoft and installed into the Microsoft office suite. Users can use VBA to author, format, and manipulate their documents. With VBA, you get to write a code that helps you remove only conditional formatting in your Excel sheet.
Remove conditional formatting with VBA code by following this method:
VBA can be accessed from the Developer tab, so you have to first enable the developer tab by:
- Go to the File tab, select Options to open the Options dialog box.
- Click on Customize Ribbon on the left side of the dialog box.
- Under Choose commands, select Popular Commands.
- Navigate to the Customize the Ribbon option on the right side of the dialog box.
- Select Main Tabs in the drop down list box, and then select the Developer checkbox.
- Choose OK.
When this is done, Open Excel to the worksheet you want to remove the formatting from and choose the Developer tab in the ribbon.
- Click on Developer.
- Select Visual Basic.
- A new Microsoft Visual Basic for applications window will be displayed.
- Click on Insert.
- Click on Module, and input the VBA code you have written into the Module.
- Click the F5 key on your keyboard to run the code, a prompt box will come up reminding you to select a range that you want to use.
- Then click the OK button, and all conditional formatting in the selected range will be removed.
However, there is a VBA shortcut you can use:
- Open the worksheet you want to work on.
- Press the “Alt + F11” keys in your keyboard to access the VBA editor.
- Select the code you want to run.
- Click the “Run” button or press “F5” to run the code.
- The conditional formatting will be removed from the selected cells.
How to Remove Conditional Formatting in Excel Using a Keyboard Shortcut
You can use a keyboard shortcut to remove from conditional formatting, here’s how to:
- Select the cell range you wan to work on.
- Then click on Alt + H > L > C > S
This shortcut uses the same option we used in the first method, just with keyboard keys.
How to Remove Only Conditional Formatting in Excel and Leave All Other Formatting Intact
Use the Clear Option from the Quick Analysis Tool to remove only Conditional Formatting in Excel and leave all other formatting intact.
When you select the range of cells with the conditional formatting, Excel shows you the Quick Analysis Tool at the bottom right. And from here, you use the “Clear Format” option to remove the conditional formatting. This will leave other formatting intact and clear only conditional formatting.
Problems with Removing Conditional Formatting Rules
Some users experience issues while trying to remove conditional formatting in their Excel sheet. To avoid such issues:
- Ensure you are selecting the cells that have the conditional formatting applied. If you select only some cells, it will not remove all the formatting and that becomes an issue.
- Also your cells might be locked, and when they are locked, it is difficult to remove conditional formatting. In this case, ensure protected cells are unprotected before proceeding.
- If the conditional formatting rule is complex, it might take a lot of time to remove conditional formatting. However, using the “Clear rules” option helps you to remove the formatting easily.
- Lastly, if it proves even more difficult. Copy and paste your data into a new worksheet and start afresh.
Before you go…
To improve your knowledge, tips and hacks on Microsoft Excel, Check out our recommended articles on Microsoft Excel:
FAQs
How to remove conditional formatting duplicate values in Excel?
Click on the Data Tab > Select Data Tools > Select the Remove Duplicates option
How to copy conditional formatting rules in Excel?
Select the control that has the conditional formatting you want to copy > On the Home Tab in the Clipboard group, double-click Format Painter > Click all the controls to which you want to copy the formatting.
How to copy conditional formatting in Excel to another sheet?
If you have multiple worksheets you want to apply the same conditional formatting to, use the Paste Special feature to do this.
- Select the cell or cells that contain the conditional formatting you want to copy.
- Ctrl+C to copy the contents of the cell.
- Right-click on the sheet tab you want to apply the formatting to.
- Select All Sheets from the context menu.
- Then select the cells you want to apply the conditional formatting.
- Right-click on the selected cells and choose Paste Special from the context menu.
- In the Paste Special dialog box, select Formats and click OK.
How to copy conditional formatting color to another cell in Excel?
If you want to copy conditional formatting color to another cell, you have to use the format painter option.
- In the Navigation Pane, right-click the form to be changed.
- Then click on Layout view or Design view.
- Next, select the control that has the conditional formatting you want to copy.
- Navigate to the Home tab and select Format Painter.
- Click the cell you want to copy the formatting to.
Save the form and go to the Home tab. Then click on the Views group, and select Form View or Report View to test the formatting you copied.
