Too Many Cell Formats

unused often corrupted styles

1) user-defined styles (Format > Styles)
2) internally generated styles

Excel 2003 has a maximum of 4,000 cell styles in a workbook
Excel 2007 has this increased to 64,000
A style is automatically created for anything that has a different cell format.
A style is defined as a unique set of formatting elements that are applied to a cell.

Different types of formatting that contribute to a unique cell style
1) Fonts (inc size, bold, underline, italic, strikethrough etc)
2) Borders (position, location, thickness, weight, color etc)
3) Fills (color, pattern)
4) Number formatting (inc custom number formats)
5) Alignment (top, left, centered across selection, indented, merged cells)
6) Cell Protection

If more than one cell contains the same format, then this is only counted once.
If there is any difference in formatting between two cells then each cell will contain a different style.


Simplify the cell formatting, here are some suggestions
1) Only use one type of font and font size
2) Keep borders to a minimum
3) Remove any unnecessary formatting (shading, patterns etc)
4) Make sure your number formatting is consistent (same number of decimal places, currency etc)
5) Clear the formatting of blank cells. To select all blank cells use Find & Select, GoTo Special. To clear the formats use the Clear drop-down on the Home tab, Editing group.
6) Remove any AutoFormats (select range, Format > AutoFormat) scroll to the end and choose None

Can be better to format the whole workbook - instead of one cell at a time ????

2007 Bug

When you move or copy a worksheet from one workbook to another, unused styles are copied unexpectedly.
After the hotfix has been installed add the following registry key:
HKCU\Software\Microsoft\Office\12.0\Excel\Options\ DisableCopyUnusedCustomStyles - DWORD (32 bit) - Value = 1

2010 Bug

HKCU\Software\Microsoft\Office\14.0\Excel\Options\ DisableCopyUnusedCustomStyles - DWORD (32 bit) - Value = 1

SS cells - 4 by 4 - with 16 different styles

© 2020 Better Solutions Limited. All Rights Reserved. © 2020 Better Solutions Limited TopPrevNext