Skip to Main Content

go to Indiana University Indiana University


Combination Charts and Secondary Axes in Excel

Combination charts can be used to highlight different types of information in a single chart.  Using a combination of a column chart and a line chart can give a more specific visual representation of the data and help to make data easier to understand. Combination charts are particularly helpful when the values of the data vary greatly or when you are working with mixed types of data.

To create the combination chart, we will use the example of an athlete who is training for a triathalon.  Each week this person spends several hours running, biking and swimming.  We can create a chart that shows the number of hours spent on each activity as well as the total number of calories consumed each week.

This is the data as seen in Excel.

original worksheet

We will create a combination chart that shows a column for each training activity and a line for the total calories. To begin, we will select all of the data.

To create the initial column chart, on the Insert tab, in the Charts group, click Column, then click Clustered Column.  The result shows us each activity, as well as the total calories, as columns.  The activity hours are barely visible because they are much less than the total calories.

FirstChart

To begin changing the Total Calories Consumed data series to a line chart, select the Total Calories Consumed column.

On the Design contextual tab, in the Type group, click Change Chart Type.  The Change Chart Type dialog box opens.  Click Line with Markers.  Click OK to close the dialog box and apply the change.

SecondChart

The chart now shows the Total Calories Consumed data as a line. These totals are much more than the activity totals, and the values on the vertical axis represent this.  As a result, the activity totals are at the bottom of the chart with the Total Calories Consumed at the top. This problem can be remedied by creating a second vertical axis that shows the values associated with the activity hours.

To begin creating the secondary axis, select the Total Calories Consumed line.

Move to the Format contextual tab.  In the Current Selection group, confirm that we are working with “Series: Total Calories Consumed.”  Click Format Selection.  The Format Data Series dialog box opens.  Under Series Options, click the Secondary Axis radio button.  To close the dialog box, click Close. The chart now shows the Total Calories Consumed on the secondary vertical axis and the activity hours on the primary axis.

It would be helpful to have axis titles.  Without them, users may be unsure of which axis represents the line and which represents the columns.

To add axis titles, move to the Layout contextual tab.  In the Labels group, click Axis Titles.  Point to Primary Vertical Axis, click Rotated Title.  The title appears on the chart, but currently reads, “Axis Title.”  Since the text is selected, we can simply begin typing, “Activity Hours” and press Enter.  The primary vertical axis title is now correct.

To add the title for the secondary vertical axis, follow the same steps.  When we are finished, the chart looks like this:

FinalChart 


6 Responses to "Combination Charts and Secondary Axes in Excel" to “Combination Charts and Secondary Axes in Excel”

  1. Benjamin Vis Says:

    Great your example; you use two vertical axes.
    However, I would like to use three and more vertical axes for three or more completely different quantities.
    I have not yet succeeded to do this in Excel.
    Please, give me a hand.
    Benjamin.

  2. April Law Says:

    Benjamin,
    My colleague and I have both looked at this and come to the conclusion that it is not possible to have more than 2 vertical axes in Excel. In attempting to solve this problem, I created two charts, set the Fill to “none,” and overlayed them. This was a tedious process and the end result looked OK, but not great.
    For a second option, a panel chart might give you the results you want. There are several Web pages that can show you how to construct a panel chart.
    Sorry I’m not more help.
    April

  3. Tina Says:

    I am trying to create a second vertical axis. I chose the data series, went to format selection and it isn’t giving me the option to chose a secondary vertical axis. Do you know what I might be doing wrong?
    Thanks.
    Tina

  4. April Law Says:

    Tina,
    Without seeing your work, I’m not sure what is happening. I have a couple of suggestions. First, make sure that you are selecting the data series, not the plot area or gridlines. You can tell what you have selected on the Format Tab in the Current Selection group. The name of the selected series should be there. Second, make sure you’re using a chart that supports secondary axes. Starting with a basic 2-D column chart might be helpful as some chart types will not work in combination.
    April

  5. danielle waring Says:

    Good Morning just figured i will let you know i had a problem with your blog coming up blank as well. Must be gremlins in the system.

  6. Tom Mason Says:

    Hi Danielle, and everyone else having an issue with the blog coming up blank.

    We recently upgraded from an ancient version of WordPress to the most recent. I’ve seen this problem before on other blogs that I administer and it’s a problem with caches in your web browser. Unfortunately, when the blog comes up blank, it’s difficult to pass word on how to fix the problem to people who can’t see the blog.

    The easiest way to solve the problem is to navigate to the http://ittrainingtips.iu.edu page, then press Ctrl + F5 (Cmd + Ctrl + R on the Mac). This forces the web browser to re-cache the page when it reloads. This should fix the problem 90% of the time.

Leave a Reply

 

Get Training Tips via RSS Subscribe to Entries feed or Read Comments via RSS Subscribe to Comments feed.