How to Clear Formatting in Excel: A Step-by-Step Guide

Formatting in Excel refers to how data is presented in a spreadsheet. This includes cell colors, fonts, borders, alignment, and number formats such as dates, currencies, and percentages.

Good formatting makes spreadsheets easier to read and interpret. For example, conditional formatting can help highlight high and low values in a sales report, while number formatting can make a value like 1000000 easier to read as 1,000,000.

However, formatting can also become messy. Spreadsheets passed between colleagues often contain inconsistent styles, such as bold text in some cells, random fill colors in others, or numbers displayed in the wrong format.

Example of inconsistent formatting in an Excel worksheet.

The good news is that you can remove these inconsistencies without deleting your actual data. In this guide, you will learn how to clear formatting in Excel using different methods, depending on what you want to remove and what you want to keep. 

How to Clear Formatting in Excel Using the Ribbon

The easiest way to clear formatting in Excel is by using the Clear Formats option on the ribbon. This method removes formatting from selected cells while keeping the actual values, text, and formulas intact.

In the example below, the revenue value for ‘Data Analytics’ is displayed as a date because the cell has the wrong number format applied. We will use Clear Formats to remove that formatting without deleting the value.

To clear formatting using the ribbon:

1. Select the cell or range of cells you want to clean.

Selected Excel cell with incorrect date formatting applied to a revenue value. 

2. Go to the Home tab

3. In the Editing group, click Clear.

Using Clear Formats from the Home tab to remove formatting in Excel. 

4. Select Clear Formats from the dropdown menu.

After applying Clear Formats, the value remains in the cell, but the visual styling and number formatting are removed. 

Excel Shortcut to Clear Formatting

If you clear formatting often, using a keyboard shortcut can save time. Instead of navigating through the ribbon each time, you can use Excel’s ribbon shortcut sequence.

To clear formatting with a shortcut on Windows:

1. Select the cell or range of cells you want to clean.

2. Press Alt, then H, then E, then F.

This performs the same action as Home → Clear → Clear Formats. It removes formatting while keeping the underlying values, text, and formulas intact.

This shortcut is available on Windows. On Mac, Excel does not have an exact equivalent for this ribbon shortcut, so the easiest option is to use the Home → Clear → Clear Formats menu. 

How to Remove All Formatting (Including Content)

It is important to understand the difference between Clear Formats and Clear All.

Clear Formats removes only the appearance of a cell. This includes colors, fonts, borders, and number formatting. The underlying values, text, and formulas remain unchanged.

Clear All removes everything in the selected cell or range. This includes the formatting, values, text, formulas, comments, and hyperlinks.

Use Clear All only when you want to completely reset a cell or range.

To use Clear All:

1. Select the cell or range you want to clear.
2. Go to the Home tab.
3. In the Editing group, click Clear.
4. Select Clear All.

If your goal is to remove only the visual styling while keeping your data intact, Clear Formats is the appropriate choice. However, if the data is no longer needed and you want to reset the cell entirely, Clear All is the better option.

How to Clear Formatting from an Entire Worksheet

If unwanted formatting is scattered across your worksheet, you can clear formatting from the entire sheet at once. This is useful when you receive a heavily formatted file and want a clean slate before applying your own styles.

To clear formatting from an entire worksheet:

1. Press Ctrl + A to select all cells in the worksheet.

2. Go to the Home tab.

3. In the Editing group, click Clear.

4. Select Clear Formats.

Excel removes the formatting across the worksheet while keeping the cell values, text, and formulas intact. 

How to Clear Conditional Formatting in Excel

Conditional formatting is different from standard formatting because it is controlled by rules. For example, Excel can automatically highlight cells above a certain value or change cell colors based on performance.

Because conditional formatting is rule-based, clearing regular formatting may not always remove it. To fully remove it, you need to clear the conditional formatting rules.

To clear conditional formatting from selected cells:

1. Select the cell or range of cells.
2. Go to the Home tab.
3. In the Styles group, click Conditional Formatting.
4. Hover over Clear Rules.
5. Select Clear Rules from Selected Cells.

To clear conditional formatting from the entire worksheet:

1. Go to the Home tab.
2. Click Conditional Formatting in the Styles group.
3. Hover over Clear Rules.
4. Select Clear Rules from Entire Sheet.

This removes the conditional formatting rules while keeping your actual data intact. 

How to Remove Table Formatting in Excel

