Combine Chart Types in Excel to Display Related Data

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.

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.

1. Enter the data as seen in the image into cells A1 to C14.

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

3. Select the Insert tab of the ribbon.

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

   

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.

2. Hover the mouse pointer over one of these columns and right-click 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. Select the first line graph option in the Chart Type list.

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

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.

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

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.