Select the cells, go to Home → Editing → Clear → Clear Formats, or press Alt → H → E → F. Fills, fonts, borders, alignment, number formats and conditional formatting are removed; values and formulas stay.
To reset the whole sheet, click the Select All corner above row 1 first. To remove only conditional formatting and keep everything else, use Home → Conditional Formatting → Clear Rules.
Clear Formats resets number formats to General, so dates turn into serial numbers such as 45658 and currency loses its symbol. Reapply a date format with Ctrl + Shift + #, or clear only the cells that need it.
Formatting from an Excel Table stays after Clear Formats because it comes from the table style. Click inside the table and choose Table Design → Table Styles → Clear, or use Convert to Range first if you want plain cells.
In VBA, Selection.ClearFormats clears the selected cells and ActiveSheet.Cells.ClearFormats clears the whole sheet.
In Excel, formatting is used to highlight cells or ranges of cells to make the relevant data easily visible. It could be done by applying cell or font color, borders, alignment, font style, and other formatting.
And sometimes you need to clear the formatting from some specific cell ranges or the entire worksheet. Doing this manually is a time-consuming task, and if your data is large, you might miss some of the formatting.
But relax, Excel has multiple ways to clear the formatting quickly and easily, and some of them are described below.
Steps to Clear Formatting Using Clear Formats Option
- First, select the cells or range of cells from where you want to clear the formatting, or the entire sheet (Ctrl + A) if you want to remove formatting from the entire sheet.
- After that, go to the “Home” tab and within the Editing group, click on the Clear drop-down icon.

- Now, select the Clear Formats option from the drop-down list.

- The moment you click on Clear Formats, it will clear all types of applied formatting, including cell colors, font colors, borders, alignment, number formats, conditional formatting, etc.

Clear Formatting Using Format Painter
- First, go to any unformatted cell.
- After that, go to the “Home” tab, and in the Clipboard group, click on Format Painter.
- Now, select the cell or range of cells from where you want to clear the formatting.

- At this moment, all the applied formatting will get cleared.

Clear Formatting Using Keyboard Shortcut
Excel also has a keyboard shortcut to clear the formatting, and for this, you just need to follow the two steps below.
- First, select the cells or range of cells from where you want to clear the formatting, or the entire sheet (Ctrl + A) if you want to remove formatting from the entire sheet.
- After that, simply press Alt → H → E → F in sequence, and the formatting will get cleared.
Clear Formatting using a VBA Code
You can also use a VBA code to clear formatting. The following line clears the formatting from cell A1:
Range("A1").ClearFormats
Put this line inside a Sub in a module to run it. And to clear the formatting from the whole active sheet, use Cells instead of a range:
Sub clear_sheet_formatting()
ActiveSheet.Cells.ClearFormats
End Sub
What Clear Formats Also Removes
Clear Formats resets the cells to the default style, so it also removes the number format. That means dates turn into numbers (like 45000), percentages turn into decimals and currency symbols disappear. If you want to keep those, use one of the options below instead, or apply the number format again after clearing.
- Clear All (Alt → H → E → A): removes the formatting and the values, comments and notes too, so use it only when you want empty cells.
- Clear only conditional formatting: go to Home ➜ Conditional Formatting ➜ Clear Rules, and pick Clear Rules from Selected Cells or from Entire Sheet.
- Clear a table style: click inside the table and go to Table Design ➜ Table Styles ➜ Clear. Clear Formats doesn’t remove a table style.
Frequently Asked Questions
Can I undo Clear Formats?
Yes. Press Ctrl + Z right after you clear the formatting, and Excel brings it back.
Why did my dates turn into numbers after clearing formatting?
Clear Formats also removes the date number format, and Excel stores dates as numbers. Select the cells and apply a date format again from Home ➜ Number Format.
Does Clear Formats delete my data?
No. It only removes the formatting, and the values and formulas stay as they are. Clear All and Clear Contents are the options that remove data.
Written by
Puneet Gogia
Microsoft MVP — Excel · Founder, Excel Champs
Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.