Poor Man’s Sparklines in Microsoft Excel

Microsoft introduced Sparklines as a native feature of Excel 2010. In a rare guest post, Sparklines For Excel vs. Excel 2010 Sparklines by Alex Kerin of Data Driven Consulting compared this new feature to existing third-party sparkline add-ins for Excel. In Sparklines and Data Bars in Excel 2010, I gave an introduction into how to use the new sparkline feature, demonstrated some of the options available for Excel 2010’s sparklines, and also showed how Excel 2010’s implementation of Data Bars was far superior to the original Data Bars in Excel 2007. I’ve also shown How to Make Horizontal Bullet Graphs that can be fitted into the cells of a dashboard.

While it’s good to see sparklines as a native Excel feature, the Excel 2010 implementation is rudimentary, and third-party sparkline products have more functionality and more features than the native Excel sparklines. Yet it’s not necessary to deal with add-ins in order to realize expanded sparkline capabilities in Excel. Excel’s regular charts can be used to create decent sparklines, and this article will show you how.

Native Excel 2010 Sparklines

It’s easy enough to insert sparklines into an Excel 2010 worksheet. First, make sure you are not in “Compatibility Mode”. Compatibility Mode means the active workbook is as compatible as possible with Excel 2003; the most obvious feature is that the worksheet grid has the Excel 2003 numbers of rows and columns, not the expanded grid introduced in Excel 2007.

Select the data range or the location for the sparklines, and click one of the Sparklines buttons on the Insert tab. In this example, I selected the range where I wanted the sparklines to appear, then clicked the Column Sparkline button. The dialog shows the selected range in the Location Range edit box.

Create Sparklines Dialog

Then I selected the data range, which appears in the Data Range edit box. For some reason, the Location Range edit box is cleared (and each box clears itself when the other box is edited), but Excel remembers the selected range.

Create Sparklines Dialog with Data Source Selected

Here are the sparklines with the location range still selected.

Built-In Sparklines in Excel 2010

Finally, here is the table with sparklines, with the active cell out of the way.

Built-In Sparklines in Excel 2010 (Clean)

Create Sparklines From Regular Charts

The protocol for generating your own sparklines using regular charts is presented below. This protocol works well in Excel 2010 and 2007. In earlier versions of Excel, charts have a border of several pixels around the plot area, so the chart area must be sized larger than the cell you want the sparkline displayed in. In earlier versions, there is also a limit to how much the chart can be shrunk and still show the entire plot area, so you’ll have to shrink the chart only partway, then shrink the plot area to a smaller fraction of the chart area size.

Keep in mind that while you can use regular charts for sparklines, the small size of a sparkline limits the amount of information you should try to cram into one. Leave out labels and limit yourself to about two series maximum in any given sparkline.

Start creating your sparkline by selecting the data for a single series.

Select Data for First Manual Sparkline

Insert a chart of the desired type. This is a typical Excel 2010 column chart.

Initial Appearance of Manual Sparkline

Now simplify formatting. Below left shows the chart with the chart area border removed and the chart and plot areas made transparent, so borders and cell fill colors show through. Below right, the legend and any axis and chart titles have been removed.

Remove Background and Borders, Remove Titles and Legend

The bars have been widened in the chart below left (by decreasing the gap width to 50%), and the major unit of the Y axis has been set to a small value, below right.

Fatten Up Bars (Reduce Gap Width), Shrink Major Y Unit

The axes have been hidden by selecting “None” for axis tick marks and axis tick labels and choosing “No line” for the axis line color. Note the size of the plot area within the chart.

Hide Axes and Note Plot Area Size

For best results, extend the plot area almost to the left and right edges of the chart, and stretch the bottom of the plot area to the bottom of the chart. Leave a large margin between the plot area and the top of the chart. In fact, you may have to increase this top margin in the sparkline, after the chart has been shrunk to fit a cell.

Resize Plot Area

Finally, shrink the chart and position it over the appropriate cell. If you hold down the Alt key while moving or resizing the chart, the chart edges will line up with the cell boundaries. If necessary, shrink the plot area from the top to increase the margin.

First Manual Sparkline Resized and Relocated

When all of the necessary formatting has been applied to the sparkline, and nothing else needs to be done, copy the sparkine and paste it into each of the other cells that need a sparkline. If you hold Ctrl and Alt while dragging the chart, a copy of the chart will be dragged into and aligned with the next cell.

Sparkline Copied and Pasted As Needed

All the charts are formatted identically. They also use the identical data, so let’s fix that.

Select the first sparkline. Notice how the source data is highlighted in the worksheet.

First Sparkline and Data Dange

Select the second sparkline. Notice how the highlighted data is from the first row. So is the highlighted data for the third sparkline.

Second Sparkline and First Data Range

You could change the source data by choosing Select Data from the Chart Tools > Design tab, or from the right-click menu. You could also edit the chart series formula. But the easiest way to adjust the chart data is to drag the range highlight with the mouse. Move the mouse over the highlight until its border thickens, then drag it to the new range. If you’ve selected the plot area or chart area, both the series name and the Y value highlights move together. If you’ve selected the series itself, the series name and Y data must be changed separately.

Data Range for Second Sparkline Being Moved

Correct the third sparkline’s source data in the same way.

Here is the finished table with sparklines. Looks like the built-in sparklines.

Corrected Data Ranges for All Sparklines

You can use any chart type: here’s a line chart with markers.

Works With All Chart Types: Here Is a Line Chart

Why Use Regular Charts?

