<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Stacked Column and Combination Charts with Excel 2010 Sparklines

Published on
25,173 Points
13,973 Views
17 Endorsements
Last Modified:
Awarded
Editor's Choice
Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Love data viz with Excel and Power BI. Power Query rocks. Have you tried it?
Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards.

Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a way to create stacked column charts or combination charts with columns overlaid by a line.

But with a bit of preparation and few tools from the trick box, it can be done.

Stacked Column Sparkline


1. Create Sparklines

Start out with two Sparklines, Blue data and Red data. The source data in this example is arranged in a table with two columns, Blue and Red. The goal is to create a Sparkline that stacks the blue columns onto the red ones. A set of standard Sparklines

2. Create a Helper Sparkline

To achieve that, first go to the data table and add a helper column that sums the values of Blue and Red. Then create a Helper Sparkline which shows the newly summed data. It looks like this: Helper Sparkline

3. Align Y Axes

We need to ensure that all three Sparklines have the same Y axis settings. Select all three Sparklines, click the Design tab of the Sparkline Tools and select the Axis drop-down. Set Sparkline Axes optionsSet the Vertical Axis Minimum Value to a Custom Value (in the example it is 0 ) and the Vertical Axis Maximum to a Custom Value (in the example it is 10).

4. Create a dynamic image of the Sparkline

Next, select the cell with the Helper Sparkline and use the Copy drop-down on the Home tab and copy the cell as a picture, accepting the default in the Copy Picture dialog. Copy As Picture Accept the Defaults

5. Paste and link

Paste the picture into a cell. This is a static picture and will not update when the Sparkline changes. To make it dynamic, select the picture, then click into the Formula bar, enter a = sign and click on the cell that has the Helper Sparkline. Paste and Link to the cellNow the picture is dynamic and will show whatever content is in that cell. Note that the picture has a transparent background (you can see the Excel grid in the background). This is because the source cell has no color fill.

6. Copy second Sparkline

Repeat the Copy Picture process for the Red Sparkline, paste it and link it to the Red Sparkline cell. The transparent picture background is essential for the next step to work, so make sure not to use fill colors in the Red Sparkline cell.

7. Drag to overlay

Now drag the two pictures on top of each other. The Blue one is in the background and the Red one is in front. Since the background of the Red picture is also transparent, the taller blue columns will peek out above the red columns, thus effectively creating a Stacked Column Sparkline. Stacked Column Sparkline

Combination Column / Line Chart


The steps are the same as above, but this time the front picture has been taken from a Line Sparkline. Ragged-looking Line Sparkline picture
[step="" title="Tip"]If the line in the image looks a bit ragged, open the Format Picture dialog and on the Size tab adjust the Height and/or Width by a percentage point. In the example, I changed the Height from 104% to 105% and the ragged line in this screenshot was restored.

Here’s the Combination Column / Line Chart:
 Column / Line Combination Sparkline[/step]

Some hints and tips


The biggest challenge is probably to align the two images perfectly. If you hold the Alt key while dragging a picture, it will snap to the nearest cell border. First drag and snap the background picture to the upper left corner of a cell, then drag and snap the foreground picture to the same position and they should be perfectly aligned.

Why not simply overlay the Helper data Sparkline with the picture of the foreground Sparkline?

Well, the picture of the Sparkline is not exactly the same size as the cell. It is a few pixels off, enough to be irritating and a pain to resize manually. It’s easier and faster to create two pictures, since they will have exactly the same size, and will align perfectly when placed on top of one another.

Here is the finished Mini-Dashboard. The attached file was created using the steps outlined above. Some of the source data is generated with Randbetween(). Open the file and hit the F9 key to see the Sparkline images update instantly. Enjoy.

 Mini Dashboard with stacked column and combination Sparklinessparkline-combination.xlsx
17
2 Comments
LVL 42

Expert Comment

by:dlmille
Very Cool.  Can't wait to get Excel 2010 at work, and install it at home.  Interestingly enough, I just learned about sparklines for a recent question I answered, where we were using Excel 2007 and I found some code that generates sparklines in that version.  Here's the link to Gerard's library for 2003-2007 users:  http://www.spreadsheetml.com/products.html

Dave
0
LVL 50

Expert Comment

by:Dave
Great article Ingeborg.

I remember liking this when I first read it, and thinking one day this would be useful. Well I have finally clicked that sparklines are an easy method to provide immediate feedback to financial modellers on input assumptions trends - not just dashboard outputs.  They can be added to every row in a financial model at the far left, just before freezing panes, so both row totals/weighted averages and sparkline summaries are always visible. Possible uses including

- quickly showing where there is missing data (which may occur off to the right of the immediate view in big financial models)
- identifying possible errors in data entry or calculations
- reveal trends which may otherwise remain hidden

Cheers

Dave
0

Featured Post

JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month