Custom Stacked Bar Chart in Shape of Pyramid

ButterflyInReverse

New Member
Joined
Apr 16, 2009
Messages
3
Greetings!

I am trying to create a chart in the shape of a pyramid with 10 stacked sections to the pyramid. These 10 sections will be equally distributed as far as height of each section and the height will not need to change with the data.

Within each section of the pyramid, I need to be able to chart 6 different data points. The data points are percentages, and each of the 10 sections will individually add up to 100%.

Is there a way to use the stacked bar (or the 100% stacked bar) to do this? :confused:

(I've tried to convince my boss that going with some sort of doughnut chart would be much easier, but he's set on the pyramid look.)

Any help would be greatly appreciated.
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
Hi BIR, welcome to the board.

I don't understand exactly what you want - how does the 6 data points per section work ?

You could possibly create a pyramid using a horizontal bar chart. But I'm struggling to visualise what you have in mind.
 

ButterflyInReverse

New Member
Joined
Apr 16, 2009
Messages
3
Thanks for the reply and welcome, Gerald.

I guess I could have explained better. A good way to illustrate what I'm trying to do might be to reference the food pyramid. As you can see, the two middle layers of that pyramid are divided. That's what I'm trying to do... divide each of the 10 layers in my pyramid based on six data points that will equal 100% in each layer. The divisions should represent the percentages of the data points.

(I do not need or want my pyramid to be 3-D, as the referenced food pyramid is.)

Does that make it any clearer?
 

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
OK BIR, thanks, that helps.

You could do something like this using a horizontal stacked bar.
This might not be the best way of doing it, but you could do it.
It would produce a stepped pyramid, not a smooth pyramid.

Let's say your date looks like this -
Bottom row
25%
25%
25%
25%

Next row
33%
67%

I'll ignore the other rows - if you follow the logic you'll be able to make them work yourself.

You then need to work out exactly how wide you want each layer to be, in some kind of arbitrary unit.
Let's say you want the bottom row to be 10 units wide, and the next row up to be 8 units wide.
You will need to convert your percentages into values as proportions of these numbers, and set them up as a table, something like this.

.................Row 1........Row 2
Value 1........2.5............2.67
Value 2........2.5............5.33
Value 3........2.5..................
Value 4........2.5..................

The 2.67 comes from 33% x 8.

You will ALSO need to introduce a value to offset each row from the left hand side.
So, if you want to offset the bottom row by 1 unit from the left, the next row would be 2 units from the left, and so on, so your table would now look like this

.....................Row 1........Row 2
Offset Value.....1...............2
Value 1............2.5............2.67
Value 2............2.5............5.33
Value 3............2.5..................
Value 4............2.5..................

Then set up your chart. The row that I have called "Offset Value" would be your first data series. The row that I have called "Value 1" would be your second data series, and so on.

Finally, format the data series so that the first one containing the Offset Values is invisible, i.e. has no fill colour and no border line.

The result should be a stepped pyramid.

Post back if you're not clear about this.
 

ButterflyInReverse

New Member
Joined
Apr 16, 2009
Messages
3
Thank you, Gerald! I think will be well-received! I appreciate this more than I can tell you! :cool:

PaddyD, thanks to you, too for the suggestions. I had found those sites, but couldn't find anything there that would work. I do appreciate your ideas!
 

Watch MrExcel Video

Forum statistics

Threads
1,133,148
Messages
5,657,111
Members
418,355
Latest member
michaelirl

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top