Note: The other languages of the website are Google-translated. Back to English
Log in  \/ 
x
or
x
Register  \/ 
x

or

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

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:

=IF($B$6,B2,NA())
Note: In the above formula, B6 is the cell linked to the first check box, and B2 is the cell that you want to extract the value.

5.2). Go on enter the below formula into cell B11, and drag the fill handle to the right cell for apply this formula:

=IF($B$7,B3,NA())
Note: In the above formula, B7 is the cell linked to the second check box, and B3 is the cell that you want to extract the value.

Tips: The above two formulas are used to return the values from original data if the check box is checked, if it is unchecked, #N/A value will be displayed.

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:

=B4

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

Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300 powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) and save 80% time for you.

  • Designed for 1500 work scenarios, helps you solve 80% Excel problems.
  • Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
  • Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
  • 30-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years.
Ribbon of Excel (with Kutools for Excel installed)

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, Firefox, And New Internet Explorer.
Screen Shot of Excel (with Office Tab installed)

Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.