Combine Chart Types in Excel to Display Related Data

Financial growth
Sean Gladwell / Getty Images

Excel lets you combine two or more different chart or graph types to make it easier to display related information together. One easy way to accomplish this task is by adding a second vertical or Y axis to the right side of the chart. The two sets of data still share a common X or horizontal axis at the bottom of the chart.

By selecting complimentary chart types, such as a column chart and line graph, the presentation of the two data sets can be enhanced.

Common uses for this type of combination chart include displaying average monthly temperature and precipitation data together, manufacturing data such as units produced and the cost of production, or monthly sales volume and average monthly sale price.

01
of 05

Adding a Secondary Y-Axis to an Excel Chart

Screenshot of Excel showing the final graph

This tutorial covers the steps necessary for combining column and line charts together to create a climate graph or climatograph, which shows the average monthly temperature and precipitation for a given location.

As shown in the image above, the column chart, or bar graph, shows the average monthly precipitation while the line graph displays average temperature values.

Combination Chart Requirements

  • One requirement for the data being displayed is that it must share the X-axis (horizontal) values such as time frame or location.
  • Also, not all chart types can be combined. 3-D charts, for example, cannot be included in a combination chart.

To create our secondary Y-Axis, we will following these step:

  1. Create a basic two-dimensional column chart, which displays both precipitation and temperature data in different colored columns.
  2. Change the chart type for the temperature data from columns to a line.
  3. Move the temperature data from the primary vertical axis (left side of the chart) to the secondary vertical axis (right side of the chart).
02
of 05

Entering and Selecting the Graph Data

Screenshot of Excel showing the table data

The first step in creating a climate graph is to enter the data into the worksheet. Once the data has been entered, the next step is to select the data that will be included in the chart.

Selecting or highlighting the data tells Excel what information in the worksheet to include and what ignore. In addition to the number data, be sure to include all column and row titles that describe the data.

The tutorial does not include the steps for formatting the worksheet as shown in the image above. Information on worksheet formatting options is available in this basic excel formatting tutorial.

  1. Enter the data as seen in the image above into cells A1 to C14.
  2. Highlight cells A2 to C14 — this is the range of information that will be included in the chart
03
of 05

Creating a Basic Column Chart

Screenshot of Excel showing a basic line graph

All charts are found under the Insert tab of the ribbon in Excel, and all share these characteristics:

  • When creating any chart in Excel, the program first creates what is called a basic chart using the data selected.
  • Hovering your mouse pointer over a chart category will bring up a description of the chart.
  • Clicking on a category opens a drop-down showing all of the chart types available in that category.

The first step in creating any combination chart, such as a climate graph, is to plot all the data in one chart type and then switch one data set to a second chart type.

As mentioned previously, for this climate graph, we will first plot both sets of data on a column chart, and then change the chart type for the temperature data to a line graph.

  1. With the chart data selected, click on Insert > Column > 2-D Clustered Column in the Insert tab of the ribbon.
  2. A basic column chart, similar to the one seen in the image above, should be created and placed in the worksheet
04
of 05

Switching Data to a Line Graph

Screenshot of Excel showing how to change a graph type

Changing chart types in Excel is done using the Change Chart Type dialog boxSince we wish to change only one of the two data series displayed to a different chart type, we need to tell Excel which one it is. This can be done by selecting, or clicking once, on one of the columns in the chart, which highlights all columns of that same color.

Choices for opening the Change Chart Type dialog box include:

  • Clicking on the Change Chart Type icon on the Design tab of the ribbon.
  • Right-clicking on one of the selected columns and choosing the Change Series Chart Type option from the drop-down menu.

All available chart types are listed in the dialog box so it is easy to change from one chart to another.

  1. Click once on one of the temperature data columns to select all columns of that color in the chart.
  2. Hover the mouse pointer over one of these columns and right click with the mouse to open the drop-down context menu.
  3. Choose the Change Series Chart Type option from the drop-down menu to open the Change Chart Type dialog box.
  4. Click on the first line graph option in the right-hand pane of the dialog box.
  5. Click OK to close the dialog box and return to the worksheet.
  6. In the chart, the temperature data should now be displayed as a blue line in addition to the columns of the precipitation data.
05
of 05

Moving Data to a Secondary Y-Axis

Screenshot of Excel showing how to add a secondary axis

Changing the temperature data to a line graph may have made it easier to distinguish between the two data sets, but, because they are both plotted on the same vertical axis, the temperature data is displayed as an almost straight line that tells us very little about monthly temperature variations.

This has occurred because of the scale of the one vertical axis is trying to accommodate two data sets that vary greatly in magnitude. The average temperature data has only a small range from 26.8 to 28.7 degrees Celsius, while the precipitation data varies from less than three millimeters over 300 mm.

In setting the scale of the vertical axis to show the great range of precipitation data, Excel has removed any appearance of variation in the temperature data for the year. Moving the temperature data to a second vertical axis, displayed on the right side of the chart, allows for separate scales for the two data ranges.

Screenshot of Excel showing the temperature and precipitation range
Note the small difference in range for temperature, but the significant difference for precipitation.
  1. Click once on the temperature line to select it.
  2. Hover the mouse pointer over the line and right click with the mouse to open the drop-down context menu.
  3. Choose the Format Data Series option from the drop-down menu to open the Format Data Series dialog box.
  4. Click on the Secondary Axis option in the pane of the dialog box.
  5. Click on the X button to return to the worksheet.
  6. In the chart, the scale for the temperature data should now be displayed on the right side of the chart.

As a result of moving the temperature data to a second vertical axis, the line displaying the precipitation data should show greater variation from month to month making it easier to see the temperature.

This occurs because the scale for the temperature data on the vertical axis on the right side of the chart now has only to cover a range of fewer than four degrees Celsius rather than a scale that ranged from zero to 300 when the two data sets shared the one scale.