I can hear you asking it now, what is a Sparkline? Very good question, and today I’m going to give you the answer and show you how to incorporate these nifty little critters into your own Excel worksheets.
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.
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.
To create a sparkline, follow these steps:
- Open Microsoft Excel
- 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
- Place your cursor in the cell, where you wish to insert the Sparkline
- Click the Insert tab from the Ribbon
- From the Sparklines group click Line
- The Create Sparklines dialog box will appear
- You will see the Location Range has been filled in as the cell you had selected. This will be the location the sparkline will be placed
- You will now need to highlight or select the Data Range you wish to include in your Sparkline
- Use your mouse and highlight the data using the left mouse button
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.
- Once you have completed this step click OK
- The Sparkline will now be displayed in the cell
- 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.
See my next post on how to customise your Sparkline, change sparkline types and delete sparklines from your worksheet.