Home > Sample chapters > Microsoft Office > Excel

Creating Charts and Graphics in Microsoft Excel 2013

Creating dual-axis charts

The Excel 2013 charting engine provides you with the flexibility to plot more than one data series, even if the series use two different scales. For example, Consolidated Messenger might track seasonal package volumes for each regional distribution center by category and, as part of the same data collection, track the number of improperly routed packages.

httpatomoreillycomsourcemspimages1561281.png

When you have two differing but related data series in a table, you can summarize the data by using a dual-axis chart. To create a dual-axis chart, click any cell in the data you want to chart and then, on the Insert tab, click the type of chart you want to create. When you do, Excel plots both data series by using that chart type.

You can plot these two data series using separate axes by creating a combo chart. To do that, click the chart and then, on the Design tool tab, click Change Chart Type to display the dialog box of the same name. In the Change Chart Type dialog box, click the All Charts tab, and then click Combo to display the Combo charts page.

You can now use the controls in the Choose The Chart Type And Axis For Your Data Series area of the dialog box to select how to plot each series. To choose how to format a series, click the Chart Type arrow for that series and select its chart type. If you want the series to be plotted in relation to the values on the left vertical axis, leave the Secondary Axis check box cleared. To have the series plotted in relation to the values on the right vertical axis, select the Secondary Axis check box.

When you click OK, Excel creates your chart.

In this exercise, you’ll create a dual-axis chart.

  1. Click any cell in the Excel table.

  2. Click the Insert tab and then, in the Charts group, click the Insert Column Chart button and click the first 2D Column chart subtype, Clustered Column. When you do, Excel creates a chart with the two data series.

  3. Right-click the Chart Title text box and click Edit Text. Enter Comparison for the chart title.

  4. On the Design tool tab, click the Change Chart Type button to open the Change Chart Type dialog box.

  5. If necessary, click the All Charts tab to display that page of the dialog box. Then, in the list of chart types, click Combo to display the Combo Chart interface.

  6. Verify that the Volume series will be plotted by using a Clustered Column chart and that the Exceptions series will be plotted by using a Line chart.

  7. Select the Secondary Axis check box next to the Exceptions series. Doing so adds a second vertical axis to the right edge of the chart. The values on this axis reflect the values in the Exceptions series.

  8. Click OK to create the chart.