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

Leo

New member
Joined
Jul 10, 2017
Messages
11
Reaction score
0
Points
0
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...

Cheers!
 
Supply a file please!
 
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.
 

Attachments

  • ExcelGuru8041Cluster-Stack-Bar-Chart for multiple measures and years.xlsm
    47.4 KB · Views: 59
Last edited:
Back
Top