During my course this week, I was demonstrating Panel Charts when a delegate asked me to show him how to prepare 2-D Stacked Column Charts with the same data. This post shows you how to do just that!
Where EAT% means earnings after tax margin
Prepare your data by generating a table such as this one:
Select the data, for example, the range C7:E12. I have excluded Gazprom, B7:B12 because its values will skew the graph. The Gazprom data can go on a different graph or accept that it will skew everything!
Create a 2-D Stacked Column Chart and you should see this:
This graph is fine for some purposes but what we want is to have year one at the bottom, year two next … year five at the top. So,
- Right click the graph
- Select Data
- Click Switch Row/Columns
- OK:
I put my legends at the bottom of the graph:
To do that:
- right click the legend
- format legend
- bottom
Make sure you have clicked Show the legend without overlapping the chart.
I don’t like the grid lines either so I right click them to delete them.
Finally, we need to show the years as legend items so that it is obvious what each segment in each bar refers to:
- Right click the chart
- Select Data
- Select series 1 on the left hand side of the dialogue box
- Select the Name and click cell A8 … 2012 … don’t just type 2012 because you will want to use this worksheet as a template in the future so keep it flexible
- Select series 2 on the left hand side of the dialogue box
- Select the Name and click cell A9 … 2011 …
- And so on to
- Select series 5 on the left hand side of the dialogue box
- Select the Name and click cell A10 … 2010 …
- Click OK
That’s it!
Try it Yourself: create the 2-D Stacked Column Chart for EBT% or Net Income%, as you see below; and for variety I have changed a couple of things that you might want to use too:
Download the file from here 2d_stack_col
Duncan Williamson
26th July 2014
Excel with Excel Master 