Prantick Bala
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.
Download Practice Workbook
Feel free to download the workbook from the link below.
6 Ways to Remove Formatting in Excel Without Removing Contents
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.
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.
Similar Readings:
- How to Remove Table Formatting in Excel (2 Smart Ways)
- Remove Formulas in Excel: 7 Easy Ways
- How to Remove Numbers from a Cell in Excel (7 Effective Ways)
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
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.
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.
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.
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.
