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.
There are numerous products which add sparklines to Excel. Two popular commercial products are 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.and , which work by forming the graphics using specially designed fonts.
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 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.
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.
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.