Fixes / Excel · Checked

Excel "Too many different cell formats": causes and fixes

Short answer

Excel shows "Too many different cell formats" when a workbook has more unique formatting combinations than it can store: about 64,000 in .xlsx files and 4,000 in old .xls files. It usually comes from styles piling up after copying between workbooks. The quickest fix is to delete unused custom cell styles, then run Inquire > Clean Excess Cell Formatting in Excel for Windows.

What this message means

Excel stores every unique mix of font, size, bold or italic, border, fill, number format, alignment and cell protection as one "formatting combination". Cells that look identical share a combination. Any small difference creates a new one. A workbook can hold about 64,000 combinations in .xlsx format (Microsoft's limits page lists 65,490 unique cell formats and styles) and only 4,000 in the older .xls format. Past that limit, Excel refuses to apply new formatting, can fail to paste, and may drop formatting or report "unreadable content" when the file opens. The data itself is not lost. The problem is the formatting information stored in the file.

Common causes

CauseHow to tell
Custom cell styles copied in from other workbooksOpen Home > Cell Styles. If the Custom section at the top has dozens or thousands of entries (often duplicate names like "Normal 2", "Percent 3" or "Style 1 45"), this is the cause. The file also tends to grow each time you paste sheets from other files.
Formatting applied far beyond the dataPress Ctrl+End. If Excel jumps to a cell far below or to the right of your last real value (for example row 1,048,576 or column XFD), whole rows or columns were formatted and are adding combinations.
Many cells formatted slightly differentlyThe sheet uses many fonts, sizes, fill colours or mixed border patterns, often from imported reports or cells formatted one at a time. Clicking around shows small differences in font or border settings between cells that look alike.
Workbook saved in the old .xls formatThe title bar shows "[Compatibility Mode]" or the file name ends in .xls. In this format the limit is only 4,000 combinations, so a fairly normal workbook can hit it.
Excess custom number formatsIn Format Cells (Ctrl+1) > Number > Custom, the list keeps going well past the built-in formats with many near-duplicates. Excel allows only about 200 to 250 number formats per workbook, depending on language version.

How to fix it

1. Save a backup, then delete unused custom cell styles

  1. Save a copy of the workbook (File > Save As) so you can go back if needed.
  2. Go to Home > Cell Styles and look at the Custom section at the top of the gallery.
  3. Right-click each custom style you don't need and choose Delete. Cells using a deleted style go back to the Normal style.
  4. Save, close and reopen the workbook, then try your formatting again. Microsoft recommends reopening before applying new formatting.

2. Remove all custom styles at once with a macro

Excel for Windows and Mac (desktop). On Mac, open the editor from Developer > Visual Basic.

  1. Make a backup copy of the file first. This removes every non-built-in style.
  2. Press Alt+F11 (Windows) to open the Visual Basic Editor, then choose Insert > Module.
  3. Paste this code: Sub StyleKill() / Dim styT As Style / On Error Resume Next / For Each styT In ActiveWorkbook.Styles / If Not styT.BuiltIn Then styT.Delete / Next styT / MsgBox "Custom styles have been removed" / End Sub (put each part separated by / on its own line).
  4. Click back into the workbook, then press Alt+F8, select StyleKill and click Run. It can take several minutes on files with thousands of styles.
  5. Save as .xlsx (the macro isn't needed after it runs), then close and reopen the file.

3. Run Clean Excess Cell Formatting (Inquire add-in)

Excel for Windows with Microsoft 365 Apps for enterprise or Office Professional Plus editions. Not available in Excel for Mac or Excel for the web.

  1. Go to File > Options > Add-ins.
  2. In the Manage box choose COM Add-ins and click Go.
  3. Tick Inquire and click OK. An Inquire tab appears on the ribbon.
  4. On the Inquire tab, click Clean Excess Cell Formatting and choose All Sheets.
  5. Click Yes to save the changes. Note that conditional formatting applied to whole rows or columns may be trimmed to the data range.

4. Simplify formatting on the sheets

  1. Select formatted ranges that don't need special formatting (or press Ctrl+A for a whole sheet), then go to Home > Clear > Clear Formats.
  2. Use one standard font and size across the workbook instead of many variations.
  3. Apply borders consistently. Adjacent cells share borders, so a right border on one cell doesn't also need a left border on the next.
  4. Remove fill patterns you don't need: open Format Cells (Ctrl+1) > Fill and choose No Color.
  5. Reapply formatting using built-in cell styles instead of formatting cells one by one, then save, close and reopen.

5. Convert an .xls file to .xlsx

  1. Open the file and choose File > Save As.
  2. Pick "Excel Workbook (*.xlsx)" as the file type (or .xlsm if it contains macros) and save.
  3. Close and reopen the new file. The limit rises from 4,000 to about 64,000 combinations.

6. Move the data into a fresh workbook

  1. Create a new blank workbook.
  2. In it, go to Data > Get Data > From File > From Workbook and pick the damaged file, or copy the cells and use Paste Special > Values.
  3. Load the sheets you need. Styles from the original file are not carried over.
  4. Reapply the formatting you need using cell styles, and rebuild any formulas or charts that were not transferred.

Next time, skip the search

Crade is an AI assistant for Mac and Windows that sees your screen. When a message like this pops up, ask “what does this mean?” and it explains the exact error in front of you, step by step.

Download Crade

Still not fixed?

If the message comes back after cleaning, check whether you keep pasting from the same source workbook, since that brings its styles back in. Paste as values or with Paste Special > Formulas to avoid it. Make sure Excel is fully updated (File > Account > Update Options > Update Now), because Microsoft fixed built-in styles being duplicated on copy in an update. If the file also shows "Excel found unreadable content", try File > Open, select the file, and use the arrow next to Open > Open and Repair. For shared or business-critical files, your IT admin can run the Inquire tools or restore an earlier version from OneDrive or SharePoint version history.

Questions people ask

What is the maximum number of cell formats in Excel?

About 64,000 unique formatting combinations per workbook in .xlsx format (Microsoft's limits page gives 65,490) and 4,000 in the older .xls format. Number formats have a separate limit of about 200 to 250 per workbook.

Why does this error happen after copying sheets between workbooks?

When you copy cells or sheets between workbooks, Excel also copies their custom styles. Repeated copying piles up duplicate styles until the workbook reaches the limit.

Will fixing this error delete my data?

No. Deleting styles, clearing formats and cleaning excess formatting only remove formatting, not values or formulas. Some cells may lose their look, so keep a backup copy.

Can I fix it in Excel for Mac?

Yes, but the Inquire add-in is Windows only. On Mac, delete custom styles from Home > Cell Styles, run the style-removal macro from Developer > Visual Basic, or clear formats with Home > Clear > Clear Formats.

Does saving as .xlsb fix it?

No. The binary format makes files smaller and faster but has the same 64,000 combination limit. It won't help if the workbook really has too many formats.

Sources

Related errors