MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2020: Show Two Different Orders of Magnitude on a Chart


March 12, 2020 - by Bill Jelen

Excel Show Two Different Orders of Magnitude on a Chart. Photo Credit: Mikael Kristenson at Unsplash.com

It is nearly impossible to read a chart where one series is dramatically larger than other series. In the following chart, the series for Year to Date Sales is 10 times larger than most of the monthly sales. The blue columns are shortened, and it will be difficult to see subtle changes in monthly sales.

Two series are in this chart. The first is monthly sales. The second is YTD sales and ends up being, well, 12 times taller than monthly sales. You can not make out any of the monthly variability because all of the monthly columns look small.

Combo charts were always possible in Excel, but they have a new interface starting in Excel 2013. Choose the chart above and select Change Chart Type. Choose Combo from the category list on the left. You then have the choices shown below. Move the larger number (YTD Sales) to a new scale on the right axis by choosing Secondary Axis. Change the chart style for one series to Line from Clustered Column.


In the new Combo Chart interface, change the YTD Sales series to be on the Secondary Axis and change the chart type from Clustered Column to Line.

The result: Columns for the monthly revenue are taller, so you will be able to make out subtle changes like a decrease from July to August.

After moving the larger YTD number to the secondary axis, the monthly numbers show a great deal of variability, with low sales in February and high numbers in December.

To get the additional formatting to the chart above, select the numbers on the left axis. Use the Font Color dropdown on the Home tab to choose a blue to match the blue columns. Select the green line. Select Format, Shape Outline to change to a darker green. Select the numbers on the right axis and change the font color to the same green. Double-click each axis and change Display Units to Thousands. Double-click a blue column and drag the Gap Width setting to be narrower. Double-click the legend and choose to show the legend at the top.

Title Photo: Mikael Kristenson at Unsplash.com


This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.


Bill Jelen is the author / co-author of
MrExcel 2020 - Seeing Excel Clearly

This is a 4th edition of MrExcel LX. Updates for 2020 include: Ask a question about your data, XLOOKUP, Power Query's Data Profiling tools, How Geography Data Types decide which Madison, A SEQUENCE example for descending 52 weeks, Exchange Rates support in Stock Data Types, How to collapse the Search box, How to leave effective feedback for Microsoft, How to post your worksheet to the MrExcel Board using XL2BB.