Wednesday, April 20, 2011

Clear Excel styles and solve the "Too Many Different Cell Formats" error

It may have happened to you.

You are working with Excel for hours and then, maybe after a big paste, maybe after inserting a row, you get the error "Too many different cell formats". Sometimes Excel crashes, sometimes you just can't format the spreadsheet the way you want.


There's a simple tool that clears the unused styles from your file, at XL Geek. I was working on a 5MB file that was crashing rather frequently. I know enough to clean a lot of common things and ended up with 500Kb and a bunch of corrupted styles (see picture above - the 386 grabber is a common one). I used the tool and got to 17Kb. It cleaned more than 6000 unused styles.

It's simple to use and it uses .NET 4.0. If you use Excel frequently and occasionally see the "386 grabber" style, give it a try.

1 comment: said...

Do you mind sharing what else you cleaned out? Named ranges? Memory over allocations? Typical size drop for 40,000 styles is ~250kb. You must've had something else going on since you dropped by 400kb+ with only 6,000 bad styles. If you comment on my blog you will save me the trouble of re-typing. Really appreciate your post and positive feedback.