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 contained 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 these 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 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

  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 highlight or 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 using the AutoFill tool, see my blog post here.

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. Click here to see how to Customise a Sparkline.

Share on facebook
Facebook
Share on google
Google+
Share on twitter
Twitter
Share on linkedin
LinkedIn
Share on pinterest
Pinterest

Leave a Reply

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

five × 4 =

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