What is a “Container Chart”? - EBOOK VBA EXCEL


EVBA.info ( Ebook VBA Excel ) Welcome EVBA.info - Nothing Is Unable ... About Excel Tricks, Learning VBA Programming, Dedicated Software, Free Courses, Living Skills ...

Monday, November 4, 2019

What is a “Container Chart”?

What is a “Container Chart”?

Here’s a screenshot of part of the chart I prepared. 
Excel Tips - Column Chart1.png

Don’t miss the entries from other participants as you will learn a lot of Excel techniques from them.
What you see above is actually a Panel Chart… meaning more than one charts are putting together intentionally to visualise the data of interest.  The upper part (showing the yearly profit) of the chart is a simple clustered column chart; the bottom part (showing the yearly costs with four components) may be something of your interest to know about, right?
It is not difficult at all to create.  Let’s do it step by step:

Prepare your data –> Make sure you have the “Total” dataExcel Tips - Data for Chart.png

Tip: Input the years as text, i.e. start with an apostrophe ‘2011 so that Excel won’t misunderstand them as data to plot.

Select the range of data –> Insert –> Column –> Clustered ColumnScreen Shot 2016-10-01 at 1.35.06 PM.png

Up to this point, you should see a chart like below:

Excel Tips - Column chart.png
If you see something like the following instead,Screen Shot 2016-10-01 at 2.50.44 PM.png

you may need to go “select data” and then “Switch Row/Column”Excel Tips - Swith Row_Column.png

simply click the  Excel Tips - Switch plot.png under “Chart” on the ribbon.
Now let’s do a little make up to the chart by changing the rainbow into themed color with different intensity.  Using Chart Style is a quick and easy way to do it.

Select the chart –> Go to Chart Style –> Select the style you like

Excel Tips - Chart Styles.png

In my example, I used the rightmost one on the first row.  And now you should be able to get a chart like this:
Excel Tips - Clustered columns 1.png

Then you are ready to make the “Container Chart”…

Select the “Total” series by right-clicking any of the “Total” columns –> Format Data Series…

Excel Tips - Clustered columns 2.png

Make it the “Secondary axis”

Excel Tips - Clustered columns 3.png

Set the Gap width: 10 (more or less) according to your own preference

Excel Tips - Clustered columns 4.png

Set the Fill Color:  “No Fill” in order to make it transparent

Excel Tips - Clustered columns 5.png

Set the Line Color accordingly.  You may also set the weights of the line

Excel Tips - Clustered columns 6.png
Now your chart should look like:
Excel Tips - Clustered columns 7.png

Further tidy up is required…

Delete the “Secondary Axis” so that all columns are of the same scale

Excel Tips - Clustered columns 8.png

Delete also the “Primary Axis” to make it less cluster (as we will add data label later)

Excel Tips - Clustered columns 9.png

Delete the gridline to make the chart more “clean”

Excel Tips - Clustered columns 10.png

Move the Legend to the bottom

Excel Tips - Clustered columns 12.png

Add Data Labels to each series by right-clicking the series

Excel Tips - Clustered columns 13.png
Tip: It’s a bit tricky to select the Series “A” to “D” as you can hardly click on them.  To tackle that,  select the series from the ribbon instead.  Look for the following dropdown that appears on the left of the ribbon under “Chart Layout”.
Excel Tips - Clustered columns 14.png

Once you are done with all the series, you should get the following:Excel Tips - Clustered columns 15.png

To make the same chart for different companies, just copy the chart, change the data sources and color theme accordingly.
As simple as that.
How do I come up with such chart?  To me, the chart is a combination of Stacked Column and Cluster Column.  So let’s take a look how it would look like if we plot the data into a Stacked Column and Clustered Column respectively.
Stacked Column
Excel Tips - Stacked Column.png
  • Pros: Total trend is clearly presented
  • Cons: Trend of individual components are not clear except the first one because they are not sitting on the same base.
Clustered Column
Excel Tips - clusted columns.png
  • Pros: Trends of individual components are clearly presented
  • Cons: Difficult to see the trend of total
Apparently, they supplement each other.  Then why not combining them together into one?
Excel Tips - Clustered columns 15.png

I have never thought about naming this chart.
How would you name this type of chart?  Please leave your suggestion in comments.
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1

No comments:

Post a Comment