I wrote an earlier article Excel Problem Too Many Cell Formats Error about a problem that I’ve had with Excel 2007. In this problem, I can not copy and paste within a spreadsheet.
The problem is somewhat repeatable — at least, it hits me multiple times every year, ever since it started occurring.
It happens like this:
- I start Excel 2007,
- I open my problem spreadsheet in Excel 2007. This is a monster with over 22,000 rows of data, over 100 columns, and runs between 20MB and 40MB in size, depending on how much "undo" information is stored in the spreadsheet file.
- In order to keep control over the size and usability, I often open a separate instance of Excel and create a new spreadsheet in it for side calculations and massaging (sorting and more) of data that I’ve copied and pasted "as text" into the work spreadsheet.
- Note that this is not the same as stretching the Excel window and displaying 2 spreadsheets in the same window. There are changes in the way Excel handles crashes and changes in the way Excel handles copy-and-paste functions.
- With two instances, you don’t have "Paste as Values". With Windows XP and Excel 2007, the Paste Special options available are Paste as HTML and Paste as Text. With Windows 7 and Excel 2007, there are more Paste Special options, but still not a Paste as Values option.
- Once I get the data in the layout and format that I need (often, I’m creating running totals and creating subtotals, where I want to copy the subtotal lines into the main spreadsheet), I select the rows and columns that I want and paste into the main spreadsheet.
- That’s where the problem apparently occurs.
- When it happens, I get an error message that says either that Excel is unable to paste into the spreadsheet or a cryptic "Too many cell formats" error.
- At that point, I can no longer past ANYTHING into the spreadsheet. Excel 2007 has triggered a bug that makes it think there are too many unique cell formats in the spreadsheet.
In my earlier article, I wrote about the problem and the free fix that I had found on the web. It identifies and counts the cell formats and then writes the correct value into the file. That’s all it takes.
Microsoft has released several Hotfix sets of patches for Excel 2007 that are designed to prevent the problem from occurring. Whey so many? Because the problem can occur in several ways. The Hotfixes don’t fix any spreadsheets that are already messed up, but you can still fix them with the downloadable tool.
The first applicable KnowledgeBase article is KB981731, dated April 10, 2010. It provides patches that fix several things, but the one that affects me is "You copy some cells from an Office Excel 2007 workbook that has many custom formatting styles. You paste these cells into another instance of Office Excel 2007 workbook. After you copy and you paste several times, you find that you cannot paste more copied content. ." That’s exactly problem I’m seeing.
The second applicable KnowledgeBase article is KB2534047, dated April 26, 2011. This one, on the other hand, appears to be specific to the way the problem is occurring. It says:
- Consider the following scenario:
- You run two instances of Microsoft Office Excel 2007.
- You open a workbook in each instance of Excel.
- You copy cells that have built-in cell styles from one workbook to another workbook multiple times.
- In this scenario, the built-in cell styles are duplicated each time. Additionally, you may not be able to paste, or you receive an error message.
The third applicable KnowledgeBase article is KB2553085. This one requires changes to the Windows Registry. It addresses the problem "when you move or copy a worksheet from one Excel workbook to another workbook in Microsoft Office Excel 2007, unused styles are copied unexpectedly. In this case, the file size of the target workbook is larger than expected."
There is a Microsoft® Fix It tool (#50805) on the page for easy application of the fix. The KB2553085 article also links to KB2553026, for people who want to do the registry fix themselves.