How to Remove Formatting in Excel Without Removing Contents

Get FREE Advanced Excel Exercises with Solutions!

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.

dataset


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.

Remove Formatting without Removing Contents

Now, you will see the formatting of your selected cells has been removed but the contents are still there.

format deleted

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.

Remove Formatting without Removing Contents


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.

all cells

➤ After that, go to Home > Editing > clear and select Clear Formats.

Remove Formatting without Removing Contents

As a result, all the formatting of your entire dataset will be removed.

without format

Read More: Difference Between Delete and Clear Contents in Excel


Similar Readings:


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.

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.

go to

Now, the Go To Special window will be opened.

➤ Select Blanks and click on OK.

go to special

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.

Remove Formatting without Removing Contents

Now you can see, formatting from the blank cells is removed.

Remove Formatting without Removing Contents

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.

data set

➤ First, Go to Home > Editing > Find and Select > Find.

find

It will open the Find and Replace window.

➤ Now, click on Options in this window to expand it.

find and replace

After that, you can see the Format box in the Find and Replace window.

➤ Click on the Format box.

options

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.

format

Now in the Find and Replace window, you will see your selected color in the Preview box.

➤ Click on Find All.

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.

selecting cells

➤ After that, go to Home > Editing > clear and select Clear Formats.

Remove Formatting without Removing Contents

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.

Remove Formatting without Removing Contents

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.

Remove Formatting without Removing Contents

As a result, conditional formatting from the selected cells will be removed without removing any contents.

Remove Formatting without Removing 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.


Further Readings

Prantick Bala
Prantick Bala

Hi, I'm Prantick Bala, currently working and doing research on Microsoft Excel in Softeko.com. I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo