Combine Chart Types in Excel to Display Related Data

Create a combination chart to combine two or more chart types

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.

These instructions apply to Excel 2019, 2016, 2013, 2010, and Excel for Microsoft 365.

How to Combine Two Graphs in Excel

You can enhance the presentation of the two data sets by selecting complementary chart types, such as a column chart and line graph.

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.

First, you need to create a basic column chart.

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 into cells A1 to C14.

    Three columns of data in Excel
     Lifewire
  2. Select cells A2 to C14, the range of information you'll include in the chart.

    Data selected range in Excel
     Lifewire
  3. Select the Insert tab of the ribbon.

    Insert tab selected in Excel
     Lifewire
  4. In the Charts section, select the bar chart icon and choose the 2-D Clustered Column.

    

    2-D cluster column chart selected from the Excel menu
     Lifewire
  5. A basic column chart is created and placed in the worksheet.

Switching Data to a Line Graph

Changing chart types in Excel is done using the Change Chart Type dialog box. Since 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. You can do this 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.

The dialog box lists all available chart types, so it is easy to change from one chart to another.

  1. In the chart, select one of the temperature data columns to select all columns of that color (blue in this case) in the chart.

    An Excel column chart with the blue bars selected
     Lifewire
  2. Hover the mouse pointer over one of these columns and right-click to open the drop-down context menu.

    The Change Chart Type dialog box in Excel
     Lifewire
  3. Choose the Change Series Chart Type option from the drop-down menu to open the Change Chart Type dialog box.

    The Change Series Chart Type dialog box in Excel
     Lifewire
  4. Select the first line graph option in the Chart Type list.

    The Custom Combination dialog box in Excel
     Lifewire
  5. Select OK to close the dialog box and return to the worksheet.

  6. In the chart, the temperature data should now display as a blue line.

Moving Data to a Secondary Y-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 an almost straight line that tells us very little about monthly temperature variations.

The temperature data in an Excel chart displays as an almost straight line
 Lifewire

The temperature data looks like this because the scale of the vertical axis is trying to accommodate two data sets that vary significantly 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 vast 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.

  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.

    The Format Data Series dialog box in Excel
  3. Choose the Format Data Series option from the drop-down menu to open the Format Data Series dialog box.

  4. Select the Secondary Axis option in the pane of the dialog box.

    The Format Data Series panel in Excel
     Lifewire
  5. Select the X button to return to the worksheet.

  6. In the chart, the scale for the temperature data should now appear on the right side.

Adding a Secondary Y-Axis to an Excel Chart

Excel graph with orange columns for precipitation and a blue line for temperature
 Lifewire

This example shows how to combine column and line charts 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.

A few things to note:

  • Combined charts must share the X-axis (horizontal) value, such as time frame or location.
  • Not all chart types can be combined, including 3-D charts.

Generally, the procedure for creating a combination chart is as follows:

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

As a result of moving the temperature data to a second vertical axis, the line displaying that data shows greater variation from month-to-month making it easier to read.

Was this page helpful?