Excel: Chart Two Series with Differing Orders of Magnitude

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Problem: I'm trying to create a combo chart that shows revenue and gross profit percentage. In Excel 2010, the legend shows that both items are in the chart, but I can see only the Revenue series on the chart.

Strategy: Excel 2013 now easily handles this with their Recommended Charts feature. Make sure the gross profit percent series is formatted with a % format. Select the data. Go to Insert, Recommended Charts. Excel 2013 will offer a Clustered Column - Line on Secondary Axis chart:

  1. In Excel 2013, the combo chart is easy.

    In Excel 2010, you have to create the combo chart through a longer series of steps. Initially, the GP% series is on the chart, but the numbers are too small to be seen. You need to plot the series along a secondary axis and change the chart type.

  2. The GP% series is too small to be seen.

    Follow these steps:

    1. Click on the chart to activate it.
    2. Select Format, Current Selection dropdown, Series GP%. Excel will select the nearly invisible columns.
    3. Select Format, Format Selection. Excel displays the Format Data Series dialog.
    4. In the Series Options category in the Format Data Series dialog, change the Plot Series On setting from Primary Axis to Secondary Axis. You can now see the red columns.

  3. Move the GP% to the secondary axis.
    1. Excel will add numbers from 38% to 50% along the right axis of the chart. One problem with this setting is that Excel will now draw the red columns directly in front of the blue columns. In every month except November and December, you can't even see the blue columns. One option is to increase the gap width for the GP% series and make the columns thinner. Instead, I prefer to change the series to a line chart, as described in step 6.
    2. Make sure that Series GP% is still the current selection. Select Design, Change Chart Type. Choose a line chart. The reader can now see both the increasing trend of Revenue in December and the plummeting GP% in the same month.

  4. Revenue and GP% are both visible.

    Gotcha: When the range of a series is less than 20% of the maximum value of the series, Excel automatically zooms in on the range. For GP%, the range is 42% to 49%-a 7% range. 7/49 is less than 20%, so Excel has chosen to show 38% to 50% as the range for the second vertical axis. This allows you to see more detail in the GP%, but some purists always want the axis to start at 0.

    Additional Details: If the chart is going to be printed in color, I change the font for the right axis to match the color of the GP% line. This helps the reader to figure out that the right scale applies to the red line. Follow these steps to format the axis:

    1. Right-click on any number along the right axis. Choose Format Axis.
    2. In the Axis Options section, Minimum and Maximum are set to automatic. In the grayed out Minimum text box, you can see 0.38. Click the Fixed option button for Minimum and type the value 0.
    3. You won't find a font color setting in the Format Axis dialog, so select the Home, Font Color dropdown, Red. (All the Font settings in the Home tab will work to format the numbers along the axis.).
    4. Click on the numbers along the left axis. Use the Home tab to change the font color to blue.
    5. Steps 3 and 4 won't help if the chart is being printed in monochrome, so select Layout, Axis Titles, Secondary Vertical Axis Title, Rotated Title. Excel will add "œAxis Title" along the right axis.
    6. While the axis title is selected, type the new title GP%. As you are typing the characters, they will appear in the formula bar. When you press Enter, these characters will replace the axis title.

      These steps are optional, but they are reflected in the final result below.

    7. Layout, Legend, Show Legend at Top
    8. Layout, Axes, Primary Vertical Axis, Show axis in thousands.

    Results: The final chart is shown below.

  5. You can see both series on the chart.

    Additional Details: You can apply the steps above to build many different combination charts in Excel 2010. Excel 2013 offers a new interface for controlling combo charts. Select Change Chart Type, Combo, and you can choose a chart type and axis for each series.

  6. Combo charts are easier in Excel 2013.

For more resources for Microsoft Excel