Combine Chart Types in Excel to Display Related Data

Create a combination chart to combine two or more chart types

Bar chart with an overlaid line chart

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

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

    Screenshot of selected range
  3. Select the Insert tab of the ribbon.

    Screenshot of Insert tab
  4. In the Charts section, select the bar chart icon and choose the 2-D Clustered Column.

    

    2-D cluster column chart selected on Excel menu
  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
  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
  3. Choose the Change Series Chart Type option from the drop-down menu to open the Change Chart Type dialog box.

    Screenshot of Change Series Chart Type
  4. Select the first line graph option in the Chart Type list.

    Screenshot of Custom Combination
  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.

Temperature data in an Excel chart displaying as an almost straight line

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.

    Screenshot of Secondary Axis in Format Data Series pane
  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

Screenshot of Excel showing the final graph

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.