How to Clear Formatting in Excel with Simple Steps

When working with large Excel worksheets, its a common practice to apply different formatting options to make data relevant to a particular situation stand out. In other situations, however, you may want to highlight other data, and for this, you will need to remove the current format first.

Manually changing cell color, font, borders, alignment and other formats would be tiresome and time-consuming. Luckily, Microsoft Excel provides a couple of quick and simple ways to clear formatting in a worksheet, and I will show you all these techniques in a moment.

Working in Excel sheets often involves applying different formatting like fonts colors, borders etc. to cells and ranges. But sometimes you may want to remove this formatting and clear cells back to the default styles.

Clearing formatting in Excel is quick and easy. In this guide, you’ll learn multiple methods to remove font, color, border, and other cell formatting in Excel for Windows and Mac.

Why Clear Formatting in Excel?

Here are some common reasons you may need to remove formatting in Excel

Undo manual formatting applied to cells and ranges
Clear formatting after pasting copied cells
Import data without bringing in styles
Reset worksheet to default format
Fix unexpected formatting issues
Standardize styles for consistent appearance
Remove formatting before analysis or visualization

Without the ability to clear formatting, Excel sheets would quickly become an inconsistent mess of different fonts, colors, alignments, and borders. Let’s look at the key ways to avoid this.

Clear All Formatting

The fastest way to remove all cell formatting at once is using the Clear Formatting command:

Select the cells or range to clear.
On the Home tab, click the Clear button and choose Clear Formats.

This removes fonts, colors, borders, number formats – anything applied to the selected cells. They reset to the default style.

Or use the keyboard shortcut Ctrl + Shift + Space to clear formatting.

The only exception is conditional formatting rules, which this method does not remove. You’ll learn how to clear those separately next.

Clear Specific Formatting

To remove only certain types of formatting, you can use the Clear command on the Home tab:

Clear Formats – clears all formatting
Clear Fonts – resets just fonts
Clear Borders – removes cell borders
Clear Hyperlinks – deletes hyperlinks
Remove Hyperlink – clears link formatting but keeps URL text

Simply select the cells, click the Clear button, and pick what formatting to remove.

For example, to remove just fonts, run Clear Fonts. Or to delete hyperlinks but keep the text, choose Remove Hyperlink.

This lets you selectively clear only certain formatting, instead of everything at once.

Clear Column Width and Row Height

Adjusted column widths and row heights are a type of cell formatting in Excel.

To revert columns or rows back to the default size, right-click and choose:

Clear Width to reset a column’s width
Clear Height to reset a row’s height

This will undo any manual sizing of rows and columns.

Clear Conditional Formatting Rules

To remove conditional formatting rules from cells:

Select the cells with conditional formatting applied.
On the Home tab, click the Conditional Formatting button.
Choose Clear Rules, then select Entire Sheet or This Range to clear rules from the whole sheet or just the selected cells.

You can also right-click the selected cells and choose Clear Rules from the pop-up menu.

This will delete all conditional formatting rules, leaving just the underlying values.

Remove Formatting When Pasting Cells

A handy trick when copying and pasting cells in Excel is to use the Paste Special option.

This lets you paste values or formulas only, without bringing over any formatting.

To paste without formatting:

Copy the cells with Ctrl + C.
Select where to paste the cells.
Right-click and choose Paste Special.
Pick Values or Formulas in the Paste Special box.

Thispastes just the contents and numbers into the cells, ignoring any colors, fonts, borders, etc.

Clear Formats with “Go To Special”

Here is one more clever way to remove formatting in Excel using the Go To Special dialog box:

Press Ctrl + G to open the Go To dialog.
Click Special.
Select Constants under Select: and uncheck Numbers in the dialog.
This will select all non-numeric cells on the sheet.
Press Delete to clear their formatting.

It acts like a filter to select only cells with formatting applied. Deleting these wipes the formatting while keeping the raw values.

VBA Macro to Clear Formatting

You can also automate clearing formatting by using a VBA macro in Excel.

Here is a simple macro to clear formats from the selected cells when run:

Sub ClearFormats()    Selection.ClearFormatsEnd Sub

Press Alt + F11 to open the Visual Basic editor and paste the code into a module.

Then run the macro by pressing F5 or clicking the Run button.

This provides yet another option for programmatically removing formatting.

Top Tips for Clearing Formats

Here are some useful tips for effectively clearing Excel formatting:

Remove formatting before analyzing data or creating charts for clean results.
Name ranges before clearing formats to retain names.
Double check ifnumber formats were cleared and reapply as needed.
Use Ctrl + Z to undo clearing if you change your mind.
Save a copy before clearing formatting on important workbooks.
If only clearing cell contents, use Delete instead to leave formatting.

