Combine chart types in Excel to display related data

Excel allows you to combine two or more different types of charts or graphs to make it easier to display related information together. An easy way to accomplish this task is to add a second vertical axis, or Y-axis to the right of the diagram. The two data sets still share a common X or horizontal axis at the bottom of the chart.

You can improve the presentation of the two data sets by selecting additional chart types, such as bar chart and line chart.

Common uses for this type of combo chart include displaying monthly average temperature and precipitation data, production data such as units produced and costs, or monthly sales volume and average monthly sales price together.

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

Contents
  1. Add a secondary Y-axis to an Excel chart
  2. Create a basic bar chart
  3. Switch data to a line graph
  4. Move data to the secondary Y-axis

Add a secondary Y-axis to an Excel chart

This example shows how you can combine bar and line charts to create a climate chart or climatograph that shows the average monthly temperature and rainfall for a particular location.

As seen in the image above, a bar graph or histogram shows the average monthly rainfall, while a line graph shows the average temperatures.

A few things to watch out for:

  • Combination charts must share an x-axis (horizontal) value, such as a time frame or location.

  • Not all map types can be combined, including 3D maps.

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

  1. Create a simple 2D bar chart that displays rainfall and temperature data in colorful bars.

  2. Change the chart type for temperature data from bars to lines.

  3. Move the temperature data from the primary vertical axis (left side of the graph) to the secondary vertical axis (right side of the graph).

Create a basic bar chart

The first step in creating a climate plot (or any chart or graph) is to enter data into a worksheet, as shown in the image above. Then select the data you want to include in the chart.

By selecting or highlighting data, Excel knows which information to include on the worksheet and which to ignore. In addition to the numeric data, make sure to include column and row headings that describe the data in your selection.

All charts are in the tab Insert on the adhesive tape in excel:

  • When creating a chart in Excel, the program first creates a so-called basic chart using the selected data.

  • Hover your mouse over a chart category to get a description of the chart.

  • Click on a category to open a drop-down menu that lists all chart types in that category.

The tutorial does not include the steps to format a worksheet as shown in the image above. Information about sheet formatting options is available in this simple Excel formatting tutorial.

  1. Enter the details as shown in the image above, in cells A1 C14

  2. highlight cells A2 C14, the range of information you will include in the chart.

  3. With the chart data selected, click Insert tab on the adhesive tape

  4. In section diagrams click the histogram icon and select 2D clustered column.

  5. A basic bar chart should be created and placed on the sheet, similar to the one in the image above.

Switch data to a line graph

Changing chart types in Excel is done using a dialog box Change chart type. Since we only want to change one of the two data series shown to a different chart type, we need to tell Excel which type it is. This can be done by selecting or clicking one of the columns in the chart in which all columns of the same color are marked.

Options for opening the Change Chart Type dialog box include:

  • Click the icon Change chart type tab Design on the adhesive tape

  • Right click in one of the selected columns and select an option Change series chart type in the drop-down menu.

The dialog box lists all available chart types so that you can easily switch from one chart to another.

  1. In the diagram, click on one of the temperature data columns to select all bars of that color (blue in this case) in the graph.

  2. Mouse over to one of these columns and right click to open the drop-down menu.

  3. Select Change series chart type in the drop-down menu to open the dialog box Change chart type.

  4. Press first line chart parameter on the right side of the dialog box.

  5. Click Okay to close the dialog box and return to the worksheet.

  6. The temperature data should now appear as a blue line in the graph.

Move data to the secondary Y-axis

Changing the temperature data to a line graph might make it easier to distinguish between the two data sets, but since they are both plotted on the same vertical axis, the temperature data is almost a straight line, which tells us very little about the monthly temperature variation.

The temperature data looks like this because the vertical axis scale tries to accommodate two sets of data that differ significantly in size. Average temperature data has a narrow range of 26.8 to 28.7 degrees Celsius, while precipitation data ranges from less than three millimeters to 300 mm.

By scaling the vertical axis to display a wide variety of precipitation data, Excel eliminated any change in temperature data for the year. By moving the temperature data to the second vertical axis shown on the right side of the graph, you can separate the scales for the two data ranges.

  1. Click once temperature line to select it.

  2. Mouse over per line and right click to open the drop-down menu.

  3. Select an option from the drop-down menu Formatting a series of data to open the dialog Format a series of data.

  4. Press the button secondary axis on the dialog box.

  5. Press the button X to return to the sheet.

  6. The scale for the temperature data should appear on the right side of the graph.

As a result of moving temperature data to a second vertical axis, the line representing this data shows wide variations from month to month, making it easier to read.

Leave a Reply

Your email address will not be published.