How to Clear Formatting in Excel and Keep Your Data

To clear formatting in Excel without losing your data, select the cells and choose Home > Clear > Clear Formats. The values and formulas stay exactly as they are; only the look is reset. This guide shows the steps and shortcut, what Clear Formats does and doesn't remove, and how to fix the dates and percentages that change when their format goes.

How to clear formatting in Excel#

Clear Formats is on the Home tab, under the eraser icon. It works on one cell, a range, whole columns or the entire sheet:

1. Select the cells. To select the whole sheet, press Ctrl+A (Mac: ⌘A) or click the box where the row and column headers meet.
2. On the Home tab, in the Editing group, click Clear (the eraser).
3. Choose Clear Formats.

On Windows, the keyboard route is Alt, then H, E, F, pressed one after another rather than held together. Excel for Mac has no built-in shortcut for this command, so use the Clear button on the Home tab.

Clear Formats is all or nothing. To remove just one kind of formatting, use its own button instead: Fill Color > No Fill for a background colour, Borders > No Border for lines, or the Bold button again to turn bold off. That keeps the rest of the formatting, such as a date or currency format.

If you clear too much, press Ctrl+Z (Mac: ⌘Z) straight away. The guide to editing cells, undo and redo covers undo in more detail.

What Clear Formats removes and what it keeps#

Clear Formats resets the cell to Excel's default Normal style. Everything about how the cell looks goes; everything about what it contains stays.

The row in that table that catches people out is number formats. A number format changes how a value looks, not the value itself. Once it's gone, the raw value shows.

Clear All vs Clear Formats vs Clear Contents#

The Clear menu has several options, and the Delete key does something different again:

The Delete key leaves the formatting behind. That's why a cell you "emptied" can still turn new entries into dates or show them in red: the old format is still there. Use Clear All when you want a truly empty cell.

Fix numbers and dates after clearing formats#

Clearing formats often makes data look broken when it isn't. Dates are stored as serial numbers (whole numbers counting days), so a cleared date such as 9 Dec 2025 shows as 46000. A 15% discount shows as 0.15, and $1,250.50 shows as 1250.5. The values are still correct.

To put a format back, select the cells and pick it from the Number Format box on the Home tab, such as Short Date, Currency or Percentage. Or press Ctrl+1 (Mac: ⌘1) to open Format Cells for more choices. On Windows, Ctrl+Shift+# applies a date format and Ctrl+Shift+% a percent format. The guide to formatting numbers as currency and percent covers every option.

If you need the date as readable text in another cell, for a label or an email, the TEXT function does it:

=TEXT(A2,"d mmm yyyy")

With 46000 in A2, this returns the text 9 Dec 2025. Use TEXT for display only: the result is text, so you can't do date maths with it.

Clear conditional formatting and table styles#

Two kinds of formatting have their own clear commands, because they aren't stored like ordinary formats.

Conditional formatting colours cells based on rules. To remove the rules, select the cells and go to Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells, or Clear Rules from Entire Sheet. Learn how the rules work in the conditional formatting guide.

Table styles come from an Excel Table (made with Ctrl+T). Click inside the table, open the Table Design tab (called Table on a Mac), and choose Clear at the bottom of the Table Styles gallery. The table keeps its filters and structured references but loses the banded colours. To turn it back into a plain range, choose Convert to Range; the old colours remain as normal formatting, which you can then clear with Clear Formats.

Other ways to reset formatting#

Clear Formats is the fastest way to strip everything, but sometimes you only want to match or avoid formatting:

• Format Painter from a plain cell. Click a cell with the look you want, click Format Painter, then click the cells to fix. See how to use Format Painter.
• Cell Styles > Normal. On the Home tab, open Cell Styles and choose Normal. This resets the font, fill, borders and number format to the workbook default, much like Clear Formats.
• Paste as values. When you copy from a website or another workbook, paste with Paste Special > Values so the source formatting never arrives. The guide to Paste Special values shows the shortcuts.

Practise it#

Clearing, undoing and reformatting cells are everyday skills that basic Excel tests check. Practise editing, clearing and undo in a live spreadsheet: you work in a real grid and get instant feedback. Then practise date, currency and percent formats in the more formatting lesson, or try the exercise on this page first.