How to Remove Formatting in Excel Without Removing Contents

“Removing formatting in Excel without removing contents” means separating the raw data (text, numbers, formulas) from formatting (font styles, colors) in a spreadsheet. This is done to keep the data while eliminating any applied formatting.

In this free Excel tutorial, we will consider 7 cases where we will remove formatting in Excel without removing contents.

As you can see in the GIF above, we have removed the formatting of the contents in the range B4:F13.

In this article, you will learn how to remove formatting in Excel without removing contents for a range, a table, or an entire worksheet. We will use different commands like Clear Formats, Find & Replace, Conditional Formatting, etc. to remove formatting.

How to Remove Formatting in Excel Without Removing Contents: 7 Cases

We can apply the commands Clear Formats, Find & Replace, Conditional Formatting, etc. to remove formatting in Excel without removing contents. We can apply these methods in different cases like removing formatting in Excel without removing contents from selected cells, a certain range, a table, or an entire worksheet.

1. Remove Formatting with ‘Clear Formats’ Command from Selected Cells

With the use of the Clear Formats command, we can remove formatting from selected cells without removing contents.

➤ 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. Remove Formatting with Keyboard Shortcut from a Certain Range

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 sequentially.

➤ 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.

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 colors. Now, we want to remove the formatting only from the blank cells.

➤ First, select your dataset and go to the Home tab.

➤ Click on Go To Special… from Find & Select.

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.

➤ Go to Home > Editing > Clear and select Clear Formats to remove the formatting of these blank cells.

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 three types of formatting; green, yellow, and blue colors. We will remove the formats of the green cells.

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

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

➤ Click on the Format option.

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 green-colored cells is removed.

➤ Lastly, close the Find & Replace window.

Now, you can see the formats of the green-colored cells are removed while the contents of these cells are still in place.

6. Remove Conditional Formatting without Removing Contents

We can clear the applied conditional formatting to remove cell formatting without removing contents. In the following dataset, I have selected the entire row based on the Completed text from the Status column

➤ 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.

7. Remove Table Formatting Without Removing Contents

We can remove the table formatting and keep the contents unchanged.

➤ Select a cell from the table and click on the extension button from the Table Styles group.

➤ Now, click on Clear.

➤ Thus, we can remove the table formatting without removing contents.

Download Practice Workbook

Feel free to download the workbook from the link below.

Remove Formatting.xlsx

In this tutorial, we have discussed how to remove formatting in Excel without removing the data. Whether it’s a small section, a table, or the whole sheet, we have demonstrated several method, like using Clear Formats, Find & Replace, or playing with Conditional Formatting.