logo
  • Excel Charts
  • About David Aldred & This Blog
  • Consulting & Excel Support

Using Excel Sparkline Charts

July 3, 2013 2 comments Article Easy, Excel

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.

Related

Tags: Charts, Excel, Formatting

2 comments

  • Roofing Repair Arlington May 26, 2014 at 11:56 pm - Reply

    Hello There. I found your blog using msn. This is an extremely well written article.

    I will make sure to bookmark it and return to read
    more of your useful information. Thanks for the post.

    I’ll definitely comeback.

    Here is my web blog: Roofing Repair Arlington

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Showing a Picture Based on the Value of another Cell in Excel
Populate a Dropdown Based on the Value of Another Dropdown in PowerApps
Format or Remove Borders from a Slicer or Timeline in Excel
Using SUMPRODUCT to create a Conditional Weighted Average in Excel
Using the IFS() and SWITCH() Functions to test for more than one condition in Excel 2016
Creating Virtual Tables in Power BI Using DAX

Meta

  • Register
  • Log in
  • Entries RSS
  • Comments RSS
  • WordPress.org

Categories

Pages

  • Excel Charts
  • About David Aldred & This Blog
  • Consulting & Excel Support

Copyright System Secrets 2021 - Theme by ThemeinProgress