Sparklines, Bullet Graphs, Minipies, and Whiskers

There are two new add-ins for Excel that deserve some special attention: Bissantz SparkMaker and BonaVista MicroCharts. They both provide a means for implementing some of the latest and greatest techniques in dashboard reporting, where the idea is to provide data-rich tables and graphs that enable people to make better decisions.

So what are sparklines? Edward Tufte defines sparklines in his book, Beautiful Evidence, as: "Sparklines are data-intense, design-simple, word-sized graphics." Below is an example of a sparkline created in Excel using SparkMaker. Bissantz invented font-based sparklines and was the first to apply the technique to in-cell sparklines in Excel.

Historical data from Yahoo! Finance as of Oct 2, 2006

(Sparkline created using SparkMaker Pro)

Other useful charts that can be displayed with fonts and fall into the category of "word-size graphics" or "in-cell graphs" include bar charts , mini pie charts , bullet graphs , and whisker graphs (i.e. win/loss charts) . The bullet graphs are currently only available in MicroCharts, but both SparkMaker and MicroCharts can create sparklines, sparkbars, mini-pies, and win/loss charts.

One of the problems with the font-based sparklines in SparkMaker is that the sparkline length is not adjustable, because it depends upon the number of data points. However, if you create an image rather than use the font-based sparklines, you can then resize the image. Because the image is a vector graphic, it should still print well. Before moving on to a different example, let's take a look at a sparkline produced using MicroCharts:

(Sparkline created using MicroCharts Pro)

MicroCharts contains an option for scaling the length of the sparkline. However, my test of the feature showed that although the charts scale up well, the sparklines do not necessarily scale down accurately.

One thing I noticed about both Excel add-ins: You must make sure that your data is in the correct order and equally spaced - like when you use line charts in Excel.

The definite advantage of these add-ins is the ease with which these charts can be made!

Creating Sparklines via Excel Chart Objects

Sparklines: I created a sparkline using a scatter plot in Excel, which allowed me to add a 3-month moving average trend line and also adjust the length of the sparkline. Note also that the first/last and min/max points are highlighted - and the size of the markers are adjustable. This approach was certainly not simple - the Excel charts don't easily scale to this size (you have to scale the axes, then delete the axes, and play with the margins).

(Sparkline created using an Excel scatter plot)

Bar Graphs: It is possible (but not easy) to make word-size bar charts using normal Excel charts. However, like the scatter plot above, the charts don't scale very well. Once you have one created, it's not too hard to copy and paste and change the source data.

(Word-size bar graph created using an Excel bar chart)

Pie Graphs: I couldn't get an Excel pie graph to scale down to word-size. But that is okay. I don't like mini pie graphs. When used for comparing data, scaled bar charts are much better. See the example below.

Bullet Graphs: I didn't bother trying to create a bullet graph using a normal Excel chart. It was too easy to just use MicroCharts.

Win-Loss-Tie Records: You can create word-size whisker plots or win/loss/tie graphs using Excel's stacked bar charts.

(Win/Loss graph created using a stacked bar chart in Excel)

To summarize, it is certainly possible to create some types of sparklines and other word-size graphs using normal Excel charts, but it involves quite a bit of mouse agility and toying with chart properties to get the graphs just the way you want them. It took me a good 30 minutes to create the three charts above using Excel.

One of the applications of these word-size graphs that got me excited was the ability to summarize survey data. I threw together an example of a 6-question teacher evaluation survey (the data is made up). The first table below was created using SparkMaker and the second was created with MicroCharts. I was very impressed with how easy it was to create the bar charts and pie graphs, and even the bullet graphs. Most of my time was spent making up the data for the tables. It only took me about 5 minutes to add the bar charts and the pie graphs (both SparkMaker and MicroCharts were easy to use).

(Bar graphs and pie graphs created using SparkMaker)

Creating the Histogram Bar Graphs: If you have survey data that consists of discrete values, such as integer rankings of 1-5, you can use Excel's FREQUENCY() formula to easily tally up the results for the histogram. You can integrate the frequency formula right into the SparkBars() function like this: =Sparkbars(FREQUENCY(A21:A61,{1,2,3,4,5})), where A21:A61 is the range containing the survey results. The frequency function is an array formula, so you need to press Ctrl+Shift+Enter after typing in the formula.

What is the purpose of the pie graphs? I set up the pie graphs to represent the ratio rating/5. A perfect rating would be a solid colored circle. The pie graph was created using the Excel formula =SparkPie(rating), where rating is the average listed in the table to the left of the pie graph. The color was modified by changing the cell's font color (very intuitive). Important: In this example, the pie graphs add very little value. If the idea is to compare the ratings, scaled bars would be much easier to interpret at a glance.

The table below was created using MicroCharts. Let me explain the bullet graphs a little. With instructor evaluations, it is common to compare a instructor to the performance of the department as a whole. The red vertical lines in the bullet graphs represent the average rating of for the department for each individual question. The green zone in the graph represents a "Good" range or a value between 4-5.

(Bar charts, pie graphs, and bullet graphs created using MicroCharts)

Important: The use of both the pie graphs and the bullet graphs is redundant of course. The column of pie charts should be removed. Notice how much easier it is to compare the size of the blue bars in the Target column? The bullet graph provides additional value by making it easy to see how the rating for a specific question compares to the department average, and whether the rating is in the desirable "Green Zone".

I would recommend downloading the trial versions of both SparkMaker and MicroCharts and choosing one (or both) after you've had a chance to play with them a bit. Make sure that you experiment with the software based upon not only how you will want to create the sparklines and other graphs, but also how you will give/present the information to others. SparkMaker Pro makes the sparklines and other graphs available in other office applications (Word, PowerPoint). Both SparkMaker and MicroCharts can convert the charts to images - making the graphs portable to almost any other application. The program with images alone is a loss of resolution when printing. Creating a PDF may also be an option for sharing the charts, graphs, and tables with others.