• Excel Book Excerpt

Excel Chart Two Series with Differing Orders of Magnitude

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

Chart Two Series
with Differing Orders of Magnitude

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

Strategy: 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.

LE10001075.jpg 

Figure 1041 The GP% series is too small to be seen.

Follow these steps:

1. Click on the chart to activate it.

2. Select Layout, Current Selection dropdown, Series GP%. Excel will select the nearly invisible columns.

3. Select Layout, 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.

LE10001076.jpg

Figure 1042 Move the GP% to the secondary axis.

5. 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.

6. 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.

LE10001077.jpg

Figure 1043 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.

LE10001078.jpg

Figure 1044 You can see both series on the chart.

Gotcha: The combination chart used to be a choice in the Excel 2003 Chart Wizard. However, those selections lead people to believe that you could only show Series 1 as a column and Series 2 as a line. Microsoft’s new method leads most people to believe that you can no longer do a combination chart. In reality, in both the old and new Excel, you can easily combine many types of charts. In the chart below, all seven series started as a line chart. You then select a series, use Format, Change Chart Type, choose a new type. This rather hideous chart manages to combine stacked area, clustered column, line, and bar charts in one chart. I am not suggesting that you ever do this, but if you have to have Series 1, 3, 4 as a line and series 2 and 5 as a column, it is easy to do.

LE10001079.jpg

Figure 1045 Select one series, then change chart type.

<-Previous Topic Next Topic->

For more resources for Microsoft Excel:

privacy policy