Creating a chart with vastly different numbers

Sep 8, 2022 | Excel Hints and Tips

Creating a chart with 2 or more sets of figures, with one set much larger than the others, will often produce a diagram which is not that useful.  One set of the figures will show up perfectly but the other set are so small they can barely be seen as shown below. 

Clustered column chart

The turnover can be clearly seen but the number of employees does not even show up on the chart.  This is where the combination chart comes in.

What is a Combination Chart?

A combination chart often uses 2 different scales for the 2 sets of data enabling the smaller figures to still be viewed.  One scale is displayed on the left of the chart, the other on the right.  Often the 2 sets of data are shown using different chart styles too, most commonly a column chart and a line chart.

How to create a combination chart

  1. Select the data to be charted including the column headings
  2. From the Insert tab choose Recommended Charts then the All Charts tab
  3. From the chart types on the left select Combo
  4. There are 3 preset options to choose from but these can be adjusted as required.

a) Clustered column with line but with no secondary axis.  One set of the data is shown using a column chart, the other using the line but both on the same axis which doesn’t solve the issue we have

b) Clustered Column with line, with the line on the secondary axis as a different scale to the other set of data.  This enables both sets of data to be clearly read when the numbers are significantly different

c) Stacked area and clustered column with no secondary axis which has the same issue as (a)

5. The chart type for each set of data and which is on a secondary axis if that is required can be adjusted using the drops down and tick boxes available.  If a secondary axis is used then this is the scale on the right of the chart and the unchecked primary axis is on the left

Chart type and secondary axis

6. The combination chart produced makes it much easier to see whether there is any dependency on turnover and number of staff members 

Combination chart

Changing the scales on the primary or secondary y axis 

The scale on both the primary and secondary axis will be created automatically based on the figures in the data but this can also be tweaked 

  1. On the Chart Design tab select Add Chart Element 
  2. Choose Axis and More Axis Options 
  3. By default the x axis at the bottom is selected. 
  4. Click the numbers on the primary y axis on the left 
  5. Change the Minimum and Maximum figures and the Major and Minor units if necessary in the Axes pane on the right 
  6. Repeat with the secondary y axis. 

Further Reading

If you’ve enjoyed reading about how to create a chart with vastly different figures in Microsoft Excel, there’s some other blogs below that you might find useful:

Want to learn more about Microsoft Excel? Then email lara@laramellortraining.co.uk to discuss how I can help or have a look at the Microsoft Courses I run.