Combine Chart Types in Excel 2010

Microsoft Excel
Wikimedia Commons

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.

By selecting complimentary chart types - such as a column chart and line graph - the presentation of the two data sets can be enhanced.

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.

Note: The steps outlined in this tutorial are only valid for versions of Excel up to and including Excel 2010.

01
of 09

Add a Secondary Y Axis to an Excel Chart

Create a Climate Graph in Excel 2010

Ted French

Combination Chart Requirements

  • One requirement for the data being displayed is that it must share the X-axis (horizontal) values - such as time frame or location.
  • Also, not all chart types can be combined. 3-D charts, for example, cannot be included in a combination chart.

Excel Climate Graph Tutorial

This tutorial covers the steps necessary for combining column and line charts together 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.

Tutorial Steps

The steps followed in the tutorial for creating the climate graph are:

  1. Creates 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)
  4. Apply formatting options to the basic climate graph so that it matches the graph seen in the image above

 

02
of 09

Entering and Selecting the Climate Graph Data

Create a Climate Graph in Excel

Ted French

The first step in creating a climate graph is to enter the data into the worksheet.

Once the data has been entered, the next step is to select the data that will be included in the chart.

Selecting or highlighting the data tells Excel what information in the worksheet to include and what ignore.

In addition to the number data, be sure to include all column and row titles that describe the data.

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

Tutorial Steps

  1. Enter the data as seen in the image above into cells A1 to C14.
  2. Highlight cells A2 to C14 - this is the range of information that will be included in the chart
03
of 09

Creating a Basic Column Chart

Create a Climate Graph in Excel

Ted French

All charts are found under the Insert tab of the ribbon in Excel, and all share these characteristics:

  • When creating any chart in Excel, the program first creates what is called a basic chart using the data selected
  • Hovering your mouse pointer over a chart category will bring up a description of the chart
  • Clicking on a category opens a drop down showing all of the chart types available in that category

The first step in creating any combination chart - such as a climate graph - is to plot all the data in one chart type and then switch one data set to a second chart type.

As mentioned previously, for this climate graph, we will first plot both sets of data on a column chart as seen in the image above, and then change the chart type for the temperature data to a line graph.

Tutorial Steps

  1. With the chart data selected, click on Insert > Column > 2-d Clustered Column in the ribbon
  2. A basic column chart, similar to the one seen in the image above, should be created and placed in the worksheet
04
of 09

Switching Temperature Data to a Line Graph

Switching Temperature Data to a Line Graph

Ted French

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.

This can be done 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

All available chart types are listed in the dialog box so it is easy to change from one chart to another.

Tutorial Steps

  1. Click once on one of the temperature data columns - shown in blue in the image above - to select all columns of that color in the chart
  2. Hover the mouse pointer over one of these columns and right click with the mouse 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. Click on the first line graph option in the right-hand pane of the dialog box
  5. Click OK to close the dialog box and return to the worksheet
  6. In the chart, the temperature data should now be displayed as a blue line in addition to the columns of the precipitation data
05
of 09

Moving Data to the Secondary Y Axis

Moving Data to the Secondary Y Axis

 Ted French

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 displayed as an almost straight line that tells us very little about monthly temperature variations.

This has occurred because of the scale of the one vertical axis is trying to accommodate two data sets that vary greatly in magnitude.

The average temperature data for Acapulco has only a small range from 26.8 to 28.7 degrees Celsius, while the precipitation data varies from less than three millimeters in March to over 300 mm in September.

In setting the scale of the vertical axis to show the great 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.

As a result, the chart will be able to display variations for both sets of data over the same time period.

Moving the temperature data to a secondary vertical axis is done in the Format Data Series dialog box.

Tutorial Steps

  1. Click once on the temperature line - shown in red in the image above - 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
06
of 09