Mastering how to clear formatting in Excel gives you more control and flexibility when working with sheets. Use the above skills to keep your workbooks clean and consistent.

Quick Recap

To clear formatting in Excel, you can use:

Clear Formatting button – Clears all fonts, colors, borders, etc.
Clear commands – Selectively remove font, border, hyperlink formatting
Clear Width/Height – Reset column widths and row heights
Conditional Formatting Clear Rules – Delete conditional formats
Paste Special – Paste values/formulas without formatting
Go To Special – Select and clear just formatted cells
VBA macro – Automate clearing formats

No matter the situation, you now have the knowledge to strip unwanted formatting from cells and ranges in Excel.

For more help, take a look at the advanced tutorial on clearing all formatting in Excel. And be sure to learn how to apply formatting in Excel as well using fonts, colors, styles, and much more.

With the ability to add and remove formatting, you can make your Excel workbooks look exactly how you want. Clean up those sheets and breathe new life into your stale Excel files!

how to clear formatting in excel

How to make the Clear Formats option accessible in a click

If you want to have a one-click tool to remove formatting in Excel, you can add the Clear Formats option to the Quick Access toolbar or Excel ribbon. This might be especially useful if you receive many Excel files from your colleagues or clients and their formatting prevents you from making the data look the way you want.

If Clear Formats is one of the most used features in your Excel, you can have it added to the Quick Access Toolbar in the top-left corner of your Excel window: The Clear Formats option added to the Quick Access toolbar

To do this, perform the following steps:

In your Excel worksheet, click File > Options, and then select Quick Access Toolbar on the left-side pane.
Under Choose commands from, select All Commands.
In the list of commands, scroll down to Clear Formats, select it and click the Add button to move it to the right-hand section.
Click OK.

If youd rather not clutter your Quick Access toolbar with too many buttons, you can create a custom group on the Excel ribbon and place the Clear Formats button there.

To add the Clear Formats button to the Excel ribbon, follow these steps:

Right-click anywhere on the ribbon, and select Customize the Ribbonâ¦
Because new commands can only be added to custom groups, click the New Group button:
With the New Group selected, click the Rename button, type the name you want, and click OK.
Under Choose commands from, select All Commands.
In the list of commands, scroll down to Clear Formats, and select it.
Select the newly created group and click Add.
Finally, click OK to close the Excel Options dialog and apply the changes youve just made.

And now, with the new button in place, you can remove formatting in Excel in a single click!

How to clear all formatting in Excel

The most obvious way to make a piece of information more noticeable is to change the way it looks. Excessive or improper formatting, however, may have an opposite effect, which makes your Excel worksheet difficult to read. The easiest way to fix this is to remove all current formatting and start embellishing the worksheet from scratch.

To remove all formatting in Excel, just do the following:

Select the cell or range of cells from which you want to clear formatting.
On the Home tab, in the Editing group, click the arrow next to the Clear button.
Select the Clear Formats option.

This will erase all cell formatting (including conditional formatting, number formats, fonts, colors, borders, etc.) but keep the cell contents.

With this Excel Clear Formatting feature, you can easily remove formats not only from a single cell, but also from an entire row, column or worksheet.

To clear out formatting from all cells on a worksheet, select the entire sheet by pressing Ctrl+A or by clicking the Select All button at the top-left corner of the worksheet, and then click Clear Formats.
To remove formatting from a whole column or row, click the column or row heading to select it.
To clear formats in non-adjacent cells or ranges, select the first cell or range, press and hold the CTRL key while selecting other cells or ranges.

Fastest Way to Remove All Formatting in Excel – Excel Quickie 57

What is the easiest way to clear formatting in Excel?

The easiest way is to simply select the cells that you want to clear, and then press the “Clear Formatting” button on the Home tab of the Excel ribbon. You can also use the keyboard shortcut “Ctrl+Shift+F” to clear formatting. If you want to clear formatting from the entire worksheet, you can press the “Clear All” button on the Home tab.

What happens when you use the Clear Formatting command in Excel?

Select the Clear Formats option. This will erase all cell formatting (including conditional formatting, number formats, fonts, colors, borders, etc.) but keep the cell contents. With this Excel Clear Formatting feature, you can easily remove formats not only from a single cell, but also from an entire row, column or worksheet.

What is the best way to fix cells that are not correctly formatted in Excel?

Press the sequence Alt, H, E, and F on your keyboard to clear the format in the selected range of your Excel sheet. Any unused cells in your workbook will have the default format applied and you can copy and paste this as a way to clear formatting in other cells.

Are there any other methods for removing formatting from a table in Excel?

There are a few different ways that you can clear formatting in Excel. The easiest way is to simply select the cells that you want to clear, and then press the “Clear Formatting” button on the Home tab of the Excel ribbon. You can also use the keyboard shortcut “Ctrl+Shift+F” to clear formatting.