Chart Redrawing Performance of Excel 2003 and 2010

In Chart Point Limits in Excel 2010 I investigated the charting capabilities of 2010, in regards to the number of points allowed in a chart. I noted that charts with a million points took a while to redraw, but let’s not forget, a million points is a lot of points.

I decided a comparison was in order between Excel 2003 and Excel 2010. I have Excel 2003 SP3 and Excel 2010 TP installed under Windows XP Pro SP2, on a three-year-old Presario laptop (which has miraculously recovered from a bad crash) that has 2MB of RAM and a 1.79 GHz AMD single-core processor.

I used the following macro to automatically change the chart’s data range, and measure how long it took to redraw the chart. I started at -2 just because the code seemed to run slowly the first loop or two, and I wanted it to warm up. I adjusted the code to account for the much smaller limit of points per series in Excel 2003.

Sub point_timer()
  Dim i As Long
  Dim i10 As Long
  Dim t As Double

  t = Timer
  For i = -2 To 12
    i10 = IIf(i < 0, 1, 10 ^ (i / 2))
    ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).Values = ActiveSheet.Range("B1")
    ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).XValues = ActiveSheet.Range("A1")
    DoEvents
    t = Timer
    Application.ScreenUpdating = False
    ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).Values = ActiveSheet.Range("B1").Resize(i10)
    ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).XValues = ActiveSheet.Range("A1").Resize(i10)
    Application.ScreenUpdating = True
    DoEvents
    Debug.Print i, i10, Timer - t
  Next
  ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).Values = ActiveSheet.Range("B1")
  ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).XValues = ActiveSheet.Range("A1")

End Sub

Here are Excel 2010 and 2003 charts showing my data (X = Row and Y = 1/Row) for 10 points:

Excel 2010 chart with ten points

Excel 2003 chart with ten points

Here are Excel 2010 and 2003 charts showing their respective maximum number of points:

Excel 2010 chart with 1,048,576 points

Excel 2003 chart with 32,000 points

I’ve just noticed that the chart export feature has stripped off the axis labels in the Excel 2010 chart.

The timed redrawing results are shown in this table, seconds to redraw vs. number of points:

This is a blog about charting, so I have charted the performance here:

At the low end, both versions show a plateau where redraw times are insensitive to the number of points. Excel 2003 is faster than Excel 2010, but by a factor of two or less. At the high end, both versions converge on the same redrawing vs. points line. The ultra long redraw times I observed last week obviously was not due to inferior redrawing, but to superior amounts of data.

This is good news. It means that the work Microsoft has undertaken to improve charting performance, particularly chart redrawing performance, has paid off.