Sparklines in Excel – New Features in Excel 2010 Series

Excel 2010 introduces a new feature called sparklines. Sparklines are essentially miniature charts showing only the plot and pretty much nothing else (though you can turn on chart axis if you want). The advantages are obvious. In the same amount of space that it to present a normal excel chart with a handful of data points, you can pack a dozen or more sparkline charts. If used intelligently, the data loss due to absence of reference lines and chart labels can be handsomely offset by the ability to pack in a different ‘views’ of the information in the same space.


Types of Sparklines

line-column-win-loss-types-of-sparklinesThree types of sparklines has been introduced – Line, Column and Win Loss. The win loss sparkline essentially is a boolean representation of the data – all the data points are shown with the same magnitude with negative data points extending down from the axis while the positive ones shoot upwards.

Create a Single Sparkline

Click on the ‘Insert’ tab from the ribbon and choose and of the sparkline icons. In the options box that appears enter the range where the data is located and where you would like to place the sparkline. Click ok and you’re done !


Create Hundreds of Sparklines

Why stop a good thing 😉 The best part is that it takes about the same time to create a few thousand sparklines in Excel as it takes to create one. So now a dashboard with hundreds of charts can be created in minutes.

The easiest way to create multiple sparklines in Excel is to simply select the entire data range (you can select as many columns and rows as you want) and then click the sparkline button from the ‘insert’ tab.

Copy Paste Sparklines to other Cells or Range

Another interesting aspect is that sparklines are dynamic. Which means that you can copy and paste them just like ones does for a cell or a range. Similar to when you copy a cell or a range, the relative references in a sparkline also change thus picking up new (relative) data points as you paste them elsewhere. This makes life so much easier when copying across rows or columns – don’t you think so? You can, like most other things in excel, copy and past them as an image.


Color Sparklines at will and Highlight Negative Points

Sparklines can be given custom colors of their own. Just select the color palette from the ‘Design’ tab and color them whichever way you want.


Mark the High, Low, First and Last Points on the Sparkline

sparkline-markerWhen you are working with sparklines, the sky is the limit :-8 You can:

1. Highlight negative points using a different color.
2. Highlight the highest and the lowest point in the entire chart
3. Highlight the first and last points in the chart
4. Shade the markers differently from the lines (in case of a line chart)

Just select the ‘Marker Color’ from the ‘design’ tab and then pick whichever option you would like to use.

Sparklines – Other features

Still looking for more … okay here goes:
1. Ability to turn on (and off) the chart axis.
2. Being able to plot the data in the reverse direction (right to left)
3. Customizing the maximum and minimum values on the vertical axis of the chart
4. Ability the change the chart axis from General to the one based on dates

Want to see sparklines in action. Download the sparkline dashboard here or click on the button below:


In the upcoming posts we will cover some of the other new features in Excel 2010 and how to make them work for us. Keep reading !

Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Comments and Trackbacks

  1. Chandoo wrote:

    Very good round up Ajay. I have installed the beta yesterday and have been playing around alot. I like the sparklines features, they open numerous possibilities for heavy excel users.

  2. Ajay wrote:

    @Chandoo. Yeah I fully agree. Imagine the reduction in time and effort sparklines offers in making a dashboard with a few dozen charts. Definitely a welcome addition to the charting fraternity :-)

  3. Andreas wrote:

    For sparklines that work with older Excel version (Excel 2000, XP, 2003 and 2007) have a look on MicroCharts


  4. kapiti wrote:

    good stuff … but how do you copy a sparkline and paste as an image?

  1. Excel 2010 - Beta Review | Excel & VBA - da Tab Is On wrote:

    […] useful addition is the sparklines. Sparklines are minature charts that fit inside a cell. Sparklines are fun to make and equally useful when you […]

  2. Volatility Chart in Excel - Lessons in Chart Design From NY Times | Excel & VBA - da Tab Is On wrote:

    […] and readibility. Most of the charts that you see in the corporate world are not exactly as small as sparklines. This means that a typical chart will have a lot of space that can be used for other things – such […]


Keep up with the latest stories - Delivered right to your inbox


English flagItalian flagKorean flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagRussian flagDutch flagHindi flagFilipino flagIndonesian flagThai flagTurkish flagPersian flag
treeemap software for excel