Combining Chart Types, Adding a Second Axis
Today’s author: Katherine Fifer, an intern on the Excel team. Katherine is just wrapping up a summer of fantastic work, and today she is going to discuss how to create a combo chart in Excel 2007.

Often it is useful to create charts which compare different types of data. For example, you might want to compare share price with trading volume, or revenue with number of units sold, or poverty rate with unemployment and consumer confidence, or any number of other things.

To quickly and clearly display data of different types, it can be helpful to plot some data series either with different chart types, on different axes, or both. Let’s look at an example.

Suppose I work at a manufacturing company and I’d like to analyze the number of units we’ve sold over the last few months and the total dollars sold each month. I’m hoping to identify trouble spots, such as high unit sales but low dollar transaction amounts, which may indicate that the units are being discounted too deeply.

I could make two different charts – one plotting units sold by month, and one plotting transaction dollars by month. However, it will be challenging to analyze the two charts separately. I’d much rather create a chart like the one pictured below.

With the right helpful hints, it turns out that making this kind of chart isn’t too tough. First, I’ll walk through the two major steps (changing series chart types, and adding an axis) and then I’ll describe how to put the finishing touches on this chart.

STEP 1: So that we’re on the same page, I’m starting with the following simple data set:

I could also format this as a table by simply clicking “Format as Table” on the Home tab of the ribbon and choosing any of the styles. By setting up a table, I can more easily read the data, and I can also do more advanced things like filtering the data (more on this in the Tables section of the Excel blog). I’ve included what the table might look like below, but for simplicity’s sake, I’ll carry through the rest of the example without the table formatting.

Fun shortcut: If you right-click a series, you can choose from the menu to either “change series chart type” to change its type or “Format Data Series” to change its axis. If you want to do things quickly and are already familiar with this charting, you can go this route. Otherwise, walk through the steps and return to this shortcut at the end.

Combining Different Chart Types
The first step in building a chart with more than one type is to actually set up a chart with just one type. Here, I want a chart with columns and lines, but I’ll start with a regular column chart. (Note: It actually doesn’t matter which chart type you start with, but if you’re working with a lot of series, I’d pick the chart type which applies to the majority of your series – it’ll mean less work for you later).

STEP 2: Select the data you entered in STEP 1. Go to the “Insert” tab on the ribbon and insert a column chart (as shown below).

Now we have a column chart with two series, both charted on the same axis. This is all of our data, but it is not displayed in a meaningful way. Because of the difference in scale between transactions and units sold, we can barely even see the units sold series:

The next big step is changing the chart type of the “Total Transactions” series into a line.

STEP 3: Select a series. We want to select the series we’d like to change to a different type – in this case it’s the “Total Transactions” series.

Selecting the series: You can select a data series in one of many ways. I’ll outline the two most common here. Note: This is where most people trip up, so pay careful attention to select a series rather than the whole chart or an individual data point.

Option 1: Click on the series in the chart. Note: Don’t click on the legend text “Total Transactions”. Rather, click on one of the red bars in the chart. You should now see that series highlighted as follows (note the little blue circles on the red columns):

Option 2: Click on either the Chart Format tab or the Chart Layout tab of the Ribbon. (Note: these tabs only appear when you have the chart selected. So if you don’t see them, click your chart to select it). At the leftmost part of the ribbon on either of these tabs, there’s a section called “Current Selection”. In “Current Selection” there’s a dropdown menu. At this point, it probably says “Chart Area”.

Click this dropdown menu and select the series you would like to change – in this case, choose Series “Total Transactions ($)”.

STEP 4: Change the selected series’ chart type.

First, get to the “Design” tab on the ribbon. If you followed Option 1 above, you’re already there. If you followed option two, you’re either on the Chart Layout or Chart Format tab, so you’ll need to click the “Design” tab. Then click the left-most option on the ribbon “Change Chart Type”.

This will bring up the Chart Type Picker. Select the new type you’d like. In this case, select the Line chart and hit “OK”.

Now we’ve created a chart with two types (column and line). In fact, you can combine far more than two types by repeating the process with another series, and selecting a different type from the chart type picker.

Adding a Secondary Axis

Our chart is still difficult to analyze, because the scale of the Transactions is much larger than the scale of the Units Sold. As a result, we can barely read the Units Sold series and can’t gain any useful insight from the virtually indistinguishable columns. The Total Transactions should be moved to a secondary axis, allowing the series to be scaled differently.

STEP 5: Select the data series you wish to place on the secondary axis. In this case it’s the “Total Transactions” series. Do this exactly as you did in STEP 3.

STEP 6: Navigate to the Format or Layout tab if you’re not already there. (If you chose Option 2 in selecting the data series, you’re already there). Now, in the “Current Selection” section at the far left, make sure the dropdown selection reads “Series ‘Total Transactions’” and then click “Format Selection” (right below the dropdown).

STEP 7: Clicking Format Selection with the series selected will bring up the Formatting dialog. It will open to the Series Options tab with Primary Axis selected. Click the “Secondary Axis” radio button and then click “Close”.

You’ve now successfully added a second axis to your chart.

Finishing Formatting Touches

The following things are just finishing touches for your combination chart.

STEP 8: Move the legend to the bottom. Click the Chart Layout tab on the ribbon. Then click “Legend” in the middle of the ribbon. Choose “Show Legend at Bottom”.

STEP 9: Change secondary axis labels to display dollar signs. Right-click the axis label and select “Format Axis”.

Then click the Number tab at the left-hand side of the dialog that has just opened up (the Format Axis dialog). Then click “Currency” from the Category list. If you don’t like a decimal place and subsequent zeros, remove the two rightmost zeros and the decimal place from the “Format Code”. Then click “Add”. Finally, click “Close”.

STEP 10: Add axis titles. Go to the Layout tab on the ribbon and choose “Axis Titles”. From the dropdown, choose “Primary Vertical Axis”. Then choose whichever one you’d like – in this case I’d choose “Rotated Title”. Type in the title you’d like. Do the same for the Secondary Vertical Axis.

STEP 11: Add a chart title. Navigate to the Layout tab on the ribbon. Click “Chart Title” towards the left-hand side. Choose whichever title you’d like (I’ve chosen “Above Chart” for mine). Input your title.

Finally, you finish with the following combination chart:

By repeating the steps above with more series and different types, you can make even more complex charts.