Director SumProduct
Share this content
A picture of a spreadsheet used for budgeting and planning
istock_Spreadsheets_simpson33

Excel tip: Creating similar charts side by side

by

Microsoft MVP Liam Bastick from SumProduct explains how you can use Excel to create two charts side by side so you can compare data for a report.

19th May 2021
Director SumProduct
Share this content

Imagine you needed to create comparison charts for a report or dashboard. In this step by step tutorial, I am going to show you how you can do this in Excel.

Let’s say you had the following data:

Example data

To do this, I would create my chart as follows. I will select cells F35:G38 and then insert a 2-D Clustered Column chart (keyboard shortcut ALT + N + C):

Chart created with the data

With a little tidying up of the chart, I could get something similar to the following:

A tidied up chart

Note that the scale on the y-axis goes from zero (0) to 1,200. Exciting I know, but I do need to point this out for reasons that will become apparent shortly.

Rather than add the second dataset into this chart, for dashboard reporting reasons, I have decided I want to display a similar chart next to this one.

The easiest way to do this is to right-click on the chart and select ‘Copy’ (CTRL + C). I can then paste a duplicate wherever I please.

If I right-click on the data,

A dashboard chart

I can choose ‘Select Data…’ which results in the following dialog box:

data converted into a dialog box

I can then edit the ‘Legend Entries (Series)’ section (pictured above):

Edited Legend entries

Here, I have changed the references to the second data set. Clicking ‘OK’ twice in succession, I get a replica chart but for the following year:

Replica chart

Do you see comparing these charts can be misleading? The right-hand chart has a y-axis scale that goes from zero (0) to 900 – not 1,200. We are not comparing like with like. This is a common mistake made in dashboard reporting and easily rectified.

Most modellers – assuming they notice – will right-click on one of the two y-axes and then click on “Format Axis…’ on the shortcut menu that appears. This gives rise to the ‘Format Axis’ pain where they will manually modify the maximum value.

Format Axis

But what happens when the data changes? The process is doomed to be repeated ad nauseum. The problem is we cannot link this ‘Maximum’ value to a cell and I don’t want to use VBA when you can simply cheat!

The trick is to go right back to the start and add another column to our chart data, viz.

Go back to the start and add another column

In column I, I have added a ‘Max’ column which determines the largest value in all of the data using the formula

=MAX($G$35:$H$38)

Now, we include this column in our original chart. Returning to the first chart (you can bin the second one), we’ll re-open the ‘Select Data Source’ dialog box and this time, we’ll add data:

Select data source

The ‘Max’ data will be included as follows:

Max data

Clicking ‘OK’ twice in succession generates the following chart:

Clicking ‘OK’ twice in succession generates the following chart

It’s not pretty, but we aren’t finished. Now, we will right-click on this second data series and select ‘Format Data Series…’:

Format data series

In the resulting ‘Format Data Series’ pane, change the ‘Series Overlap’ to 100%:

In the resulting ‘Format Data Series’ pane, change the ‘Series Overlap’ to 100%:

Given this data series was added second (it’s the bottom data series in the ‘Select Data Source’ dialog box), this “obliterates” our original data, viz.

Given this data series was added second (it’s the bottom data series in the ‘Select Data Source’ dialog box), this “obliterates” our original data

That’s fine, because we are again going to right-click on this data series and select ‘Format Data Series…’, but this time select ‘No Fill’ for both the ‘Fill’ and the ‘Border’ of the data series:

this time select ‘No Fill’ for both the ‘Fill’ and the ‘Border’ of the data series

Lo and behold, we have our original chart back again after we add a chart title and remove the second data series’ shadow if necessary (it depends on what chart you choose):

we have our original chart back again after we add a chart title and remove the second data series’ shadow

The difference now is that when we repeat the process of replicating the chart and changing the dataset (keep ‘Max’ as is), we get comparative charts side by side (i.e. the y-axis scale is the same for both):

Side by side comparison

If you have a query, please feel free to drop Liam a line at [email protected] or visit the SumProduct website.

Replies (0)

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.