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.

Related Posts:

• Excel 2007 Recalculates Slowly
• Poor Charting Performance in Excel 2007
• Excel 2007 Chart Performance – Revisited
• Update Regular Chart when Pivot Table Updates
• Dynamic Chart using Pivot Table and VBA
• Time Trials of Approaches to Measure Minimum and Maximum Chart Values
• Build an Excel Add-In 2 – Enhanced Functionality
• VBA to Split Data Range into Multiple Chart Series
• Build an Excel Add-In 1 – Basic Routine
• BonaVista Releases Chart Tamer