Excel: “Too Many Different Cell Formats” Error

You apply a fill colour, paste a block from another workbook, or just hit Save, and Excel answers with one line:

Too many different cell formats.

Then it refuses the operation. Your data is still there and the file is not corrupt — the workbook has hit a hard ceiling on how many distinct format combinations it will carry, and it will keep refusing until that list shrinks.

This is housekeeping, not corruption. It is fixable without touching a single number, as long as the cleanup happens in the right order.

What the message actually means

Every formatting combination — font, size, weight, fill, border set, number format, alignment — is stored once as a record in a table inside the file, in the cellXfs list of the xl/styles.xml part. Each cell stores only an index pointing at one record, so a workbook with clean formatting has a very short table.

Excel never garbage-collects that table. When a cell stops using a record, the record stays. Apply a slightly different grey in 2019 and another in 2021, and both live there forever. Excel’s documented ceiling is 64,000 unique combinations per workbook, and a file edited by a dozen people over several years can reach it. The wording itself is old, from builds where the limit was far lower; some current builds fail the operation with no message at all.

Why the style table fills up

Four patterns account for most cases:

• Pasting cell by cell from other files or a browser. Each pasted cell can carry its own variation of the same look: one is bold at 11pt, the next is bold at 11pt with a slightly different fill.
• Formatting individual cells instead of columns. Formatting a whole column creates one record; touching 300 cells inside it one by one can create 300.
• Conditional formatting duplicated per range. Every rule with a unique mix of fill, font colour and border adds to the same table.
• Sheets copied in from other workbooks. Copying a sheet merges the source file’s entire style table into yours, including records nothing on that sheet uses.

Fix 1 — Clear formats, then rebuild the look

The fastest route, on a copy of the file:

1. Save a copy under a new name. Work on the copy.
2. Press Ctrl+A twice — the first press selects the used range, the second selects the whole sheet.
3. Home → Clear → Clear Formats.

Every cell drops back to the default style and the table collapses to almost nothing. Values, formulas and comments survive; number formats, merges, fills, borders and cell-level rules do not.

Then rebuild the appearance deliberately: format at column level, pick a small palette of four fills and two fonts, and use Cell Styles instead of hand-formatting. Reapply date and currency formats by selecting the column and pressing Ctrl+1 → Number.

Fix 2 — Move the data into a fresh workbook

More reliable when the file has many sheets and the formatting matters less than the content:

1. Press Ctrl+End to find the real bottom-right corner of the data, then select from A1 to there.
2. Ctrl+C, open a new workbook, and use Paste Special → Values.

Paste Values is the point — a normal paste drags the source formats along and defeats the exercise. The new workbook starts with an almost empty table, so there is room to format it properly afterwards.

Two caveats. Formulas become results, so keep the original if you need the logic. And dates pasted as values can arrive as serial numbers; reformat those columns with Ctrl+1 → Date.

Fix 3 — Strip duplicate conditional formatting

Conditional formatting hides below the surface, so clear it out before concluding the file is beyond saving. Open Home → Conditional Formatting → Manage Rules, set Show formatting rules for: This Worksheet, and walk every sheet. Delete rules that apply to a handful of cells rather than a whole column, rules that repeat the same logic on adjacent ranges, and rules pointing at ranges that no longer hold data.

Where several rules do the same job, keep one and widen its Applies to range. Check data bars and icon sets on small ranges too.

Fix 4 — Delete unused named styles

Named styles are a smaller part of the same table, but you can clear them without touching cells. Save as .xlsm, press Alt+F11, insert a module, and run:

Sub PruneStyles()
  Dim s As Style
  For Each s In ActiveWorkbook.Styles
    If Not s.BuiltIn Then
      On Error Resume Next
      s.Delete
      On Error GoTo 0
    End If
  Next s
End Sub

Any cell using a deleted style falls back to Normal, so expect some formatting to shift. If the error persists, the problem is the per-cell cellXfs records, not the named styles — go back to Fix 1 or Fix 2.

When it is not worth fighting

If the workbook is five years old, has thirty sheets, and every one was formatted by hand, clearing formats means rebuilding a report your colleagues recognise on sight. That is a legitimate reason to stop.

The options then are two: rebuild the layout on a clean template and move data across sheet by sheet, or have the style table pruned inside the package by remapping cell indices in xl/styles.xml. The second keeps the original look, but it is surgery — one wrong index and cells change colour or lose their number format, so it wants a copy and a diff against the original.

Be clear about what a rebuilt workbook keeps: values yes, formulas if copied as formulas rather than pasted as values, charts and pivots no.

If the file also throws a repair prompt on open, that is a different failure and belongs in Excel files that will not open rather than here.

FAQ

Will clearing formats delete my data?

No. Values, formulas and comments stay; presentation resets. The one thing that catches people out is dates — a date cell cleared of its number format shows as a five-digit serial number until you reformat the column.

The error came back a week after I fixed it.

Because the workflow that created the formats is still running. Pasting from emails, browser tables and other workbooks rebuilds the table quickly. Switch to Paste Special → Values followed by a cell style, and the workbook stays small.

Does Save As or Open and Repair fix this?

No. Both handle structural damage and validation failures — the kind of thing behind “We found a problem with some content” — and neither trims a bloated style table. The only lever is reducing the number of distinct combinations.

If you have run Fix 1 and Fix 2 and the workbook still refuses to save, send it over with the exact wording of the message and which action triggered it — that tells us whether it is the style ceiling or something else wearing the same error text.