Limitations on the number of formats used in a workbook

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a very large workbook I'ver developed for doing system design then
generating a budgetary quote and, if accepted the BOM for executing the
project. All has been fine until recently when it began telling me I had too
many formats. The MS knowledge base states this occurs if one exceeds 4,000
formats in a workbook but can be easily resolved by merely reducing the
number of discrete formats however, every time I attempt to change some
exisiting formats as suggested the error is announced and I am prevented from
changing anything. Two questions arise ... First, is there any way to get
past this "lockout" (I have not found one) and two, if I am reduced producing
a new workbook using fewer discrete formats is there any way I can monitor
the number of formats I have going so I can better understand how Excel
counts formats in order to stay clear of this problem?
 
Excel counts a 'format' as any change to the format, size, font, colour,
bold, italic, fill colour, borders, etc. If you always use four borders,
times new roman, size 10, italic. This is one format, but change a cell to
bold for emphasis and you now have two.

There are a few ways to possibly get out of this lock-out. (Save a copy of
the workbook before)

1) Copy a sheet from the workbook to a new file and reformat before moving
back. That may give you some breathing space
2) Try saving the file as HTML and then back as an .xls (Known as
round-tripping). This may remove some that Excel thinks it still has but
doesn't. Again you may get more space

In future consider setting up Styles under Format>Styles and apply these
based on your criteria. That way you are sure that all 'xxxxxxxx' are
formatted absolutely identically and count as one format

FYI: XL2007 has increased this limit (4000) to 64000

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)
 
Back
Top