Excel Graph: how can I show two values in the same bar? Not using stacked?

Question

I've created some data for inventory items over time. Each item has two models (A & B), and the scale is (-) or (+). I've included example images of the type of graph (drawn) I'm aiming for, with an image of the data table it would come from.

The inspiration for the 'look' has come from Jon peltier's site, called Conditional Stacked Clustered Column Chart with Targets. I don't need anything as complicated, but it's turned out to be trickier than I thought.

I've tried overlapping, removing the fill, changing one series into another type, etc. Nothing really worked.

Answer 1

Here's the result I've come up with that doesn't require a lot of messing around to create. Uses the input data provided.

It's not exactly what you're after because Model B isn't just a vertical line, it's an entire outlined bar, but should convey the information in effectively the same manner.

You can get it closer to you're desired output by giving B some error bars (all of zero size) and hiding the bar completely, but unless you do as dkusleika and variant have discussed in terms of mixing graph types and using secondary axis you cannot set the error bars to vertical so they aren't much good.

Instructions (using Excel 2007)

Create a clustered bar chart from the data.

Edit the y-axis; setting the labels to low, and reverse the category order.

Then edit the series Model B, set fill to none and increase the border weight.

On any series (as it changes them all) set the overlap to 100 and gap width to ~50.

Answer 2

Add a fourth column to your data with the values 1-9 corresponding with Items 1..Item 9. Create a stacked bar chart with that data.

Change Model B to XY Chart. For Model B, make the X Values C2:C10 and the Y Value D2:D10.

Hide the markers and add Y Error bars (both) at a fixed value of .25. Format them as thicker and without the wings.

Fix the Y axis scale to 0.5 to 9.5 (this linkes up the secondary axis with the primary). Fix the secondary X axis to -5 to 5.

Finally, download http://www.dailydoseofexcel.com/excel/StackedBarErrors.zip so you can get all the stuff I forgot to tell you.

Note, as variant said, that fixing the axes makes the chart less flexible.

Answer 3

I think the data would be better displayed and understood in a Dot Plot format:

http://peltiertech.com/Excel/Charts/DotPlot.html

http://peltiertech.com/WordPress/use-dot-plots-for-better-categorical-comparisons/