- Excel Book Excerpt
Excel Create Tiny Charts with Sparklines
This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Create Tiny Charts with Sparklines
Problem: I need to create a chart for every row in my data set.
Strategy: Use a sparkline.
Professor Edward Tufte introduced the concept of sparklines in his book Beautiful Evidence. Tufte described sparklines as intense word-sized charts. Microsoft implemented Tufte’s ideas in Excel 2010 with three types of tiny charts: line charts, column charts, and win/loss charts.
Creating sparklines is simple, although you might want to tweak the default sparklines. Below, there are 27 months of closing stock prices for 3 financial firms. Select the data that you want to plot in the sparklines.
From the Insert tab, choose the Line sparkline.
Excel displays the Create Sparklines dialog. Because you pre-selected the data, you need to specify only the output range. Because the input range is 3 rows by 27 columns, the output range has to either be 3 cells or 27 cells. The size of the output range will determine whether you want 3 sparklines or 27 sparklines.
Figure 1089 Specify an output range.
Gotcha: In the rare case where your input range is exactly square, Microsoft will turn each row into a sparkline. If you want each column to be a sparkline, use the Edit Data drop-down on the Sparkline Tools Design tab and choose Switch Row/Column.
Excel will draw in the line charts, one in each cell. Here are default sparklines.
Figure 1090 Default sparklines have no labels or markers.
Excel will let you add markers to your sparklines. With a sparkline selected, the Sparkline Tools Design tab will be available in the ribbon. You can toggle on all points by choosing Markers, but a more interesting option is to choose High Point and Low Point
Figure 1091 Choose which points to add to the line.
After you’ve added the high and low point, use the Marker Color drop-down to choose a color for each type of point.
Figure 1092 Change the color of the markers.
For more resources for Microsoft Excel: