Remove All Formatting

What it does

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. Excel's Home > Clear > Clear Formats works on a selection but it's a 3-click navigate every time. The macro is a single-keystroke version: select, run, formatting gone. Cell values and formulas are untouched.

Real-world example

Picture an HR analyst inheriting the talent review workbook from a predecessor who color-coded everything. Top performers in green. High potential in blue. Promotion-ready in yellow. Underperformers in red. Plus borders around every "important" cell, double underlines under headers, italics on comments. The color scheme made sense to the predecessor; the new analyst needs a clean canvas to apply her own consistent format.

Her current method: select all (Ctrl+A), Home > Editing > Clear > Clear Formats. Repeat for each tab because Ctrl+A only works on the active tab. About 30 seconds per tab; with 8 tabs that's 4 minutes plus the cognitive load of the clear navigation.

With this macro she selects all on each tab, runs the tool, and 4 minutes of work becomes 30 seconds total. Each run shows a count of cells stripped. After 8 runs across 8 tabs, the workbook is a clean canvas she can reformat consistently.

On an 8-tab inherited talent workbook, the time saved per cleanup is about 3 minutes plus the brain space saved by not navigating Excel's nested menus.

How to use

First time? See How to run our tools for the 30-second setup.

1. Select the range to strip. Ctrl+A (twice) selects the entire used range of a tab.
2. Press Alt + F8 (Windows) or Option + F8 (Mac), pick RemoveAllFormatting, click Run.
3. The macro strips all formatting (colors, borders, fonts, number formats) and reports the cell count.

FAQ

What gets stripped?

Everything that Range.ClearFormats removes: cell fill color, font color, font face, font size, bold/italic/underline, borders, number formats (the cell goes back to General), conditional formatting rules referencing those cells.

What stays?

Cell values, formulas, comments, hyperlinks, data validation rules. The macro only touches formatting, not data.

Will it remove Conditional Formatting rules?

ClearFormats removes the rules' application to those specific cells. The rules themselves still exist (in the workbook's CF Rule Manager) but no longer affect the cleared cells. To remove the rules entirely, use Conditional Formatting > Manage Rules > Delete.

What about merged cells?

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

Can I undo it?

Yes, Ctrl/Cmd + Z right after running. As always, work on a copy of the file if it's the only copy.

What to do next

After stripping formatting, you'll typically want to re-apply consistent formatting. Standard moves: bold the header row, apply a number format to numeric columns, autofit columns with AutoFit All Columns on All Sheets, apply Freeze Panes with Freeze Top Row on All Sheets.