Create combination chart in Excel
A combination chart is a type of chart which combines two or more different charts in Excel, the most common combination chart is a combination of column chart and line chart as below screenshot shown. With the help of this type chart, you can display two different data sets together. When the values from one series is too large or small compared to the other data, this combination chart also can do you a favor. This article, I will talk about how to create a simple and a dynamic combination charts in Excel.
- Create a simple line and column combination chart in Excel
- Create a dynamic combination chart with check box in Excel
- Download Combination Chart sample file
Create a simple line and column combination chart in Excel
It is easy for us to create a simple line and column combination chart in Excel, please do as this:
1. Select the data range that you want to create chart based on, and then click Insert > Insert Column or Bar Chart > Clustered Column, see screenshot:
2. And then, a chart has been inserted into the sheet, delete the unwanted elements you need, such as the chart title, gridlines, see screenshot:
3. Then, right click any data bar in the chart, and choose Change Series Chart Type from the context menu, see screenshot:
4. In the popped out Change Chart Type dialog box, in the Choose the chart type and axis for your data series list box, select one data series that you want to display it as line, and then select one line chart type you like from the Chart Type drop down list, and then check the check box under Secondary Axis section, see screenshot:
5. Then, click OK to close the dialog box, and the line and column combination chart has been created as below screenshot shown:
Create a dynamic combination chart with check box in Excel
Supposing, you have the total orders of the products in year 2018, 2019 and 2020, now, you want to compare the total orders from this year(2020) with previous years (2018, 2019). May be the below dynamic combination chart can help you, checking the check box to display the line chart to compare with the column chart.
To create this dynamic combination chart by using check box, please do with the following steps:
First, insert the check boxes into the sheet
1. First, please insert two check boxes, click Developer > Insert > Check Box (Form Control), and then draw two check boxes, and then format the text as your need, see screenshots:
 |
 |
 |
2. Right click the first check box, and choose Format Control, see screenshot:
3. In the Format Object dialog box, under the Control tab, select a cell to link the check box, in this example, I will select cell B6, see screenshot:
4. Repeat the above two steps for linking another cell (B7) to the second check box.
Second, insert helper data to create the chart based on
5. After creating the check boxes, then you should insert three helper rows.
5.1). Please enter the below formula into cell B10, and then drag the fill handle to right side for getting the formula result:
5.2). Go on enter the below formula into cell B11, and drag the fill handle to the right cell for apply this formula:
5.3). Then enter the below formula into cell B12, and then copy to fill this formula to other cells in the row, this will get the values of year 2020 from the original data:
Third, create the chart based on the helper rows data
6. Select the helper data, and then click Insert > Insert Column or Bar Chart > Clustered Column to insert a chart as below screenshot shown:
7. Right click any data bar of the chart, ad then choose Change Series Chart Type, see screenshot:
8. In the opened Change Chart Type dialog box, choose line chart type you need from the Chart Type drop down for 2018 and 2019 data series, see screenshot:
9. Then, click OK to close the dialog box, now, the dynamic combination chart has been created successfully as below demo shown:
Download Combination Chart sample file
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
