Excel 2007 Chart Performance – Revisited
by Jon Peltier
Friday, October 10th, 2008
Peltier Technical Services, Inc., Copyright © 2010.
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.
Related Posts:
- Poor Charting Performance in Excel 2007
- Excel 2007 Recalculates Slowly
- Chart Redrawing Performance of Excel 2003 and 2010
- Redrawn Electoral Maps
- Spider Chart Alternatives
- Slow Loading Blog
- Chart Busters Calorie Chart
- Close Races
- In-Cell Bullet Charts
- Back in the Swing
Posted: Friday, October 10th, 2008 under Excel 2007.
Comments: 12
Comments
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
Comment from Peter Ennis
Time: Friday, May 1, 2009, 11:10 pm
Jon,
I submitted an application to Chad Rothschiller, program manager for Excel,
related to the poor charting performance and provided a link to your analysis.
They were aware of it and doing performance testing with the recommendation
being to try again with SP2.
Excel 2007 SP2 running on XP seems to be worse! I will do some testing
on Vista and eventually Windows 7.
Do you plan to rerun this test for Excel 2007 SP2?
Thanks,
Peter
Comment from Jon Peltier
Time: Saturday, May 2, 2009, 8:03 am
Hi Peter -
I think I did some similar tests on the SP2 beta, but it was long ago and far away, and I don’t recall where I may even have saved the results. As it was a beta, the results are probably not representative anyway. I do in fact plan to rerun the tests. I do not really expect a big difference: a priori I think the 2007 SP1 and SP2 will probably be grouped together, both substantially different than 2003.
Comment from John McCallum
Time: Wednesday, December 2, 2009, 4:59 pm
I can scarcely get Excel 2007 to create graphs at all. What took milliseconds in Excel 2003 is taking minutes in Excel 2007. Probably 4 orders of magnitude. My graphs are much larger than your tests here – typically around 18000 data points. From what I have been able to find on the web, it seems Microsoft was aware of this and created a fix that worked. That fix was supposedly included in SP2, which is what I am running. As Peter pointed out, SP2 still has severe performance issues.
Comment from Jon Peltier
Time: Wednesday, December 2, 2009, 9:59 pm
John -
I think the real fix to Excel 2007’s charting performance problem is Excel 2010. I wrote about this in Chart Redrawing Performance of Excel 2003 and 2010.
Write a comment
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.
If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.
Read the PTS Blog Comment Policy.