"Justin" wrote in message:
I have created a column chart and now I want to plot an x-y scatter chart on top of it, but I do not know how to have both of those plot styles in one chart.
OK, lets see...
A combination chart is a chart that combines two or more chart types in a single chart -- for example, a chart that has a line series and a column series. When you choose the Custom Types tab in the first step of the Chart Wizard, you'll find the following combination charts listed:
- Column - Area
- Line - Column
Many users think that these are the only two combination charts that are possible. In fact, you have a great deal of flexibility in creating combination charts. They key is understanding how the Chart - Chart Type command works. When you select this command, it works in either of two ways:
If a series is selected in the chart, the chart type that you choose applies only to the selected series.
Or...
If anything other than a series is selected, the chart type that you choose applies to all series in the chart.
Now, Let's Try....
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.
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”.
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.
Or we can do other way, just like Jon Peltier, Microsoft Excel MVP, says in short:
"Add the data for the second series. I like to copy the range, select the chart, and use Paste Special to add the data as a new series. Right click the new series, which was added as a column type, select the Chart Type item from the pop up menu, and in the dialog, select the chart type you want to change the series to. "
Note:
Not all chart types can be used in a combination chart. For example, Excel does not allow any of the 3-D chart types to be used in a combination chart.
The figure below carries this concept to the extreme. This combination chart displays five series, and each series uses a different chart type. This chart, of course, is provided for demonstration purposes only. I can't think of any situation that would warrant such a confusing chart!
Good Luck*)
