Top 5 Tips for Using Sparklines in Excel

Sparklines are tiny charts displayed inside individual cells in your spreadsheet. Introduced in Excel 2010, they provide a convenient way to quickly compare data sets and analyze trends. If you’re not sure how to use these fantastic little charts, don’t panic – there’s a walkthrough at the end of the article!

Here are 5 tips for using sparklines in Excel:

Tip #1 – Place Sparklines Near Your Source Data

For maximum impact, you should display sparklines adjacent to your data. You want people to immediately understand the connection between the numbers in your spreadsheet and the little charts representing your figures.

Excel Sparklines

Tip #2 – Choose the Best Chart Type for Your Scenario

Make it easy for people to understand the information in your spreadsheet. If you’re trying to highlight trends like an increase in sales, or a decline in product defects, use a line chart. For a comparison of revenue or units sold, a column chart will do the job nicely. Tracking investments? The win/loss chart is the obvious choice!

Chart Types

Tip #3 – Display Useful Data Points

Use checkboxes in the Sparkline Tools > Design group to display data points representing the high point, low point, negative point, etc. These options offer a useful way to help people interpret the information in your spreadsheet.

Show Dialog Box

Tip #4 – Use Formatting to Emphasize Key Indicators

There are several ways you can format your charts. You can choose a colour for your entire chart in the Style Gallery or you can choose colours for specific data points using the Marker Color control in the Sparkline Tools > Design > Style group.

Style Gallery

Tip #5 – Create Additional Sparklines Using the Fill Handle

If you need to append additional data to your spreadsheet, you can easily add more charts. Create additional rows for your data as required then highlight the cells containing your sparklines and drag the fill handle down to add more!

Fill Handle

How to Create Sparklines in Excel

Here is a quick tutorial on creating and working with sparklines.

  1. Selecting the range where your charts will be displayed. In the example (below), I have selected F4:F8.
    Tutorial 1

  2. Next, select the type of chart you want. I selected Column from the Insert > Sparklines group on the Ribbon.
    The Create Sparklines dialog box appears with the Location Range box already filled in.

  3. I enter B4:E8 in the Data Range box and click OK.
    Tutorial 2

  4. Excel displays my charts!
    Tutorial 3

A Few Final Comments

Sparklines are easy to create and they add visual interest to your spreadsheet, making your data easier to understand. And remember: these little charts are dynamic. If you modify the source data, everything will update automatically!