Since before Excel 2007 came out, there have been a number of complaints about how slowly its new graphics engine rendered charts. I decided to run a comparison between Excel 2007 SP1 and Excel 2003 SP3. To level the playing field, I ran each in Virtual PC within its own virtual machine, each allocated 513 MB and running Windows XP SP2.
I populated by blank worksheet with “x” and “y” in cells A1 and B1, and =RAND() in A2 and B2. I extended row 2 down N rows to populate an XY chart with N points. The XY chart had default formatting with markers only, no lines, and the X and Y axis scales were set to automatic.
I automated recalculation in the worksheet using this command from the VB Editor’s Immediate Window:
debug.Print now, timer : for i=1 to 100 : activesheet.calculate : _
next : debug.Print now, timer
which essentially recalculates the random numbers 100 times, recording the time (now outputs the date and time, timer outputs elapsed time in seconds) before and after the recalculations. Output of a typical run looked like this:
10/10/2008 9:00:55 AM 32455.06 10/10/2008 9:01:05 AM 32465.61
I calculated average chart redraw times by taking the difference between the two timer values, and dividing by 100. The times in Excel 2007 to redraw the chart for 100, 200, 500, and 1000 points is shown in this chart:
I remembered hearing that in the RTM version of Excel 2007, chart performance suffered if the axis scales were fixed, so I ran the same tests on a chart with fixed axes. It turns out that there’s very little difference.
Then I thought about the markers in the XY chart. They are made from the new Office shapes, which means they have built in the ability to contain all kinds of glittery effects, like gradients, shadows, bevels, glows, and the like. Perhaps If I made a line chart instead, with lines only and no markers, I would see a difference. (The line chart used only one column of random numbers.) In fact, there is a difference, and the line charts take substantially longer to plot.
I repeated the tests in my Excel 2003 VM. The line charts in Excel 2003 actually do take a shorter time to redraw than the XY charts. More striking is the different axis scale between the chart above and below this sentence.
The Excel 2007 charts take around an order of magnitude longer to redraw than the Excel 2003 charts.
The data I used in this analysis is available in ChtPerf2003v2007.csv. A pivot table I made from this data is shown below.
Here is a comparison of 2003 and 2007 XY charts.
The Excel 2007 XY charts take 8.5 to 10.4 times as long to redraw as the Excel 2003 XY charts.
Here is a comparison of 2003 and 2007 Line charts.
The Excel 2007 Line charts take 15 to 24 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 below, 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. The results can be read in Excel 2007 Chart Performance – Revisited.
Update October 10, 2008
In a comment below, 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.
derek says
For one horrible moment there, I thought the graphs were about how the markers turned up in different positions if the scale was fixed!
One of the disappointing things for me about old Excel is that when you use Office shapes, they plot significantly off when you try to print or export them. Does new Excel cure that fault? How about Wingdings and other typographical shapes, when used as centered data labels?
Robert Kosara says
Interesting comparison! Though given the small amount of RAM, I wonder if part or all of that difference might be due to Excel 2007 simply being larger and thus making use of swap space. It would be interesting to see if the numbers change when given 1GB of RAM.
Simon says
Jon
If you remove the charting step and just test the calc part, how much (if any) of that 15-24 is just 2007 generally calculating slower than 2003?
Jon Peltier says
Derek –
I haven’t tested such things as fine alignment of shapes used as markers. The time it takes to redraw has rarely affected me, except for a few charts with “large data sets” (10k points). The poor quality of charts pasted as metafiles into other applications is more of an issue. The most serious issue is the way that the whole new user interface cheats me of my efficiency, which is the subject of an upcoming post.
Robert –
A rising tide floats all boats. I just did some tests with about twice the RAM, and I’ll post a follow-up article soon. The performance of 2007 redrawing has improved, but so has that of 2003, so the ratios are almost as pronounced.
Simon –
Good question. Maybe that will be my next article after the chart follow-up. I suspect the effect on charting is more severe.
Jon Peltier says
The results of my follow-up tests, using 1023 instead of 513 MB Ram, can be read in Excel 2007 Chart Performance – Revisited.