Using Excel Sparkline Charts

Sorry for yet another Excel post – what can I say? I like Excel!

Another feature introduced with Excel 2010, is Sparklines / Spark Charts

There are tiny charts that sit inside a cell, and used to show trend data for many areas at once. They’re simple to use, and helps visualize your trend data.

So how do you use them? Well, you’ll need some trend-based data – eg sales by week.

For my example, I’m using data shown by Region, by Month – for one year.

Then, select the cell where you want your SparkLine to go. From the Ribbon, select ‘Insert‘ and pick ‘Line‘ (we’ll cover the others later).

You’ll be prompted to enter the range where your data is held. Select your data range. (You should be able to leave Location Range as-is). Click ‘OK’

And you should see your first Spark Chart!

You can then drag this down your range…

The great thing about Spark Charts, is they always adjust to your cells. Resize your columns, and the charts will resize with them.

When you select your Spark Charts, you will see a blue box around the range. This is Excel grouping your charts, so if you change the format / behavior if one, they’ll all change.

If your grouping breaks (perhaps you added new rows), then highlight the entire range, select the ‘Design‘ option from the Ribbon and select ‘Group‘

There are a whole host of settings and options for Spark Charts – having a look around the ‘Design’ toolbar when you select your Spark Charts.

One of the most important (I think) yet hidden away options, is to allow the spark chart to include hidden columns. If you don’t enable this option, and then hide your data, your charts disappear.

From the Spark Chart Design toolbar, select the drop-down below ‘Edit Data’, and select ‘Hidden & Empty Cell…’

Tick the box that says ‘Show data in hidden rows and columns’ and hit ‘OK’

You can now hide your data without losing your Spark Chart.

Another option I like is that it can add markers to high / low points, or negatives. I’m going to show this using some Growth % data I have added for the same dataset.

Create your Spark Charts as before. From the Design toolbar, tick the ‘Negative Points’ tick box.

This adds a red marker (you can change the colour) to the Spark Chart – a helpful way to see which months had negative growth.

As you will have probably noted, there are a couple of other types of Spark Charts you can create.

Columns creates column charts in your cell…

And Win/Lose just adds a simple +/- tick. I don’t think this one as useful, but I’m sure there are those of you out there that would – and they work better in a more compact environment.

There you have it – Spark Charts. Simple. Effective.

2 comments