ICAEW.com works better with JavaScript enabled.
Exclusive

Exploring Charts (Graphs) in Excel - Part 5: Column/Bar Charts, Clustered, Stacked and 100%

Author: John Tennent

Published: 11 Feb 2021

Exclusive content
Access to our exclusive resources is for specific groups of subscribers.

So far in the Blog series we have focused on creating Line charts and exploring some of the features and options available to display the underlying data in the most appropriate manner. In this Blog we will look at alternative chart types. Helpfully many of the features and options (such as titles, axis and bounds) are the same as those for a Line chart.

Column or Bar charts

These charts are essentially the same with Column being vertical and Bar being horizontal. They are best used for discrete or categorical data rather than continuous. For example a sample of people asked their favourite fruit would be best displayed as a Column or Bar chart whereas the sales by day for a month might be better displayed as a Line chart. Take the following data and highlight all eight cells.

Excel screenshot 1

On the Insert ribbon click on the Column Icon and select the 2-D Clustered option. The following will be drawn.

excel screenshot

The gap between the columns can be widened and narrowed by right clicking on the column and selecting Format Data Series. Under Series Options the Gap Width slider can be moved to widen and narrow the columns. Technically as the data is discrete the columns should not touch.

To turn this into a Bar chart right click on the chart and select Change Series Chart Type… from the options select Bar and 2-D Clustered again.

Excel screenshot

The clustered aspect is used to group blocks together for example take the following sales data:

Excel screenshot

As above highlight all the data including the column and row headers and select the 2-D clustered chart. The following will be drawn.

excel screenshot

In the same place where we flexed the Gap Width in the above example you can also flex the overlap. This will move the 2020 and 2019 numbers apart or indeed overlap them. Below is shown with a 50% overlap. As in Blog 2 if you want the colours displayed the other way around (blue on top of red) then right click and Select Data. In the left hand side of the dialog box move the 2019 set to the top of the list so it is laid down first and 2020 to the bottom.

excel screenshot

The columns can be changed to cylinders, cones and displayed in 3-D as well. By right clicking on the chart and selecting Change Series Chart Type… these options can be easily explored. As mentioned in Blog 1 there is a balance between medium and message. In a pole of FDs that were asked to vote on their least favourite Excel feature, it was 3D charts that topped the list. Simplicity and clarity are the adage for communicating information.

Stacked column charts

These charts are best used to illustrate components parts of a total. Take for example the following product data for an ice cream business:

Excel screenshot

In this case select the cost and profit cells together with the column and row headers and select the 2-D stacked column chart. We have deliberately not included the revenue data as the ‘stacking’ of the other two pieces of data will calculate the revenue as the total height of the column.

excel screenshot

100% stacked column charts

In the chart above each of the columns is a different height and thus it is not clear which of the four products makes the greatest profit.  The 100% chart makes all the columns the same height and thus reveals the one which has the greatest profit percentage.  To create this chart just right click on the existing chart and Select Series Chart Type… and choose 100% stacked Column.

excel screenshot

As with the previous articles the best way to understand all the options is to simply play and explore the range of permutations that are available.