View Full Version : Cluster-Stack-Bar-Chart for multiple measures and years (more than 2 axis)

2017-07-10, 11:49 PM
Hi all,

I've got a list of 100+ buildings and their annual GHG emissions from 2011-2015. I want to display the annual GHG emission breakdown (gas, electricity, etc.) for the 5 years and all buildings together in one chart. The emissions have to be stacked per year so that they add up to the total annual emission. Accordingly, building 1 would have 5 stacked bars (one for each year from 2011-2015) right next to each other, followed by building 2 (again, 5 stacked bars), etc.

I get the secondary axis thing but it only allows me to display 2 years in one and the same chart. Hope someone can come up with a solution...


2017-07-12, 03:40 PM
Supply a file please!

2017-07-12, 06:30 PM

2017-07-13, 12:23 AM
In the attached:

A re-arrangement of your source data starting in A19 (done by a macro called blah).
A pivot table and associated chart from new table

Note that

the pivot/chart is filtered for only a few buildings (adjust this at will yourself in the pivot or chart),
and I've added a few water values so that they show up on the chart.
If you change any value(s) in the source data yourself, you'll need to refresh the pivot/chart (right-click one of them and choose Refresh) .

I've also made a duplicate table based on links to the top table starting at cell A239 (using macro blah2); if you use this as the source data for the pivot/chart, then when you adjust values in the topmost original table, the links will update automatically then a manual refresh of the pivot chart will update things accordingly.

You don't need to enable macros for the file to work.