Excel 2007 limitations when plotting variable width column charts


John McKibbin

I recently discovered a subtle limitation of Excel 2007 when building a
variable width column charts as proposed by Andy Pope (see
http://andypope.info/charts/colwidth2.htm). This specific implementation
involves building an array of values, charting them as a series of stacked
areas, and aligning the stacked areas into columns by setting the horizontal
axis type as a date. While this was previously the most versatile and robust
solution, new charting limits seem to have been imposed in Office 2007 that
limit its implementation.

In Excel 2007, the date-formatted x-axis appears to revert the axis type to
text upon reaching a maximum axis value of 32760, while Excel 2003 correctly
plots the chart up to the maximum value of date formatted data (i.e. 2958466).

While this may seem an academic question, such charts are widely applied to
generate the cost curves necessary in cost-effectiveness policy studies (see
http://www.epa.gov/air/caaac/coaltech/2007_05_mckinsey.pdf page 38), where
large magnitudes are required (~500,000 units), while maintaining high
resolution (~20-50 units), thus scaling the axis is an inappropriate
workaround (as date formatted numbers are stored as integers).

Can anyone interpret why this new limit was necessary in Excel 2007 and how
I could possibly overcome or work around it?

Jon Peltier

The new limit was necessary only because they didn't realize it wasn't
necessary in prior versions, and they weren't given time to get it right.

I've had to fudge with axis scales and dummy series to apply labels.

- Jon

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question