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.
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

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,096,293
Messages
5,449,499
Members
405,569
Latest member
deanro

This Week's Hot Topics

Top