Excel Tip of the Week: Sparklines

Today’s topic is Sparklines.

Sparklines are a new feature to Excel 2010 and 2011 that let you quickly obtain a visual representation of your data. Graphs in Excel are extremely useful, but sometimes they are overkill when all you need to show is a quick trend line. Also, adding multiple sets of data to one graph can clutter the graph and make it hard to read. Sparklines provide the perfect alternative to the standard graph and come with several unique features.

Here is a screenshot of one way sparklines can be used:

Widgets and Gidgets have the exact same amount of sales over the past seven years, but notice the lines to the left of the table. Those are the sparklines. The sparklines make it very clear that Widgets are booming and Gidgets and performing very poorly. With just one glance the problem is obvious thanks to the sparklines.

Here are the instructions to recreate the above sparklines:

1)      Download the example spreadsheet here

2)      Move your cursor into cell B5

3)      PC: From the “Insert” menu in the “Sparklines” grouping select “Line” (Keyboard shortcut: Alt→N→S→L)

4)      Mac: From the menu bar, click on “Insert” and then “Sparklines”

5)      You will see a box that looks like this show up:

6)      With your mouse or the arrow keys, highlight cells  D5:J5 and click OK

7)      Now copy cell B5 and paste to cell B6. Excel will automatically shift the formula for you. (One of the cooler sparkline features if you ask me.)

That’s the basics of sparklines, but they can take many different forms and you have a surprisingly wide array of options to customize them. When you click into a cell with a sparkline, notice at the top the new menu option that looks like this (Mac users will have one that looks different):

If you click into that menu you have the option to change the color of the line, add markers to the line, change the sparkline to a bar chart, and other options.  Here are some screenshots of other sparklines I’ve made:

Above is an example of a Win/Loss sparkline. The data feeding into the sparklines is not shown in the screenshot.

Finally, above is a picture of the column spark line. These sparklines have all been placed on the same vertical scale so that comparisons across plants are possible. This option is accessed from the “Axis” feature in the “Design” Tab.