There are a number of reasons to use regular charts rather than the built-in Excel 2010 sparklines.

Excel Version

One obvious reason is that you might not have upgraded to Excel 2010. If you’re using Excel 2007, the techniques shown here work the same way.

In Excel 2003 and earlier, the chart imposes a thicker border between the plot area and the chart area, so you have to oversize the chart to make the plot area fit as intended. Also, in earlier versions, the plot area only shrinks a certain amount within the chart area, so further shrinking of the chart truncates the plot area. The chart area can only be shrunk so far, but the plot area can be reduced further without shrinking the chart. The result is a chart that’s substantially larger than the cell it covers, but making the plot and chart areas transparent makes this no problem.

Mouseover Information

An important feature of a regular chart that is lacking in a sparkline, is the ability to mouse over a point and read relevant information from a popup. This is very useful in an interactive dashboard.

Native Sparklines Have No Mouseover Popups

Combination Charts

What if you want to compare a time series to other data, such as a target value.

Combination Chart: Target and Actual

Native sparklines can only plot one timeline, while a regular chart has more flexibility (as do some of the third-party sparkline programs).

Combination Sparkline: Target and Actual

Remember the limited resolution of a cell-sized chart: adding the simple target data to these sparklines is almost not worth the effort.

Other Embellishments

You may wish to reverse the vertical axis. For example, your costs are probably reported as positive numbers, but you want to show them as negatives.

Reverse Y Axis So Positive Values Plot As Negative

Native sparklines do not support reversing the vertical axis, but regular charts do.

Sparkline With Reversed Y Axis

 

Peltier Tech Charts for Excel

Sparklines For Excel vs. Excel 2010 Sparklines (Guest Post)

Back in November I came across a new blog, called Data Driven Consulting. On this blog I found articles covering some of my favorite topics, including graphics and data visualization. I snooped around and discovered that the author of the blog, Alex Kerin, lives only 20 miles away from me here in the snow belt of Massachusetts. We got together over lunch to discuss our mutual interests, and the first outcome of our meeting is this post which Alex has prepared for the Peltier Tech blog.

Alex is founder of Data Driven Consulting, an independent consulting company that focuses on obtaining high quality data to facilitate effective business decisions. Data Driven Consulting specializes in a number of areas, including market research, dashboard and data presentation, and authoring of white papers and e-content.

One of the more anticipated functions of Excel 2010 is Microsoft’s implementation of sparklines (as defined by Edward Tufte – data intense, design-simple, word-sized graphics). Although the recent patent application made by Microsoft would seem to imply that they are the inventors of in-cell sparklines, both Edward Tufte and the software vendors who have offered Excel sparkline solutions would beg to differ.

One of these solutions, Sparklines for Excel (SFE), is favored by many, is free and open-source, and works well. There are versions for Excel 2003 (and before), and Excel 2007, with the latter version adding a new section to the ribbon. Below are screenshots of the implementations – click on these reduced images to open a new window showing the full size sparkline tabs.

Excel 14 Native Sparkline ribbon tab
Excel 14 Native Sparkline ribbon tab

Sparklines for Excel add-in ribbon tab
Sparkline For Excel Add-In Ribbon Tab

Given the anticipation of Excel 2010’s sparklines it makes sense to compare the two offerings. This table compares the functionality of the two options.

Sparklines for Excel
Excel 2010
Types of cell charts 14 and growing: Line, Pie, Pareto, Bullet, XY, Scatter, Bar, Column, Stripe…
Three: Line, Column, Win/Loss
Overall look (sparklines)
Some blocking, not noticeable at normal zoom levels

Smoother
Features on individual chart types Excellent, whole range of markers like hi/low lines, max/min points
Limited to some basic additional data indicators, but they work well
Ease of deployment Have to distribute macros with file, some VBA crashes, mostly during creation. Win today, as no built-in option No extra package, but Excel 2010 needed. Win in 2-3 years when 2010 is ‘standard’.
Ease of use More complex, for  example looking up meaning of formula variable, color chart Easier, native to the product, so formatting is quick
Code Customization and Stability VBA macro based (non-locked) so customization possible (and legal) Not VBA based – more stable, but no customization
Grouping Can set max and min so that multiple charts
scale the same
Adjacent charts are grouped automatically making for easy changes of formatting/scales
Ease of editing Charts are shapes created on the basis of formula in the cell Effectively separate charts, edited with own section on ribbon
Future additions Constant addition of new chart types/upgrades to existing
Maybe some fixes, but will likely have to wait until Excel 20XX for new chart types
Cost Free, but you need Excel of course
Free, at least after you’ve paid any upgrade costs

While there are equal numbers of ticks in both columns, there are clear winners depending on your required functionality and how you intend to share the information. For creating dashboards that benefit from different types of in-cell charts and line markers, SFE is still a clear winner – the range of chart types, the continued development, along with the fact that the sparklines are formula based makes for a powerful tool. Distribution is complicated by the need to load the add-in file, but for dashboards that shouldn’t be a barrier.

On the other hand, for a quick summary of data that is emailed around, the native version in Excel 2010 is clearly simpler. In the near term (until 2012 I would guess), SFE is still really the only option anyway as sparklines created in Excel 2010 simply won’t show on earlier versions.

It’s great that Microsoft has offered sparklines – hopefully end-users will appreciate their value more now they are a native part of the program, and become used to sparklines’ inclusion in their data lives.

 

Peltier Tech Charts for Excel

Peltier Tech Charts for Excel 3.0