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.
Robert Kosara says
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.
JP says
So I take it you are still using 2003 for most of your charting work? :)
Jon Peltier says
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.
TM says
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.
Jon Peltier says
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?
Charles Williams says
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
gman says
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….
Ed Green says
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
Peter Ennis says
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
Jon Peltier says
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.
John McCallum says
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.
Jon Peltier says
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.
Andreas says
If you are creating the graphs using a VBA macro there is a workaround for the slow charting in Excel 2007. The workaround is to use simple + or X markers instead of the diamonds, circles, stars etc.
myChart.SeriesCollection(1).MarkerStyle = xlMarkerStyleX
I use a macro to create multiple scatterplots, having multiple series; with around 10-20k datapoints in each; on multiple sheets, in a workbook. The redrawing performance then becomes comparable to Excel 2000.
Andreas says
The + or X markers can of course also be set manually… :)
Jon Peltier says
Andreas –
Thanks for the suggestion, I wonder how this would look if I tested performance of different marker styles.
Mr. Kariranta says
Thanks for your informative page!
I was just starting processing work of a huge set of measurements for my Masters thesis with X2007 when i noticed the problem with the drawing of charts. So then I was almost upgrading my HW with some memory and graphics card when I found your page and the real reason for the problems.
At the moment I have some 12000 data rows and I typically use 3-5 columns for my graphs and the working was just horrible when the screen draw the data points every time I even touched the graph.
I tried the hint of using X and + markers and it truly helps, the slowness is away, but I’m no way going to change the markers separately for each graph, it is just unnecessary and annoying thing to do when you have to produce large series of graphs specially as everything works fine with older versions. Or does someone know how you could change the markers to x or + by default?
Jon Peltier says
Mr K –
You can format a chart with the markers and colors you need, then save it as a user-defined style. This style can then be applied to new or existing charts.
Mr. Kariranta says
Yep, I noticed that and also that it is possible put your own chart template with x- and +-markers as a default chart style – tried that and it initially seems to make the graph making bearable, I’ll at least try to work that way for a day or two and postpone the decision of downgrading.
Dmitry Kolosov says
Not only is Excel 2007 notoriously slow when it comes to drawing the charts.
Too often I see it redrawing a chart in an infinite loop!
I have a Scatter chart with 18000 points. If I scroll the page so that the chart goes out of view and then scroll it back – Excel redraws the chart (it takes 8 seconds on my Dual-i7 with 4G RAM) – then immediately redraws it again, and again, and again – infinitely
Chris Wharton says
Excel 2007 seems to have a problem with chart events. The VBA code you wrote in GetXYonAnyChart does not work in 2007
David Gobbett says
After quite some time searching the net and messing around in Excel, I finally found a workaround to this problem, which I now see is also mentioned above (if only I had seen that earlier…). Setting the Marker Options to Built-in (e.g. simple x) prevents the slow (sometimes looping) redraws of a chart with 3 series of 9000+ points and the performance becomes quite satisfactory.
ChemE says
Thank you all who posted the marker options fix. I was ready to ragebreak my computer over this.
Elena Rozier says
Hi Jon! Is there any way in Excel 2007 to prevent the graph from refreshing?
Jon Peltier says
Hi Elena –
Long time!
Yeah, well, anything bad about 2007/2010 charting usually cannot be worked around. Redrawing of charts happens when Excel wants it to happen, and sometimes I think redrawing the chart causes Excel to redraw the chart again. At least that’s how it seems. I try to avoid charts with a lot of data.
No Khorry says
Hi. I know some formulas which are related to Dates and Times Function, but I’ve found some problems with, “How to create Calendar.” So I need your tips and tricks.