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