Fonts warning message when creating charts

[Tom-S]

When creating a large number (120) of pie charts on a worksheet I
unexpectedly got a warning message about half way through saying something
like 'No new fonts may be created in this workbook', and an OK button.
(Office Student & Teacher 2003 version of Excel)

Clicking OK (at least once) allowed me to continue but the message appeared
every time I wanted to create and then edit another chart on that and
subsequent worksheets in the workbook.

I'm guessing that the message was related to the large number of charts
being created? Anyone know if that's true and, if so, how to create a large
number of charts on a single worksheet WITHOUT getting the warning.

 

[Barb Reinhardt]

This might help

http://support.microsoft.com/kb/215573

 

[Tom-S]

Thanks to Barb for the suggested solution, which I tried. Excel then allowed
me to easily create the 120 charts I wanted on a single worksheet.

However, I also wanted to copy the worksheet several times (within the same
workbook) but wasn't able to copy more than TWICE (i.e. I did get 360 charts
total, plus associated data tables). On the 4th time of trying to copy the
worksheet, the data tables copied ok but the charts did not copy at all.

Interestingly enough, I was able to CREATE at least one new chart on the 4th
worksheet, but with so many charts needed, I didn't want to have to revert to
laborious creation.

Although this didn't seem to me like a problem of exceeding a chart number
capacity (if there is such a thing - anyone know????), there did seem to be
some link to the high volume of charts as the problem source. Any help
please????

 

[Jon Peltier]

This is a limitation in the architecture of Excel. There are some things you
could do:

1. Store your data and charts in separate workbooks, one or two sheets each.

2. Reevaluate your method for displaying information. Pie charts are
generally considered inefficient ways to display information (Google for
'pie charts bad' if you want to know why). 120 charts on a worksheet can't
be a good thing either, as navigation among the charts sounds like a
nightmare. Is there any way to extract the important information from 120
pie charts into a handful of more effective charts? Or could you use INDEX
with a number in another cell to use one chart to show a row of data
interactively?

- Jon

 

[Tom-S]

Thanks for the message Jon.

I was working this task for someone who wanted the data presented in this
way, and specifically with pie charts, despite my lobbying for anything
remotely more efficient (like stacked bar charts).

Having found the problem, however, & having tried the partial solution
suggested by Barb, I already decided to split the data/charts between
separate workbooks and have continued in that vein.

For the record, what is the actual architecture limit? Is this a specific
max number of charts per workbook - or does it depend on how much data each
chart is linked to? Does anyone have specific numbers on this???

 

[Jon Peltier]

The limit is related to an archaic (the core code was written in the early
1980s) limit on the number of font resources in a workbook, or maybe a
worksheet. That limit is about 256, but somewhat less. The font resources
are determined by adding up how many fonts are used in the worksheet and in
each chart. If you use only Arial and you have three charts, that's four
font resources. If you use auto font scaling, each chart needs two font
resources per font per chart, so one worksheet with three charts would need
seven chart resources. So I guess the limit is between 200 and 250 charts.
This is more than anyone can easily keep track of.

Regarding chart types, keep in mind that stacked charts are only
intermediate in effectiveness between junk charts like pies and standard
charts like line charts and clustered bar charts. You may as well continue
your lobbying efforts towards the best options there are.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______