Create a Combination Chart in Excel
What is a Combination Chart?
A combo chart, or combination chart, uses different types of chart in one. For example, it can be a column and line, or column and area chart.
A combination chart is used to highlight different types of information, or to display different types of data such as currency and percentage on one chart.
Take the table below for instance. We could use a combo chart to display the number of sales and conversion ratio of our sales force.
Create the Column Chart
The first step is to create the initial column chart.
Select the data you want to use.
Click the Insert tab on the Ribbon, Click Column, and select the 2D Clustered Column chart.
The column chart is created. Because the number of sales and conversion ratio data are so different from each other, they cannot share the same axis.
The conversion ratio column is barely visible on the chart and it looks terrible. A secondary axis should be created for the conversion ratio data.
Plotting Data on a Secondary Axis
We will plot the conversion ratio data on a secondary axis. If each data series has its own axis, they can co-exist on one chart.
- Click the Layout tab under Chart Tools on the Ribbon.
- Select the data series you want, in this example Series “Conversions”, from the Chart Elements list and click Format Selection.
- Click the Plot Series on Secondary Axis option and click Close.
The conversion ratio data series is now visible although it stands in front of the sales data series. A secondary axis on the right is used to plot the conversion data, whilst the primary axis on the left continues to plot the sales data.
Change Series Chart Type
The final step will be to change the chart type of the conversion ratio data series. This can be changed to a line or area chart to easily compare both sets of data on one chart.
Right mouse click one of the conversion ratio columns and select Change Series Chart Type from the menu.
Select the chart type you want to use. In this example I have used an area chart.
The conversion data series is changed and our chart is finished. Further modifications can be made if felt necessary to improve the clarity of the chart data.
Watch the Video
More by this Author
Form controls can be added to an Excel spreadsheet to create interactivity with the user. This article looks at using option button controls to allow a user to choose the data they want to see on a chart. The first...
If you are using Microsoft Project to schedule and track your projects, at some point you will probably want to export your project data into Excel. Excel can then be used to analyse and summarise your project data...
Although Excel’s greatest strength is its ability to analyse huge amounts of data, it is also great for creating forms. We can create formulas, apply validation rules and use techniques to make our forms user...
No comments yet.