Sparklines in Excel 2010

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

A strange name but Sparklines is a useful new part of Excel 2010

Mea Culpa – we thought that ‘Sparklines’ was a fancy name made up by Microsoft after a long lunch … Office Watch readers, John F, Nick M and Peter B all pointed us to the facts.  Sparklines were named by Edward Tufte for “small, high resolution graphics embedded in a context of words, numbers, images“.  It was our mistake, sorry Mr Tufte. 

We were mislead partly because Microsoft hasn’t acknowledged Mr Tufte’s contribution to their new feature.  When you see Microsoft boasting about sparklines in Excel 2010, spare a thought for the people who came up with the idea.  Microsoft now has a patent over their implementation of sparklines in Excel 2010.

Whatever it’s called, Sparklines is actually a useful new feature than even beginners can use to jazz up their worksheets. 

Sparklines are simply tiny charts that show up within a cell in the Excel 2010 worksheet. These miniature charts represent changes for a particular row or column of entries.

If you ever have been scared off from the complications of formatting an Excel chart, especially for a simple need, then Sparklines is a good alternative.

How to Create Sparklines

To create Sparklines you need some kind of data report or a set of metrics so that you can insert Sparklines for the data in the required cell.

We’ll use the Excel sample template, sales report as an example to show the process of inserting Sparklines.

Click File | New | Sample templates | Sales Report and press the “Create” icon.

1369 Create new report - Sparklines in Excel 2010

The sample sales report will open. In the report you can add Sparklines to all the rows of data or select a set of rows or even add it to just a single row of data. I will select the first 5 rows of data to add the Sparklines.

Once you select the data click Insert | Sparklines. As shown in the below screenshot you can add three types of Sparklines which are Lines, Column and Win/Loss. As shown in the icons for each type, different styles of charts will be inserted depending on the need.

1369 Create sparkline - Sparklines in Excel 2010

Once you select the type of Sparkline as shown in the below screenshot “Create Sparklines” popup window will open where you need to enter the Data range i.e. the cell number for which the corresponding chart should be created and the Location range i.e. the cell in which you want the chart to be inserted. You can either insert the chart in a new cell or in the same cell where the data is appearing. If you select the latter then the chart will appear as a background in the cell. Let us see both these options. Now as shown in the below screenshot the data range is already selected. You can deselect the earlier selection and make new selection for the data range. For the Location range I chose a set of empty cells beside the “Qtr 4” column. After making the selections click the “OK” button. Note here that by clicking the small red arrow icons (see below screenshot) next to the textboxes for Data range and Location range the window will collapse and expand to show just a single corresponding textbox.

1369 Add cell details - Sparklines in Excel 2010

As you can see in the below screenshot tiny charts representing the data variations in each row is inserted in the empty cell.

1369 New sparkline - Sparklines in Excel 2010

Customizing Sparklines

Once you insert Sparklines you can customize it in terms of color, style, etc. An array of Sparkline tools is available on the ribbon (see above screenshot) to change the color, mark high, low, negative points in the chart and hide or show those points in the Sparklines.

Note here that editing individual Sparkline in a group will apply the changes to all the charts in that group. However there is a workaround for that which I will explain shortly. To change the color or style of a chart select that particular Sparkline, automatically the Sparkline Tools ribbon will be displayed. Here you can select the required type, style, choose Sparkline and marker color and show or hide the marker points. I’ve selected the High Point and Low Point marker to be shown. Accordingly all the charts are displaying these marker points (see below screenshot). Suppose you decide not to show any markers then you will have just plain Line charts.

1369 Edit sparkline - Sparklines in Excel 2010

See the below screenshot wherein for the example I’ve selected the Sparkline type as “Column” and used blue color for high points and red color for the Sparklines. Note here that although I selected only one row of data to apply the changes all the rows got affected. Next I inserted a separate Line Sparkline for a single row of data. Also for one of the rows of data I’ve inserted a Line Sparkline in the column “Qtr 4” itself. As a result this is inserted as background in that particular cell.

1369 Adding marker to charts - Sparklines in Excel 2010

Sparkline Groups

When you create multiple Sparklines at once they are automatically put into a group.  The formatting and settings of the Sparklines will be consistent unless you break the grouping.

You can see the grouping in the worksheet itself.  When you click on a grouped sparkline, a thin blue line appears around all the sparkines in the group.

In this example the middle cell/sparkline is selected with the blue line around all three graphs in the group.

1369 excel 2010 sparkline group - Sparklines in Excel 2010

Breaking the Group

There is a simple option to break a group of Sparklines. For this select the required Sparkline, under Sparkline Tools | Design | Group click Ungroup. That particular cell will be ungrouped from the group. If you want to break the entire group, then select all the cells and click on Ungroup. If you see in the screenshot below after ungrouping the first cell I’ve changed the column color of that particular Sparkline to yellow not disturbing the remaining Sparklines. Again if you wish to group a set of individual Sparklines then simply select all of them and under Sparkline Tools | Design | Group click Group.

1369 Ungroup sparklines - Sparklines in Excel 2010

Deleting Sparklines

To delete a particular Sparkline or a group of Sparklines select the required Sparkline, under Sparkline Tools | Design | Group click Clear and choose the required option. That particular Sparkline or Sparklines will be cleared.

1369 Clear sparkline - Sparklines in Excel 2010

subs profile e1563205311409 - Sparklines in Excel 2010
Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address