Excel 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.

In the old days, say, from Excel versions 97 through 2003, when you opened a workbook containing VBA code, you received a big ugly modal dialog box with the macro warning. You had to click a button on the dialog in order to continue. The dialog was annoying, but it was a reminder to allow the code to run.

In Excel 2007, if the Visual Basic window is open, you still get this dialog. In fact, the dialog shown above is the Excel 2007 version. However, if the VB window is not open, you don’t get the dialog. Instead you get a notice embedded in the UI. It’s a non-modal message, with no beep, and it doesn’t make you respond to it.

See the notice, tucked away between the ribbon and the formula bar? I didn’t think so. Half the time I don’t notice it, and I run around wondering what’s wrong.

I can’t count how often I’ve loaded a workbook with macros, and not known why they don’t work. Or at least I’m annoyed by the next message.

Excel 2010 works like 2007. If the VB editor is open, you get the blatant dialog that forces you to respond. If the VB editor is closed, you still get the sedate little notice in the UI, but it’s no longer camouflaged to blend in with the ribbon.

See it there, between the ribbon and the formula bar? Think you could possibly miss it? Good.

This is one bit of gaudy formatting that I welcome. I always see the notice immediately every time it appears.

I heard a rumor that Excle 2010 had expanded limits to how many points you could plot in a chart. From Excel 97 through 2007, you were limited to 32,000 points per chart series, and 256,000 points per chart. As far as I was ever concerned, this was more than enough points to make a chart unreadable. Most charts have a plot area less than 250,000 pixels. The plot area in the following chart, which I’ve shaded yellow, is rather large, 605 x 423 pixels, or 255,915 total pixels. If every point is one pixel in size, and no points overlap, you couldn’t fit them all into this chart.

Never mind now long it takes to redraw.

Number of Points in a Series

Despite the limited utility of cramming data points on top of each other, I thought I’d investigate the rumored new limits. Here’s an Excel 2010 chart with 100 points; I’ve used the row number for X and 1/(row number) for Y.

Let’s skip 1000 points, here’s a chart with 10,000 points

Keep piling on. Here’s a chart with 32,000 points, the maximum allowed in a series in earlier Excel versions.

100,000 points, anyone?

Okay, I’ll bite. Here’s a chart with all of column A as X values and all of column B as Y values. 1,048,576 points. And it didn’t choke. It took a while to redraw, of course.

Check out this unique chart series formula. It uses the entire columns in the definitions of X and Y.

Number of Series in a Chart

I tried the same thing with the number of chart series. This is what 100 series look like in a chart. The X values are 1 through 25. Series 1 uses Y values 2 to 26, Series 2 uses 3 to 27, and so forth.

Here’s the same chart with 255 series, the maximum number of series in a chart in prior versions of Excel.

I tried adding more, and learned I’d exceeded the limit.

Summary

The number of series allowed in a chart in Excel 2010 has remained the same as in earlier versions, 255 series, but the number of points in a series has increased dramatically, from 32,000 to 1,048,576, so that entire columns in the larger grid of Excel 2007 & 2010 can be used as the source data of a chart series.

The total number of points allowed in a chart has obviously increased, since the old limit of 256,000 points is smaller than the new limit on points per series. Since drawing one series with a million points took over a minute, I did not have time to explore this limit. (Hmm, I also didn’t check whether the help files have yet been updated with the new limits.)

There are two Conditional Formatting features in Excel 2010 which allow for graphical displays right in the worksheet. Sparklines, the word-sized graphical elements invented by Edward Tufte, are a new addition to Excel 2010. Data Bars were introduced in Excel 2007, but they have been improved and expanded in 2010. I gave each a test drive today.

Sparklines

There are numerous products which add sparklines to Excel. Two popular commercial products are BonaVista Microcharts and Bissantz SparkMaker, which work by forming the graphics using specially designed fonts. Sparklines For Excel is an open source add-in which works by drawing sets of shapes to construct the sparklines. These are full-featured sparkline programs which accommodate many chart types and styles.

Microsoft has gotten its start in sparklines in Excel 2010. The Microsoft Excel Team Blog has discussed this new feature in Sparklines in Excel, Adding Some Spark to Your Spreadsheets, and Formatting Sparklines. There are only three chart types: line, column, and high-low; and they do not have such features as baselines or axes or shaded zones. However, I think it’s a promising start.

To try out the sparklines, I loaded my blog stats into a pivot table, with months in rows and day of the month in columns. Creating the charts was easy. I selected the block of data to plot, then clicked the Sparklines – Lines button on the Insert tab.

A simple dialog pops up with two RefEdit boxes, one for selecting the data, the other for selecting the cell(s) to contain the graphics. If you’ve selected cells containing data, the Data Range box indicates this range; if you’ve selected a range of empty cells, the Location Range box indicates the selected range. These two ranges do not need to be on the same worksheet.

