Excel 2020: Show Two Different Orders of Magnitude on a Chart - KING OF EXCEL

Thursday, March 12, 2020

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

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

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.
#evba #etipfree #eama #kingexcel
📤How to Download ebooks: https://www.evba.info/2020/02/instructions-for-downloading-documents.html?m=1

No comments:

Post a Comment