In Excel, you can easily remove formatting from cells without removing the contents of the cells following some simple steps. In this article, we’ll show you how you can remove formatting in Excel without removing contents in 6 different situations.
Let’s say, our dataset has some cells formatted. Now, we will remove these formatting without deleting the contents.
How to Remove Formatting in Excel Without Removing Contents: 6 Cases
1. Remove Formatting from Selected Cells
➤ First, select the cells from where you want to remove formatting
➤ Then, go to Home > Editing > clear and select Clear Formats.
Now, you will see the formatting of your selected cells has been removed but the contents are still there.
Read More: How to Clear Formatting in Excel
2. Keyboard Shortcut to Remove Formatting
The easiest way to remove formatting from selected cells is using a keyboard shortcut.
➤ First, select the formatted cells.
➤ Then, press ALT+H+E+F
As a result, you will see all the formatting of the selected cells are removed.
3. Remove Formatting from Entire Dataset
You can also remove formatting from the entire worksheet without removing any contents.
➤ First, select all the cells by clicking on the arrow sign from the intersect point of the row and column number.
➤ After that, go to Home > Editing > clear and select Clear Formats.
As a result, all the formatting of your entire dataset will be removed.
Read More: Difference Between Delete and Clear Contents in Excel
Similar Readings:
- How to Clear Multiple Cells in Excel
- How to Clear Cells with Certain Value in Excel
- How to Clear Cell Contents Based on Condition in Excel
4. Remove Formatting From Blank Cells
Now, let’s see how you can remove formatting from the blank cells. Consider the following dataset, where we have some blank cells formatted with green color. Now, we want to remove the formatting only from the blank cells.
➤ First, select your dataset and press F5 key.
It will open the Go To window.
➤ Click on the Special box from the Go To window.
Now, the Go To Special window will be opened.
➤ Select Blanks and click on OK.
Now you can see all the blank cells of your dataset are selected.
To remove the formatting of these blank cells,
➤ Go to Home > Editing > clear and select Clear Formats.
Now you can see, formatting from the blank cells is removed.
Read More: How to Clear Contents Without Deleting Formulas Using VBA in Excel
5. Remove Specific Cells’ Formatting without Removing Contents
In this section, we’ll show you how to remove specific cells’ formatting without deleting the contents. Suppose in our dataset we have two types of formatting; one is with green color and another is with yellow color. We will remove the formats of the yellow cells.
➤ First, Go to Home > Editing > Find and Select > Find.
It will open the Find and Replace window.
➤ Now, click on Options in this window to expand it.
After that, you can see the Format box in the Find and Replace window.
➤ Click on the Format box.
As a result, a new window, named Find Format will appear.
➤ Go to the Fill tab and select the color of the cells from where you want to remove formatting.
➤ At last press OK.
Now in the Find and Replace window, you will see your selected color in the Preview box.
➤ Click on Find All.
As a result, a list of cells containing the specific format will appear at the bottom of the Find and Replace window.
➤ Now, select all the cells from the list.
➤ After that, go to Home > Editing > clear and select Clear Formats.
As a result, the formatting of yellow colored cells is removed.
➤ Lastly, close the Find and Replace window.
Now, you can see the formats of the yellow colored cells are removed while the contents of these cells are still in place.
Read More: Excel VBA to Clear Contents of Range
6. Remove Conditional Formatting without Removing Contents
To remove Conditional Formatting from your dataset without removing contents,
➤ First, select your entire dataset.
➤ Then, Go to Home > Conditional Formatting > Clear Rules and select Clear Rules from Selected Cells.
As a result, conditional formatting from the selected cells will be removed without removing any contents.
Download Practice Workbook
Feel free to download the workbook from the link below.
Conclusion
Here we’ve listed several approaches to remove formatting without removing contents, hope these will help you to remove formatting in Excel without removing contents. If you face any confusion please leave a comment.
