Blog Archive

Creating Spark-Line in MS Excel

What is a sparkline?

A Sparkline is a small chart that is aligned with rows of some tabular data and usually shows trend information.
Here is an example of sparklines in a project team status report.
Example Sparkline Implementation - Project Team Member - Status Report

How to create sparklines in Excel 2010?

Creating sparklines in excel 2010 is very easy. You follow 3 very simple steps to get beautiful sparklines in an instant.
  1. Select the data from which you want to make a sparkline.
  2. Go to Insert > Sparkline and select the type of sparkline (you have 3 options – line, column and win-loss chart)
  3. Specify a target cell where you want the sparkline to be placed
  4. Optional: Format the sparkline if you want.
Here is a short screen-cast showing you how a sparkline is created.
How to create sparklines in Excel 2010 - Tutorial

Types of Sparklines in Excel 2010:

Types of Sparklines in Excel 2010
There are 3 basic types of sparklines in Excel 2010. They are,
  1. Line chart
  2. Column chart
  3. Win-loss chart (useful for showing a bunch of wins & losses denoted by 1s and -1s)

Sparkline Formatting and Options – Explored

Sparkline Formatting Options in Excel 2010
In excel 2010, you will find a new ribbon called as “Sparklines – Design” ribbon. This is where all the formatting options for sparklines are included. Some of the key formatting / customizations you can do are,
  • Change the sparkline type
  • Change the source data / target cells of sparkline
  • Set different colors for first point, last point, highest & lowest points (applicable for column and line chart types)
  • Set axis options (show / hide axis, set min and max value for vertical axis, set axis type to date axis etc.)
  • Group / un-group a bunch of sparklines (you can change formatting options, axis settings en-masse when you group sparklines)
  • Remove sparklines

Sparklines & Missing Data – How does it work?

Sparklines & Missing Data - Examples
  • If the sparkline source data contains non-numeric data, they are neglected while plotting the sparklines.
  • If data has some #NA values, they are neglected
  • If data has blanks, sparkline shows blanks too
  • If data has zeros, zero value is plotted
  • If data has some hidden rows / columns, the values are neglected (unless you enable “Show data in hidden cells” option)

Sparklines in Tables & Pivot Tables

You can add sparklines to tables and pivot tables too. Adding them to pivot tables is a bit tricky (I will write about that when I master the trick) but adding sparklines to tables is fairly straightforward and scales nicely.
Sparklines in Pivot Tables - An Example

Sparkline Tips & Tricks

Here is a bunch of quick tips & tricks for those of you starting on sparklines.
  • You can auto-fill sparklines. Select the first set of values and add a sparkline. Now copy and past sparklines to auto-fill them based on data in adjacent cells.
  • When you adjust row-height or column-width of the cell containing sparkline, the size of sparkline changes too.
  • Juxtapose sparklines with conditional formatting icons to create stunning charts and dashboards.
  • If you want to copy a sparkline over to a ppt or document, you can use “copy as picture” option.
  • The sparklines feature is disabled whenever you open a workbook in “compatibility mode”.