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
Three 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
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 !