Making a "stacked" funnel chart in Excel?

Question

I'm trying to graphically represent some data and I'm not sure how to do it or if Excel has the capacity to do it.

There are three centers, and teams from each center apply for an award, and there are several rounds of competition before the award is given out. Each bar represents a round of competition, and the numbers represent how many teams have made it that far. Segmenting the bars shows how many teams from each center have made it to that round.

This is what I want it to look like - I mocked this up in Paint but I'd like to be able to update it with live data:

I can make a funnel with the total numbers, but what I really want to do is make a funnel that divides each bar into sections to represent each center. Any ideas?

Answer 1

Is this what you want?

I set out the data as below:

then plotted a stacked bar chart from it.

I switched Rows/Columns, reversed the order for the vertical axis and deleted it, limited the range for the horizontal axis up to 700, set No fill and No Line for the right-hand parts of each bar and the left-hand parts of each bar other than the top one. Added and formatted data labels and adjusted the colour scheme.

The formula in A2 and copied down:

=(E$1-E2)/2

is to centralise the rest of the output and for it, and the numbers in black, the totals in ColumnE, eg:

=SUM(B1:D1)

were calculated.

The result is dynamic.