Gain/Loss Population Stacked Bars

[Matthew Edminster]

Hello,

I am trying to create a chart showing changes in a population over time. The chart is based on a table of additive and subtractive "population events" that looks something like this:

Name      Date       Event
Jim       2014       Joined
Nat       2014       Joined
Bob       2015       Joined
Joe       2015       Joined
Liz       2015       Left
Amy       2015       Transferred

The goal is to create a stacked bar chart that shows a) both positive and negative values and b) if possible, also indicates the net positive or negative population change.

A) would be relatively simple if I could figure out how to return a negative count in my pivot table for subtractive events like "left" and "transferred". But I can't seem to find a way to display the "Event" field twice (once for positive events and again for negative) in the pivot table columns.

B) is a bit more complicated. The idea would be to add the additive and subtractive counts for a given year and adjust the total height of the bars accordingly. I have no idea how to go about doing this.

Any help would be very appreciated!

Matt

 

[Luke M]

Hi Matthew, and welcome to the forum!

I would suggest the use of a waterfall chart, where you start with a certain height column bar, the initial value, and then go up/down from there. We can mimic this in XL using a stacked column chart. To get your additional request of net, I would propose using a line series.

Note that I added several rows of data to create more data points.


We started with initial population of 10. In 2014, we had 4 adds and 1 minus. Net puts us at 13. In 2015, not that the dividing line between green/red is the net of the previous year. In 2016, we were previously at 12, add 1, so new net is 13. Next year we lost 5, gain 3, so new net is 11, and so on.

 

[Matthew Edminster]

This is great! Thank you so much.