2-D 3 layered bar graph

Paste this data into a blank worksheet:



The data in RED are the heights of your bars. The rest of the formulas re-adjust. Select the whole table from B:E and make a non-stacked Area graph. You should see 9 flat-topped pyramids. Now right-click the chart, go to "Chart Options" -> "Axes" -> Category X-Axis = Time-scale

Now click [OK] and you should have 3 bars with the 'back bar' visible behind the 'middle bar' visible behind the 'front bar'.

Adding to H2 makes the difference in column widths bigger (if you set it at 1, the front bar is 1 unit smaller than the middle bar which is 1 unit smaller than the back bar).

Adding to H3 makes the gap between series bigger (if you set it at 100, the gaps between the first set of bars and the second will be 100 units apart).

Adding to H4 makes the smallest column wider (if you set it to 1, it is only 1 unit wide).

That should explain the methodology for creating it. To add more series you can just copy rows down. To add more series you'll have to diddle with the formulas and labels in columns A/B. In general, it goes:

new group
pad -> series label -> pad
middle
pad -> series label -> pad
new group
...

You can add multiple series and the like if you'd like.

There are many other ways of doing this if you use VBA. But this will do what you want without it, if you can figure out the logic and don't mind diddling around 'til you get something you like.

Whoah! Thanks so much, Sal. It'll take me a little bit to understand the logic, but I was able to make the graph work.

Okay, here's the long and the short of it. When you switch to a Time-scale axis, you get to give exact X,Y coordinates which will be plotted in the order you give them.

So basically, you want to draw an outline of the shape you'd like.

In this case, you want bar graphs, so you're just drawing rectangles on an X-Y chalkboard. So you want to go right across the axis until you get to where to start the first series, go up at that point until you get to the right height, go right until you get to the proper width, and head down until you get back to the axis.

Simple example:



Plot this and you get a nice pyramid. Change it to time-scale and you get an awesome rectangle like you want. See how the plotted points go (x-y)?

0-0 to 1-0 to 1-5 to 2-5 to 2-0 to 3-0

So 1 square right, 5 squares up, 1 square right, 5 squares down, 1 square right

In order to do that properly for lots of series, you need to plot a silly-big amount of points. Hence the massive data table. Basically, each point you have (and you need 4-6 points per rectangle) needs to have 3 different X values. As you add more series, you need even more points. And more points. And more points.

Since you can only have a primary and secondary axis, that means you can really only use 2 different sets of X values. That means with more than 2 series, you need to combine all your X values into a nice table like I had, where you have tons of repeating values that aren't necessary for several of the series. It's a pain, but it gets the job done.

Once you get the hang of it, it isn't difficult at all. Just time-consuming.

Hi

Jon Peltier explains here how to have columns of variable width. To get the effect you want, superimpose them using overlap.

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

Man that's a good trick. Much easier than mine too.

The custom fill technique was what I suggest for his post on excelforum
http://www.excelforum.com/excel-charting/679797-2-d-3-layer-bar-graph.html

Thank you for the information, Andy.

tokyods

In the future if you cross-post, write it in your post, as well as the corresponding cross-post address.

If you do not do this, you may have people spending their time trying to help you when the problem is already solved. This is unfriendly and leaves a bad impression.