Free Online Microsoft Training

Free tips and tricks for using Microsoft Office and Windows

Free Online Microsoft Training

Free tips and tricks for using Microsoft Office and Windows

Create a Sparkline to show data trends in Excel

Print Friendly, PDF & Email

I can hear you asking it now, what is a Sparkline? It’s a very good question which I’m going to answer for you now.

Sparklines were introduced in Excel 2010 and are a great feature for those who prefer a “visual” approach to data (like myself). Sparklines are miniature charts which are displayed within a cell and can show trends within your data. The advantage of using a Sparkline instead of a chart is that instead of having a chart take up half your worksheet you can incorporate a Sparkline into a single cell allowing you to visualise a row or column of data at a time.

Below you can see my example of data with sparklines being used in column N.

Create a Sparkline in Excel

Microsoft Excel gives you the ability to insert three (3) different types of sparklines: Line, Column and Win/Loss. Which type of Sparkline you choose will depend on the type of data you are using and how you wish to display the information.

It is important to be aware that because Sparklines are a feature of Excel 2010 and above, you cannot view them if your Excel workbook is opened in earlier versions of Excel. Likewise, you will be unable to insert a sparkline if your current workbook is running in Compatibility Mode. To find out how to convert workbooks from older versions to the latest file formats, see my post Convert workbooks to the latest file format in Excel.

Create a Sparkline

To create a sparkline, follow these steps:

  1. Open Microsoft Excel
  2. Open an existing file containing data you can use to add a sparkline (Use Ctrl + F12 on the keyboard to bypass Backstage view and go directly to the Open dialog box) or just create a quick sample of data
  3. Place your cursor in the cell, where you wish to insert the Sparkline, I have selected cell N2
  1. Click the Insert tab from the Ribbon
  2. From the Sparklines group click Line
Create a Sparkline in Excel
  1. The Create Sparklines dialog box will appear:
  1. The Location Range has already been filled in as the cell you had selected. This will be the location the sparkline will be placed.
  2. Now you need to select the Data Range you wish to include in your Sparkline
  3. Use your mouse and highlight the data using the left mouse button
Create a Sparkline in Excel

TIP: You can also perform this action in reverse; instead of selecting the cell where you want the Sparkline displayed, highlight the data instead and then select Insert > Line. You will then just need to select the Location Range (cell) you want the actual sparkline to appear in.

  1. Once you have completed this step click OK
  2. The Sparkline is displayed in the cell:
Create a Sparkline in Excel
  1. You can now use the AutoFill tool to fill this sparkline down the column where appropriate. If you are not familiar with the AutoFill tool, see my article here.

Sparkline Groups

When using the AutoFill tool to create the remaining Sparklines, you will automatically create a Sparkline Group. A Sparkline Group means that any changes you make to one Sparkline, will automatically be applied to all Sparklines in the group. This is very useful if you want to keep the styling of your Sparklines the same. If you want to use different Sparkline types and styling for each Sparkline, you will need to either create each Sparkline individually or ungroup the Sparkline from the group.

Now that you have created some basic Sparklines, let’s look at how you can “jazz” them up and customise the look of these nifty little critters by checking out how to Customise a Sparkline in Excel.


Leave a Reply

Your email address will not be published. Required fields are marked *

five × 3 =

This site uses Akismet to reduce spam. Learn how your comment data is processed.

.pf-button-img { padding-top: 20px; padding-bottom: 20px; }