Click OK and the sparklines appear in the indicated position.

The weekly cycles within each month are readily apparent, but the scales aren’t quite right. The statistics begin in March 2008, about one month after starting my blog. The numbers on the first day were nowhere near where thay have been for the past few months.

The default setting is that each sparkline scales the vertical position of the data point so that its minimum and maximum fill the cell. When I selected the ‘Same for All Sparklines’ setting, the scales of all sparklines are the same, so the earlier months are pretty much flat compared to more recent months.

It’s easy to change from one sparkline style to another: just click in the range of sparklines, then click the button for the desired style.

Above are the line and column styles. A third style is Win-Loss, which plots a positive block for any positive number, a negative block for any negative number, and a blank for a zero.

The sparklines in a set are grouped together, and when you select one cell, the whole range of sparklines is outlined with a thin blue border, as shown in the sine waves below. When you apply sparkline formatting to a cell containing a sparkline, all sparklines in the group assume this formatting. You can ungroup the sparklines, and format them individually.

Although there are a limited number of sparkline chart types, within each type you have a range of formatting options. In the line charts, you can draw the line only, you can add markers, and you can format positive and negative markers differently (there is apparently no distinction between zero and positive markers). You can highlight the first and last points, and the high and low points. I added the last group to see what happened when the cells contained both sparklines and content. Apparently the sparklines decorate the back of the cell, and the text appears in front.

You can format the bar chart sparklines all the same (top left) or different positive and negative bars (top right). You can highlight the first and last bars, or the high and low bars (bottom left and center). Any text in the cell appears in front of the sparkline.

The top center group below shows how one sparkline can be ungrouped in order to format it differently. The other three have remained grouped despite not being contiguous.

Data Bars

Data Bars appeared in Excel 2007 as a way to show values visually using bars within the cells containing the values. In 2010 the capabilities of Data Bars were expanded, and the ability to make deceptive Data Bars was reduced.

Data Bars in 2007 had their base at the left edge of the cell, and they extended to the right. If a cell had a zero value, it still had a small length of bar, giving the wrong impression that the cell really did contain some value. Also, the bars started out with a good enough color at the base, but they faded to the right, so in some cases it became very difficult to judge where the bars ended.

These deficiencies were corrected in Excel 2010. Below left is the Excel 2007 representation of data bars. The first few cells contain zeros, but the cells have data bars of finite length. The bars increase in size appropriately, but they fade out from left to right, and eyes are forced to work just too hard to distinguish their ends. Finally, at the bottom the values decline asymptotically towards zero, but the bars don’t completely vanish.

In the center is the Excel 2010 version of the same data bars. Zero equals zero, and the bars have a distinct endpoint. You still can make the faded bars, but you know better. I’m not sure whether you can make nonzero bars for zero values; I don’t think so, and I hope not.

In Excel 2010 you can make your bars go right to left, as shown below right.

The unorthodox treatment of non-positive values in Excel 2007’s data bars is further illustrated below. At left, since Excel 2007 didn’t allow for negative or right-to-left data bars, the sine wave showed positive bars, even for the most negative value. The Excel 2010 data bars plot negative values in the opposite direction, optionally in a different color.

You can control the color of the axis where positive meets negative, but unfortunately you cannot change the line style. The designers have picked a dashed line, which by nature of its discontinuous dashes, draws more attention to itself than a solid line would. However, data bars have been improved so much, that a small cosmetic problem like this isn’t too important.

Backwards Compatibility

A commenter on one of the Sparklines blog posts wondered what would happen if a workbook with sparklines were opened in a previous version of Excel. I tried it and discovered:

• A workbook containing sparklines will show blank cells when opened in Excel 2007 or 2003.
• A workbook containing Excel 2010 data bars will show Excel 2007 style data bars when opened in Excel 2007, and blank cells when opened in Excel 2003.

In either case, you receive a warning about the file being created in a later version of Excel, and while your version of Excel will do its best to open the workbook, there may be some formatting which will not be faithfully displayed.

I also tried round-tripping a workbook through previous versions of Excel:

• When a workbook with 2010 data bars and sparklines is opened and saved in Excel 2003, and reopened in Excel 2010, the sparklines have vanished, and the data bars have reverted to Excel 2007 style (bars fade at the ends and all start at the minimum at the left and extend to the right, although there are no positive length zero values).
• When a workbook with 2010 data bars and sparklines is opened and saved in Excel 2007, and reopened in Excel 2010, the sparklines reappear, and the data bars retain their Excel 2010 style.

Apparently these features survive displacement by one version, but not by two.

Summary

Excel 2010 introduces a simple version of sparklines for compact visualization in the worksheet. The native Excel sparklines are not as comprehensive as existing third-party solutions can produce, but they can still be useful in many cases.

Excel 2010 also fixes conceptual problems and cosmetic issues with the data bars that were introduced in Excel 2007.