Earlier this week I presented the Chart Edition of AccountingWEB’s High Impact Excel webinar series. One of the many topics I covered was the Sparklines feature, which was first introduced in Excel 2010. Several attendees asked “What do I do when the Sparklines feature is greyed out?” One of my favorite truisms is that Excel is fraught with nuance. Indeed, a subtle nuance can disable this feature, which I’ll reveal, along with an overview of Sparklines.
As shown in Figure 1, Sparklines are a means of displaying tiny charts inside worksheet cells. This capability is built into Excel 2010, Excel 2011 for Mac, and Excel 2013. A free add-in offers similar functionality for earlier versions of Excel. Sparklines are helpful in showing trends for numbers, and are often helpful in spreadsheet-based dashboards. Within Excel, dashboards are used to enable users to assimilate a lot of data within a compact space. Sparklines can take three different forms:
- Line: This type of Sparkline appears as a simple line chart within a worksheet cell.
- Column: This type of Sparkline takes the form of a simple bar chart within a worksheet cell.
- Win/Loss: Unlike the Column format that offers a sense of scale, the Win/Loss format simply shows bars above or below the line, so that you can compare positive and negative results at a glance.
Figure 1: Sparklines, which are located in a single worksheet cell, can be in the form of a Line, Column or Win/Loss chart.
To create your first set of Sparklines in Excel:
- Select a range of numbers that you’d like to present in Sparklines form, as shown in Figure 2.
- Choose Insert, and then pick one of the Sparklines options.
- Specify a Location Range within the Create Sparklines dialog box, and then click OK. This range must initially reside on the same worksheet as the Data Range that you selected.
Important: Make sure that your location range comprises a single row or single column, otherwise you may encounter an error prompt. Also, your Data Range should include only numbers. Any text within the range will be treated as zeroes.
Figure 2: Sparklines must be located in a single column or row.
As illustrated in Figure 3, a Design tab will appear when you select any cell that contains Sparklines. This allows you to toggle the type—say, from Line to Column—as well as make other formatting changes to your Sparklines. Although you associate Sparklines with specific worksheet cells, the feature actually hovers above the worksheet cells. This means that you can’t remove Sparklines from a cell by pressing the Delete key.
To remove Sparklines, select one or more cells that contain Sparklines and then:
- Choose the Clear All command on the Home tab.
- Choose the Clear command on the Sparklines Tools Design tab.
- Right-click on a Sparkline and choose Clear Selected Sparklines from the Sparklines menu choice.
Figure 3: Utilize the Design tab to make formatting changes or delete Sparklines.
As I noted earlier, you have to add the Sparklines to the same worksheet as the source data, but you can then cut and paste the Sparklines cells to another worksheet within the workbook.
Now that you have a quick overview of Sparklines, let’s address that pesky problem of the disabled feature, which is illustrated in Figure 4. Even though you’re using Excel 2010 and later, the Sparklines command is not available when the active workbook is saved in the Excel 97-2003 (.xls) format. Using .xls files in Excel 2010 and later can cause other features, such as Slicers, to be disabled, and will cause charts and pivot tables to take on the Excel 2003 look-and-feel. Fortunately there’s an easy fix for this situation.
Carry out these steps in order to use the Sparklines feature:
- Choose File, Save As, specify one of these workbook formats, and then click Save:
- Excel Workbook (.xlsx)
- Excel Macro-Enabled Workbook (.xlsm)
- Excel Binary Workbook (.xlsb)
- Choose File, and then Close.
When you reopen your workbook, the Sparklines feature will be enabled. The major nuance here is that simply carrying out the Save As command won’t enable the Sparklines feature. You must actually close and reopen the workbook for the format change to take effect within Excel.
Figure 4: The Sparklines feature is disabled in workbooks saved in the Excel 97-2003 (.xls) format.
One thing you can’t fix is the fact that Sparklines will not appear in earlier versions of Excel. If you add Sparklines to a workbook that you then share with someone using Excel 2007 and earlier, the cells will simply appear blank.
About the author:
David H. Ringstrom, CPA, heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at[email protected] or follow him on Twitter. David speaks at conferences about Microsoft Excel, teaches webcasts for CPE Link, and writes freelance articles on Excel for AccountingWEB, Going Concern, et.al.