Clustered-Stacked Column Chart with Target Line

In Clustered-Stacked Column Charts I showed how to create a chart that combined clustered columns with stacked columns. It’s basically just a trick using staggered data to make a stacked column chart so that series display columns for only certain categories, and the gaps give the appearance of clusters of stacked columns. Smoke and mirrors, but it’s an effective use of smoke and mirrors.

That’s been my most popular blog post, and probably the most commented. Many of the comments ask about modifications to the technique to achieve different effects, and mostly these are achieved by carefully adjusting the arrangement of the data. One such request was, How do I add a target line that reaches to the edges of the chart?

Clustered-Stacked Column Chart with Target Line

The answer isn’t too complicated. Set up the data as shown below.

Data for Clustered-Stacked Column Chart with Target Line

Using the technique in Clustered-Stacked Column Charts, create a chart using the first five rows of data.

Clustered-Stacked Column Chart with Target Line

Copy the last row of data (including the label in the first column), select the chart, and use Paste Special to add the data as a new series. A new column series will appear stacked on the rest.

Clustered-Stacked Column Chart with Target Line

Right click the new series, choose Chart Type, and select the line without markers option. Excel helpfully adjusts the secondary category axis, making the labels visible and making the value axis cross between categories.

Clustered-Stacked Column Chart with Target Line

No matter, we fixed this once before during the protocol, we can fix it again. Format the secondary category axis so it again shows no category labels, and so the axis crosses at the categories, not between them.

Clustered-Stacked Column Chart with Target Line

Peltier Tech Chart Utility

Comments

  1. Thanks Jon, excellent addition to clustered-charts and just what I needed ;-)… I wanted to mention (I’m on 2003), my drama was getting the lines to the edges. I had already removed the Pri & Secondary Category labels from the chart (they’re not required), so I found it difficult to get to the “Value (Y) axis crosses between categories” option in the Format Axis window. Also, with only the Secondary Categories (and not Primary) activated, the “…crosses between categories” option wasn’t available anyway. Maybe these items are accessible via other menus, but when ‘playing’ that was my experience, and added to some confusion in the last step. Sorted now. Cheers, will definitely be buying you a coffee!! :-)

  2. Aaron Warnecke says:

    Jon – This method and adding a horizontal line to a column chart are GREAT methods, but I can’t get them to work for my purposes. I’m trying something similar, adding a VERTICAL line to a HORIZONTAL cluster-stacked chart (rows, not columns), and I can’t get it to work! You’d think it’d be similar, but for some reason it isn’t. I was successful in adding a horizontal line to a vertical cluster chart per your methods, but I don’t seem to be excel-handy enough to convert that to a horizontal bar-style cluster chart – the line simply remains horizontal with the rest of the bars! Can you help me out?

Subscribe without commenting

Write a Comment

I welcome comments from my readers. If you have an opinion on this post, if you have a question, or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion. If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.

Subscribe without commenting

Peltier Tech Chart Utility

 

Create Excel dashboards quickly with Plug-N-Play reports.

 

Site by: Dawud Miracle, Business Coach & WordPress Websites