In this article, we would show 2 different ways to remove conditional formatting but keep the format in Excel. We frequently use conditional formatting rules to visualize our data more precisely to viewers. But if we get rid of the rules, Excel also removes the cell formats. Let’s dive into the following examples where we would clear the rules but keep the format of cells.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
2 Methods to Remove Conditional Formatting but Keep the Format
Let’s introduce our dataset first. The dataset contains a sale list of a super shop with product name, unit price, and sale quantity. We would highlight the products that have a sale quantity of more than 50 using Excel’s conditional formatting.
To do that,
- Select the quantity column (cells D5:D14).
- Then from the Home tab of the Excel Ribbon, click the Conditional Formatting
- From the dropdown, choose the Highlight Cells Rules.
- And then choose the Greater Than option.
- In the Greater Than window, put 50 and hit OK.
- What we see is the cells having quantity greater than 50, now get a red color background.
Let’s check the conditional formatting by changing any cell value of the Quantity column that is less than 50 to a value greater than 50. The following screenshot shows how the background color of cell D7 turned red when its value changed from 17 to 56.
Remove Conditional Formatting and Keep the Format
Now we want to remove the conditional formatting rules while keeping the format at the same time. As usual, when we remove the conditional formatting rules, it removes the formatting with it.
In the following methods, we’ll show how to remove the formatting rules but keep the format using the above dataset.
1. Use of the Office Clipboard to Remove Conditional Formatting but Keep the Format in Excel
In this method, we would use the Office Clipboard to keep the format of cells while removing the conditional formatting. Let’s follow the steps below-
- Select the whole dataset.
- Press Ctrl + C to copy the selected cells.
- Now select the cell where we want to paste the copied cells.
- From the Home tab, click on the Clipboard icon.
- Click on the copied item on the Clipboard panel.
- As the copied cells are pasted, close the Clipboard panel.
- Now delete the old dataset and reposition the new one.
Now if we change the value of a cell of the Quantity column to greater than 50, it doesn’t change its format.
Read More: How to Copy Conditional Formatting to Another Sheet (2 Quick Methods)
- Change Font Color Based on Value of Another Cell in Excel (2 Methods)
- Apply Conditional Formatting to Each Row Individually: 3 Tips
- Excel Conditional Formatting Text Color (3 Easy Ways)
- 4 Quick Excel Formula to Change Cell Color Based on Date
- How to Compare Two Columns in Excel For Finding Differences
2. Run a VBA Code to Remove Conditional Formatting but Keep the Format in Excel
Using VBA code, we can also remove conditional formatting while preserving the cell format. Follow the simple steps below to accomplish this.
- Select the cells that are conditionally formatted (in our dataset, cells D5:D14).
- Go to the Developer tab from the Excel Ribbon.
- Click the Visual Basic option.
- In the Visual Basic for Applications window, click the Insert dropdown to select the New Module.
- Now copy and paste the following code in the visual basic editor.
Sub RemovConditionalFormattingButKeepFormat() For Each cell In Selection With cell .Font.FontStyle = .DisplayFormat.Font.FontStyle .Font.Strikethrough = .DisplayFormat.Font.Strikethrough .Interior.Pattern = .DisplayFormat.Interior.Pattern If .Interior.Pattern <> xlNone Then .Interior.PatternColorIndex = .DisplayFormat.Interior.PatternColorIndex .Interior.Color = .DisplayFormat.Interior.Color End If .Interior.TintAndShade = .DisplayFormat.Interior.TintAndShade .Interior.PatternTintAndShade = .DisplayFormat.Interior.PatternTintAndShade End With Next Selection.FormatConditions.Delete End Sub
- Press F5 to run the code, that’s it.
Now we have successfully removed the conditional formatting while keeping the formatting as it was. Now if we change the value of a cell of the Quantity column to greater than 50, it doesn’t change its format.
- In the VBA code, we used the DisplayFormat property of Excel to keep the cell format as it is. And finally, we applied the FormatConditons object to delete the conditional formatting.
- To view the code, click the right button on the sheet name and select the View Code
Related Content: How to Copy Conditional Formatting to Another Workbook in Excel
Now, we know how to remove conditional formatting while keeping the cell formats in 2 different ways. Hopefully, it would help you to use these methods more confidently. Any questions or suggestions don’t forget to put them in the comment box below.
- How to Apply Conditional Formatting to Multiple Rows (5 Ways)
- Conditional Formatting Entire Column Based on Another Column(6 Steps)
- How to Do Conditional Formatting with Multiple Criteria (11 Ways)
- Conditional Formatting on Text that Contains Multiple Words in Excel
- How to Change Text Color with Formula in Excel (2 Methods)
Thanks, was trying to find this and it took way longer to find than it should have. Turns out all the Macros I didn’t work with latest version of Excel.
One suggestion, this didn’t work for me until I added “Dim Cell as Range” as I have variable declaration on, so might want to add that line to eliminate that error mode.
Hello JUSTIN! The code is working perfectly from my side and the output has been shown in the practice workbook. And, it is not necessary to add code “Dim Cell as Range” here as we are not working with the cell value. But it is a good practice to declare variables at first.
We have a large collection of Excel-related blogs that will help you to solve many more problems. Browse them and let us know your opinion in the comment section. Thank You!