Moving Data to the Secondary Y Axis (con't)

Moving Data to the Secondary Y Axis

 Ted French

Tutorial Steps

  1. Click on the Series Options in the left-hand pane of the dialog box if necessary
  2. Click on the Secondary Axis option in the right-hand pane of the dialog box as shown in the image above
  3. Click on the Close button to close the dialog box and return to the worksheet
  4. In the chart, the scale for the temperature data should now be displayed on the right side of the chart

As a result of moving the temperature data to a second vertical axis, the line displaying the precipitation data should show greater variation from month to month making it easier to see the temperature.

This occurs because the scale for the temperature data on the vertical axis on the right side of the chart now has only to cover a range of fewer than four degrees Celsius rather than a scale that ranged from zero to 300 when the two data sets shared the one scale.

Formatting the Climate Graph

At this point, the climate graph should resemble the image shown in the next step of the tutorial.

The remaining steps in the tutorial cover applying formatting options to climate graph to make it resemble the graph shown in step one.

07
of 09

Formatting the Climate Graph

Formatting the Climate Graph

Ted French

When it comes to formatting charts in Excel you do not have to accept the default formatting for any part of a chart. All parts or elements of a chart can be changed.

The formatting options for charts are mostly located on three tabs of the ribbon that are collectively called the Chart Tools

Normally, these three tabs are not visible. To access them, simply click on the basic chart you just created and three tabs - Design, Layout, and Format - are added to the ribbon.

Above these three tabs, you will see the heading Chart Tools.

In the remaining tutorial steps the following formatting changes will be made:

  • Add chart and axes titles
  • move the legend to the top of the chart
  • Change font and background colors
  • Bevel the outside edge of the chart
  • Resize the chart

Adding the Horizontal Axis Title

The horizontal axis shows the dates along the bottom of the chart.

  1. Click on the basic chart in the worksheet to bring up the chart tool tabs
  2. Click on the Layout tab
  3. Click on Axis Titles to open the drop down list
  4. Click on the Primary Horizontal Axis Title > Title Below Axis option to add the default title Axis Title to the chart
  5. Drag select the default title to highlight it
  6. Type in the title "Month"

Adding the Primary Vertical Axis Title

The primary vertical axis shows the volume of shares sold along the left side of the chart.

  1. Click on the chart if necessary
  2. Click on the Layout tab
  3. Click on Axis Titles to open the drop down list
  4. Click on the Primary Vertical Axis Title > Rotated Title option to add the default title Axis Title to the chart
  5. Highlight the default title
  6. Type in the title "Precipitation (mm)"

Adding the Secondary Vertical Axis Title

The secondary vertical axis shows the range of stock prices sold along the right side of the chart.

  1. Click on the chart if necessary
  2. Click on the Layout tab
  3. Click on Axis Titles to open the drop down list
  4. Click on the Secondary Vertical Axis Title > Rotated Title option to add the default title Axis Title to the chart
  5. Highlight the default title
  6. Type in the title "Average Temperature (°C)"

Adding the Chart Title

  1. Click on the chart if necessary
  2. Click on the Layout tab of the ribbon
  3. Click on Chart Title > Above Chart option to add the default title Chart Title to the chart
  4. Highlight the default title
  5. Type in the title Climatograph for Acapulco (1951–2010)

Changing the Chart Title Font Color

  1. Click once on Chart Title to select it
  2. Click on the Home tab on the ribbon menu
  3. Click on the down arrow of the Font Color option to open the drop down menu
  4. Choose Dark Red from under the Standard Colors section of the menu
08
of 09

Moving the Legend and Changing the Background Area Colors

Formatting the Climate Graph

Ted French

By default, the chart legend is located on the right-hand side of the chart. Once we add the secondary vertical axis title, things get a little crowded in that area. To alleviate the congestion we will move the legend to the top of the chart below the chart title.

  1. Click on the chart if necessary
  2. Click on the Layout tab of the ribbon
  3. Click on Legend to open the drop-down list
  4. Click on Show Legend at Top option to move the legend to below the chart title

Using Context Menu Formatting Options

In addition to the chart tools tabs on the ribbon, formatting changes can be made to charts using the drop-down or context menu that opens when you right-click on an object.

Changing the background colors for the entire chart and for the plot area - the central box of the chart that displays the data - will be done using the context menu.

Changing the Chart Area Background Color

  1. Right click on the white chart background to open the chart context menu
  2. Click on the small down arrow to the right of the Shape Fill icon - the paint can - in the context toolbar to open the Theme Colors panel
  3. Click on White, Background 1, Darker 35% to change the chart background color to dark gray

Changing the Plot Area Background Color

Note: Be careful not to select the horizontal grid lines running through the plot area rather than the background itself.

  1. Right click on the white plot area background to open the plot area context menu
  2. Click on the small down arrow to the right of the Shape Fill icon - the paint can - in the context toolbar to open the Theme Colors panel
  3. Click on White, Background 1, Darker 15% to change the plot area background color to light gray
09
of 09

Adding the 3-D Bevel Effect and Re-sizing the Chart

Adding the 3-D Bevel Effect

Ted French

Adding the 3-D bevel effect adds a bit of depth to the chart. It leaves the chart with an embossed-looking outside edge.

  1. Right-click on the chart background to open the chart context menu
  2. Click on Format Chart Area option in the context toolbar to open the dialog box
  3. Click on 3-D Format in the left-hand panel of the Format Chart Area dialog box
  4. Click on the down arrow to the right of the Top icon in the right-hand panel to open the panel of bevel options
  5. Click on Circle option in the panel - the first option in the Bevel section of the panel
  6. Click on the Close button to close the dialog box and return to the worksheet

Re-sizing the Chart

Re-sizing the chart is another optional step. The benefit of making the chart larger is that it reduces the crowded look created by the second vertical axis on the right-hand side of the chart.

It will also increase the size of the plot area making the chart data easier to read.

The easiest way to resize a chart is to use the sizing handles that become active around the outside edge of a chart once you click on it.

  1. Click once on the chart background to select the entire chart
  2. Selecting the chart adds a faint blue line to the outside edge of the chart
  3. In the corners of this blue outline are ​sizing handles.
  4. Hover your mouse pointer over one of the corners until the pointer changes into a double-headed black arrow
  5. When the pointer is this double-headed arrow, click with the left mouse button and pull outward slightly to enlarge the chart. The chart will re-size in both length and width. The plot area should increase in size as well.

If you have followed all of the steps in this tutorial at this point your Climate graph should resemble the example displayed in the image on the first part of this tutorial.