How to Remove All Formatting in Excel

Q: Will it remove Conditional Formatting rules?

ClearFormats removes the rules' application to the cleared cells. The rule definitions still exist in the workbook's CF Rule Manager. To remove the rule definitions entirely, Conditional Formatting > Manage Rules > Delete.

Q: Can I undo it?

Yes, Ctrl/Cmd + Z right after running.

The "this workbook is a visual mess" problem

You inherited a workbook where someone went wild with cell colors, borders, font sizes, and number formats. The data is fine; the visual chaos is the problem. You want a clean canvas to apply your own consistent format.

TL;DR — Key takeaways

The built-in Home > Clear > Clear Formats strips formatting from a selection.
Paste Special > Values copies values without formatting if you're moving data.
A VBA macro wraps Clear Formats with a count and works the same on any selection size.

Method 1: Home > Clear > Clear Formats

The built-in option.

Select the range.
Home > Editing > Clear > Clear Formats.

The icon is in the eraser dropdown (the eraser with the red X).

The catch: for a multi-tab cleanup, you click through this menu once per tab. About 30 seconds per tab navigating the nested menus.

Method 2: Paste Special > Values

If you're moving data to a fresh location:

Select source. Ctrl+C.
Click destination.
Right-click > Paste Special > Values.

The destination has values without formatting. Useful when you're consolidating from multiple sources into one clean sheet.

The catch: doesn't strip formatting in place. Only works for the move-to-fresh-location workflow.

Method 3: The free VBA macro

Download Remove All Formatting. Free .xlsm with one macro.

Select the range. Ctrl+A (twice) for entire used range of a tab.
Alt + F8, pick the macro, click Run.
Strips colors, borders, fonts, number formats. Reports cell count.

Faster than the menu navigation. Especially useful across multiple tabs.

What gets stripped

Everything that Range.ClearFormats removes:

Cell fill color
Font color, face, size
Bold, italic, underline
Borders (top, bottom, left, right, diagonal)
Number format (cells go back to General)
Conditional Formatting application to those specific cells

What stays

Cell values
Formulas
Comments
Hyperlinks
Data validation rules
Merged cell state

A common scenario: inherited talent review workbook

HR analyst inherits a talent review workbook where the predecessor color-coded everyone (top performers green, high potential blue, etc.). The new analyst wants a clean canvas.

On each tab, Ctrl+A twice to select used range.
Run Remove All Formatting.
Reapply consistent formatting.

For 8 tabs, the macro saves about 3 minutes versus menu navigation, but the bigger win is the brain space saved.

Frequently asked questions

Will it remove Conditional Formatting rules?

ClearFormats removes the rules' application to the cleared cells. The rule definitions still exist in the workbook's CF Rule Manager. To remove the rule definitions entirely, Conditional Formatting > Manage Rules > Delete.

What about merged cells?

Merge state is preserved. The macro strips formatting but doesn't unmerge. To unmerge, Unmerge Cells and Fill Down.

Will it strip formatting from cells with formulas?

Yes. Formula cells get formatting stripped along with everything else. The formula itself is preserved.

What about column widths and row heights?

Column widths and row heights are properties of the column/row, not the cell. ClearFormats doesn't touch them. To reset column widths, double-click the column boundary or use AutoFit All Columns on All Sheets.

Can I undo it?

Yes, Ctrl/Cmd + Z right after running.

What to do next

After stripping formatting, common next steps: bold the header row, apply Freeze Panes with Freeze Top Row on All Sheets, AutoFit columns with AutoFit All Columns on All Sheets.