Excel tables often come with built-in styles, such as colored headers, banded rows, filters, and borders. These styles can make tables easier to read, but you may sometimes want a cleaner look.

Before removing table formatting, it is important to understand the difference between table formatting and table structure:

Table formatting refers to the visual style, such as colors, borders, and banded rows.

Table structure refers to the functional features, such as filters, structured references, and automatic expansion.

Option 1: Remove the table style and keep the table

Use this option if you want to keep the table features but remove the visual style.

1. Click anywhere inside the table.
2. Go to the Table Design tab.
3. In the Table Styles group, open the style gallery.
4. Select None.

This removes the table’s visual style while keeping features such as filters and automatic expansion.

Option 2: Convert the table to a range

Use this option if you no longer need the table features.

1. Click anywhere inside the table.
2. Go to the Table Design tab.
3. Click Convert to Range.
4. Confirm the action when prompted.

This converts the table into a normal range. If some formatting remains after conversion, use Home → Clear → Clear Formats to remove it. 

How to Clear Formatting While Keeping Number Formats

The Clear Formats option removes all formatting from selected cells. This includes colors, fonts, borders, alignment, and number formats.

However, you may sometimes want to remove only the visual styling while keeping number formats such as dates, currencies, or percentages. For example, you may want to remove fill colors and bold fonts while keeping revenue values formatted as currency.

Excel does not have a single Clear Formats but keep number formats button, so you need to use a workaround:

1. Note which number formats you want to keep, such as currency, date, or percentage.
2. Select the cells and apply Clear Formats.
3. Reapply the needed number formats from Home → Number Format.

This takes an extra step, but it gives you more control over what gets removed and what stays. 

How to Clear Formatting Using Paste Special

You can also use Paste Special to remove formatting by copying the formatting from a blank, unformatted cell and applying it to the cells you want to clean.

This method is useful when you want to quickly replace messy formatting with a clean, plain format.

To clear formatting using Paste Special:

1. Select an empty cell that has no special formatting.

2. Press Ctrl + C to copy it.

3. Select the cell or range you want to clean.

4. Press Ctrl + Alt + V to open the Paste Special dialog box.

5. Select Formats.

6. Click OK.

Excel applies the blank cell’s formatting to the selected range, which removes the previous formatting while keeping the cell values intact.

This method can be helpful when you are cleaning data copied from another workbook, report, or external source. 

Common Issues When Clearing Formatting

Even after clearing formatting, you may notice some unexpected behavior. Here are some common issues and how to fix them.

• Formatting keeps coming back: This often happens when your cells are part of an Excel table. Tables can automatically reapply their built-in styles. To fix this, remove the table style or convert the table to a normal range.

• Conditional formatting still appears: Clear Formats may not remove conditional formatting rules. To fix this, go to Home → Conditional Formatting → Clear Rules and choose whether to clear rules from selected cells or the entire sheet.

• Formatting is copied from another sheet: When you paste data from another workbook or worksheet, the formatting may come with it. To avoid this, use Paste Special → Values when you only want to paste the data.

• Number formats change unexpectedly: After clearing formats, dates, currencies, and percentages may return to the General format. For example, a date may appear as a serial number. To fix this, reapply the correct number format from Home → Number Format. 

Best Practices for Managing Formatting in Excel

Knowing how to clear formatting is useful, but it is even better to avoid unnecessary formatting problems in the first place. Here are a few habits that can help:

• Use consistent styles: Apply the same formatting rules across similar headers, totals, and data fields.
• Avoid over-formatting: Too many colors, borders, and font styles can make a spreadsheet harder to read.
• Use tables carefully: Excel tables are helpful, but they can apply automatic styles. Remove the table style or convert the table to a range when you no longer need the table features.
• Use Paste Special when copying data: Paste values when you want to bring in data without carrying over unwanted formatting.
• Clean up before sharing: Before sending a workbook to others, review the formatting so the file is easier to read and work with.

Good formatting should make your spreadsheet clearer, not harder to understand. 

Conclusion

The best clear formatting method depends on what you want to remove. Clear Formats is useful when you want to remove visual styling while keeping your data intact, while Clear All should only be used when you want to remove both formatting and content.

You have also seen that conditional formatting, table formatting, and number formats may require different approaches.

As you continue building your Excel skills, you may also find our guides on How to Clean Data in Excel and Conditional Formatting in Excel helpful. These articels build naturally on what you learned here and can help you create cleaner, more reliable spreadsheets.