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!

2dstack1

Where EAT% means earnings after tax margin

Prepare your data by generating a table such as this one:

2dstack2

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:

2dstack3

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:

2dstack4

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:

2dstack1

  • 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:

2dstack5

 

2dstack6

Download the file from here 2d_stack_col

Duncan Williamson

26th July 2014

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: