How to Remove All Formatting Without Deleting Data

Quick reference: Data Cleanup troubleshooting

You inherited a sales tracker with 14 colors of conditional formatting, three font families, italic text in random rows, bold accents that nobody understands, and currency formats applied where they should not be. The data itself is fine — you just need a clean slate. Deleting the data is not an option. Manually clearing each formatting type would take an afternoon. Excel has a single command that strips all formatting while preserving values, but it’s tucked away in a sub-menu most users overlook.

Before You Start: The 60-Second Diagnostic

Before the fix: the failure mode this guide addresses

Three checks:

Confirm what to keep: Values only? Values plus column widths? Values plus row heights?
Identify hidden formatting: Conditional formatting rules, cell styles, table styles — different removal paths.
Plan the scope: Single column, whole sheet, or whole workbook?

Step-by-Step Solution

After the fix: the workbook restored to a healthy state

H2: Use Clear Formats

The simplest one-click solution:

Select the range (or press Ctrl + A for whole sheet).
Home → Editing group → Clear → Clear Formats.

This removes: font, color, fill, borders, number format, alignment. It keeps: values, formulas, comments, data validation.

H2: Distinguish the Five Clear Options

The Clear dropdown has five options:

Clear All — removes everything including values.
Clear Formats — preserves values, drops formatting.
Clear Contents — drops values, preserves formatting (same as pressing Delete).
Clear Comments and Notes — only removes comments.
Clear Hyperlinks — only removes hyperlinks; keeps display text.

For data cleanup, Clear Formats is the right choice. Avoid Clear All unless you really want to wipe the cells.

H2: Strip Conditional Formatting Separately

Clear Formats does NOT remove conditional formatting rules. To remove those:

Select the range.
Home → Conditional Formatting → Clear Rules → Clear Rules from Selected Cells.

For workbook-wide removal:
3. Home → Conditional Formatting → Clear Rules → Clear Rules from Entire Sheet (per sheet).
4. Repeat for each sheet.

To audit before clearing: Manage Rules → Show formatting rules for: This Worksheet. Note what’s there before removing.

H2: Reset to Default Cell Style

For cells where Clear Formats does not fully reset (some style inheritance survives):

Select the range.
Home → Cell Styles → Normal.

This applies the Normal cell style — Excel’s true default. Useful for resetting cells that have inherited custom styles from copy-paste operations.

H2: Strip Table Formatting

If your data is in an Excel Table with applied style:

Click any cell in the table.
Table Design tab → Convert to Range.
Confirm “Do you want to convert the table to a normal range?” → Yes.

The data preserves but the table formatting drops. To also remove residual formatting:

Select the now-converted range.
Home → Clear → Clear Formats.

H2: Strip via Paste Special → Values

For cleaning data while preserving structure:

Select the data.
Copy (Ctrl + C).
Right-click any empty cell → Paste Special → Values (or Ctrl + Alt + V → V → Enter).

The pasted values carry no formatting at all — just raw data. Then delete the original and move the cleaned copy back.

This is the cleanest approach for data extracted from heavily-formatted external sources (web pages, formatted reports).

Information Gain Box: The Hidden Cell Style Bloat

Here is what destroys workbook performance silently: every copy-paste from a heavily-formatted source can introduce new custom cell styles, and these styles persist in the workbook’s style library even when no cells use them.

After years of paste operations from various sources, a workbook can accumulate 30,000+ unused custom styles — each consuming a few KB of file size and slowing every Save operation.

To audit:

Sub CountStyles()
  Debug.Print "Style count: " & ActiveWorkbook.Styles.Count
End Sub

A typical workbook has 50-60 built-in styles. Anything above 100 indicates accumulated cruft.

To remove unused custom styles:

Sub RemoveUnusedStyles()
  Dim s As Style
  Dim i As Long
  For i = ActiveWorkbook.Styles.Count To 1 Step -1
    Set s = ActiveWorkbook.Styles(i)
    If Not s.BuiltIn Then
      On Error Resume Next
      s.Delete
      On Error GoTo 0
    End If
  Next i
End Sub

This deletes every non-builtin style. Some custom styles you defined intentionally will also be deleted — note them before running, or check s.NameLocal and skip ones with recognizable names.

For workbooks with 30,000+ custom styles, this single cleanup can reduce file size by 80-90% and dramatically speed up open/save operations. Microsoft documents the style accumulation issue briefly in Excel performance optimization but rarely surfaces it in user-facing help. Most users never know to look.

Comparison Table: Wrong Way vs. Correct Way

Goal	Wrong Way	Correct Way
Strip all formatting	Manual cell-by-cell	Home → Clear → Clear Formats
Remove conditional formatting	Hope it goes with formats	Conditional Formatting → Clear Rules
Reset to default style	Reformat manually	Apply Normal cell style
Strip Excel Table style	Try to undo	Table Design → Convert to Range, then Clear Formats
Cross-source paste cleanup	Standard paste	Paste Special → Values only
Workbook bloat	Live with slow saves	Audit and remove unused custom styles via VBA
Audit scope	Eyeball the sheet	Count via ActiveWorkbook.Styles.Count

Original Image Descriptions

Screenshot 1: Show a heavily-formatted Excel sheet with mixed colors, italic and bold text scattered throughout, multiple font families, and conditional formatting bars in some columns. The Home → Editing → Clear → Clear Formats menu is open. Draw a red circle around the Clear Formats option. Add a red annotation: “Strips all formats, preserves all data.”

Screenshot 2: Show the same sheet after Clear Formats applied — all cells in default font, no colors, no italics. Data values are preserved exactly. Beside it, the Conditional Formatting → Manage Rules dialog now shows zero rules. Draw a red circle around the clean cells. Add a red annotation: “Clean slate while data stays intact.”

Frequently Asked Questions

Q: Will Clear Formats remove my formulas?
A: No — formulas are part of the cell’s content, not its formatting. Clear Formats removes only visual properties (font, color, borders, number format). Formulas, values, comments, data validation, and hyperlinks all remain.

Q: Can I undo Clear Formats?
A: Yes — Ctrl + Z reverses Clear Formats. The undo restores every formatting attribute exactly as it was. This is reliable as long as the action is recent (within the undo history).

Q: Why does my Clear Formats not remove certain colors?
A: Those colors are likely from conditional formatting rules, which Clear Formats does NOT remove. Conditional formatting is a separate system. To remove: Home → Conditional Formatting → Clear Rules → Clear Rules from Selected Cells (or Entire Sheet).

Related guides in Data Cleanup

Browse the full Data Cleanup library or return to the byyours homepage.