Waterfall Chart
Box and Whiskers
Excel 2007 Chart Performance - Revisited
by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2009.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
In Poor Charting Performance in Excel 2007 I compared the charting performance of Excel 2007 to that of Excel 2003. I did this by recalculating the random numbers used as chart data for XY and Line charts with 100 to 1000 points, and averaging the time it took to recalculate 100 times.
I used Virtual PC to do my testing, using two virtual machines with Windows XP SP2 running in 513 MB, one with Excel 2003 SP3, the other with Excel 2007 SP1. Robert Kosara wondered whether using only 513 MB was strangling Excel 2007. I changed the settings on the two VMs so they each used 1023 MB, and I reran the tests.
Running in a larger memory space did help Excel 2007. The XY charts showed an improvement of 11-20% in redrawing time, while the line charts showed an improvement of 19-34%.
The larger memory also helped Excel 2003. The XY charts showed an improvement of 12-20% in redrawing time, while the line charts showed an improvement of 15-30%.
Excel 2007 is still about an order of magnitude slower than Excel 2003.
The test results can be read in ChtPerf2003v2007ram.csv. Here is the pivot table of the data.
Here is a comparison of 2003 and 2007 XY charts. Both versions of Excel experience an improvement with 1023 MB over 513 MB, but the ratios between 2007 and 2003 redrawing times are essentially unchanged.
With 1023 MB of RAM, the Excel 2007 XY charts take 8.5 to 11 times as long to redraw as the Excel 2003 XY charts.
Here is a comparison of 2003 and 2007 Line charts. As with XY charts, the Line charts show some improvement with 1023 MB over 513 MB, but the ratios between 2007 and 2003 are essentially unchanged.
With 1023 MB of RAM, the Excel 2007 Line charts take 15 to 22 times as long to redraw as the Excel 2003 Line charts.
Overall, charts in Excel 2007 are redrawn much more slowly than those in Excel 2003.
Update October 10, 2008
In a comment in a related post, Simon Murphy wondered how much of the chart redrawing discrepancy was due to slower recalculation in Excel 2007. I fired up the two VMs, and I ran tests measuring the time it takes to recalculate various numbers of cells. The results can be read in Excel 2007 Recalculates Slowly.
Possibly Related Posts:
- Poor Charting Performance in Excel 2007
- Excel 2007 Recalculates Slowly
- Redrawn Electoral Maps
- Spider Chart Alternatives
- Close Races
- Back in the Swing
- Repurpose the Red X Close Button on a VBA UserForm
- Slow Loading Blog
Posted: Friday, October 10th, 2008 under Excel 2007.
Comments: 8
Comments
I welcome comments from my readers. If you have an opinion on this post, if you have a question or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion.
Read the PTS Blog Comment Policy.
Comment from Robert Kosara
Time: Friday, October 10, 2008, 4:15 pm
Interesting, I would have expected Excel 2007 to gain more from more memory than Excel 2003. Would be interesting to see if there is a difference in calculation vs. drawing, as somebody suggested. But I guess it will be mostly due to the more elaborate drawing engine in 2007.
Comment from JP
Time: Friday, October 10, 2008, 4:42 pm
So I take it you are still using 2003 for most of your charting work? :)
Comment from Jon Peltier
Time: Friday, October 10, 2008, 5:52 pm
JP -
I use Excel 2007 for two main purposes:
1. So I can develop dual 2003-2007 user interfaces for my clients.
2. To test for bugs and inconsistencies (for clients and to verify what I read in online forums).
I do no work of my own in 2007, and very little other work for clients in 2007.
Comment from TM
Time: Sunday, October 12, 2008, 5:33 am
I experienced significant performance inrease of calculation in 2007. If you perform same tests on dual core machine, i think, the results will be much different.
Comment from Jon Peltier
Time: Sunday, October 12, 2008, 12:20 pm
Hi TM -
That’s interesting. I do not have a dual core processor, so I cannot validate your findings. Do you have any data comparing 2003 with 2007-dual core that I could post here?
Comment from Charles Williams
Time: Tuesday, November 4, 2008, 11:11 am
Hi Jon,
There are some performance comparisons of calculations (plus VBA and Open/Close) using a range of workbooks on both single and dual core at
http://www.decisionmodels.com/VersionCompare.htm
XL2007 calculation speed on single cores is sometimes faster than XL2003 and sometimes slower: depnds on the nature of the workbook.
regards
Charles
Comment from gman
Time: Sunday, November 9, 2008, 1:53 pm
I have dual core. The problem is Excel 2007 was NOT OPTIMIZED FOR DUAL CORE in regards to charts.
Just to DRAW the chart/refresh it, I always see only 1 core being used!
I also have Windows XP SP2 and 2 GB Ram. The ram usage is never full, but the cpu is always at 100% in Excel 2007 when redrawing a chart….
Comment from Ed Green
Time: Thursday, December 4, 2008, 4:59 am
Hi Jon,
I’ve spent ages trying to optimise code that adds error bars to charts, and have concluded that the chart refresh is - as you show here - extremely slow indeed in 2007. Which is a great shame, given that some of the pivot table improvements are so useful.
You win some, you lose some…
Ed
Write a comment