How to create a combo chart in excel

How to create a combo chart in excel

A combo chart in Excel displays two chart types (such as column and line) on the same chart. They are used to show different types of information on a single chart, such as actuals against a target.

In this article, we’ll demonstrate how to make a combo chart that follows the same axis and one that displays mixed types of data in a single chart on a different axis.

Insert a Combo Chart with a Single Axis

In the first example, we will create a combo chart to show monthly revenue against a target using the sample data below.

How to create a combo chart in excel

You can see that the target value is the same each month. The result will show the data as a straight line.

To get started, select the range of cells you want to chart—A1:C13 in this example. Next, click Insert > Insert Combo Chart. Select “Clustered Column – Line.”

How to create a combo chart in excel

The combo chart is inserted with both the column and line using the same axis. Easy as that!

How to create a combo chart in excel

You can make further improvements to the chart now, like changing the chart title. Click on the chart title box and start typing to replace the words “Chart Title” with something more useful. As you type, the text will appear in the formula bar above.

How to create a combo chart in excel

Press the Enter key, and Excel saves the typed text as the chart title.

How to create a combo chart in excel

Insert a Combo Chart with Two Axes

Using the sample data shown below, let’s create a combo chart to show the monthly revenue and the ad budget on the same chart.

How to create a combo chart in excel

Select range A1:C13. Click Insert > Combo Chart. Choose the “Clustered Column – Line on Secondary Axis” chart.

How to create a combo chart in excel

The inserted chart looks like this.

How to create a combo chart in excel

Change an Existing Chart to a Combo Chart

We have looked at two examples of creating a combo chart from spreadsheet data, but knowing how to edit an existing chart can also be useful.

Below is a clustered column chart created from the revenue and ad budget data.

How to create a combo chart in excel

The chart has one axis, and you can barely see the ad budget columns on the chart. Let’s change this to a combo chart by creating a secondary axis for the ad budget data and changing its chart type to a line.

To begin, right-click on the data series you want to change (ad budget in this example). Next, select “Change Series Chart Type.”

How to create a combo chart in excel

Now, check the “Secondary Axis” box for the data series you want to create an axis for. Select Line from the “Chart Type” list for that data series.

How to create a combo chart in excel

The chart is changed to a combo chart.

How to create a combo chart in excel

You can then make other improvements to the combo chart, such as editing the chart title or labeling the axis.

How to create a combo chart in excel

A combo chart in Excel is a chart that displays multiple sets of data in different ways on the same chart. Many kinds of data can be combined into one chart. You could even represent the same data in various ways on a single chart.

For example, you may want to combine a bar graph with a line graph in the same chart.

How to create a combo chart in excel

A combination chart displaying annual salary and annual total debt.

For a refresher on making standard graphs and charts in Excel, check out this helpful article: How to Create Graphs in Excel

Making a Combo Chart

It’s easy to make a combination (combo) chart!

    Begin by selecting a range of data in your spreadsheet. Select multiple columns of data (or series) to build a combo chart.

For example, to create the chart shown above, you would first select the rightmost two columns of data, entitled “Salary (in $)” and “Total Debt.”

If you select all three columns, then the chart function will assume that you want to graph the years, 2000, 2001, …, 2018, as values in the chart. (Keep in mind, you can always go back and edit the chart so that it uses “Years” as horizontal axis labels.)

How to create a combo chart in excel

Go to the instert tab and click Combo Chart from the Charts group.

  • Now you may select one of three common formats, or choose to “Create Custom Combo Chart.” Try out a few designs until you find one that you like.
  • Combo Charts With Three Series

    Now let’s go back to our salary and debt chart and add another series of data. Suppose you want to track your savings as well.

    First, create a new column for Savings.

    How to create a combo chart in excel

    Then, select the rightmost three columns (Salary, Total Debt, and Savings).

    This time, let’s create a custom combo chart. Now you will see a dialog box with a number of different options.

    How to create a combo chart in excel

    Because the savings amounts are so much smaller than either salary or debt, I’ve decided to lump salary and debt together on the primary axis (left), and to put savings on the secondary axis (right). The checkboxes under Secondary Axis allow me to move the data to primary or secondary axis.

    I’ve also selected “Line with Markers” for both Total Debt and Savings. Experiment with different formats for different kinds of data. The sky’s the limit!

    Finally, after choosing the desired options, then you can create the chart. As before, you might change the horizontal axis labels to show the years. Try out a few different designs for best effect — here is a snazzy dark background and shadowed effects on the lines.

    How to create a combo chart in excel

    Summary

    As you can see, it’s not difficult to make combo charts. Remember that you will need at least two columns (series) of data. Don’t forget to try out the various options and designs to really spice up your documents!

    To emphasize different kinds of information in a chart, you can combine two or more charts. For example, you can combine a line chart that shows price data with a column chart that shows sales volumes.

    How to create a combo chart in excel

    Note: To create a combination chart, you must use a 2-D chart, such as a 2-D Line, 2-D Column, Scatter, or Bubble chart.

    Note: To complete this procedure, you must have an existing chart. For more information about how to create a chart, see Create a chart from start to finish.

    Do one of the following:

    To change the chart type of the whole chart, click the chart area or plot area of the chart to display the chart tools.

    Tip: Alternatively, right-click the chart area or plot area, click Change Chart Type, and then continue with step 3.

    To change the chart type of a data series, click that data series.

    Note: You can change the chart type of only one data series at a time. To change the chart type of more than one data series in the chart, repeat the steps of this procedure for each data series that you want to change.

    This displays the Chart Tools, adding the Design, Layout, and Format tabs.

    On the Design tab, in the Type group, click Change Chart Type.

    In the Change Chart Type dialog box, click a chart type that you want to use.

    The first box shows a list of chart type categories, and the second box shows the available chart types for each chart type category. For more information about the chart types that you can use, see Available chart types.

    Note: To complete this procedure, you must have an existing chart. For more information about how to create a chart, see Create a chart from start to finish.

    This step applies to Word for Mac only: On the View menu, click Print Layout.

    In the chart, select a data series. For example, in a line chart, click one of the lines in the chart, and all the data marker of that data series become selected.

    On the Chart Design tab, click Change Chart Type and then click the 2-D chart that you want to add to the existing chart.

    How to create a combo chart in excel

    Note: To combine charts, you should have more than one data series.

    A Combo Chart in Excel is a built-in chart type. It will compare two different data sets that are related to each other. In other words, a combo chart is a combination of two more chart types. For example, a bar chart with a line chart. In this article, we are going to explore how to Create a Combo Chart in Excel Office 365.

    To know more different types of Excel Charts , refer to this link.

    Jump To:

    • Create a Combo Chart
    • Pros and Cons
    • End of the Article

    Create a Combo Chart:

    • To do this, follow the below steps.
    • You need to select the input range that you want to create a chart.

    Input data

    • Go to the Insert Tab, select the Insert Combo Chart icon.
    • Then, choose the Combo Chart option from the drop-down menu.

    Insert Tab

    • Finally, you will get the result as shown in the below image.

    Pros and Cons:

    • This chart is able to display multiple chart types in a single chart.
    • It can plot values in different scales.
    • More complex when compared to other chart types.

    End of the Article:

    In the above article, you can easily understand the steps to Create a Combo Chart in Excel Office 365 with a clear-cut example. Don’t forget to share your feedback in the comment section. Thanks for visiting Geek Excel. Keep Learning!

    Combination charts in Excel let you present and compare two different data-sets that are related to each other (in a single chart).

    When you create a regular chart in Excel, it usually has only one X-axis and one Y-axis. But with combination charts, you can have two Y-axis, which allows you to have two different type of data points in the same chart.

    For example, you may be interested in plotting the annual revenue numbers of a company, and at the same time, also be able to show how the profit margin has changed.

    A combination chart (as shown below) is a good way of doing this in Excel.

    How to create a combo chart in excel

    You can see that a revenue number are in thousands and are way higher than the profit margin numbers (which is in %). By adding a secondary Y-axis, we can plot the profit margin numbers separately (and still be able to plot both in the same chart).

    Creating a Combination Chart in Excel 2013/2016

    Suppose I have the data set as shown below and I want to plot both the revenue and profit margin numbers in the same chart.

    How to create a combo chart in excel

    To create this combination chart, I first need to create a regular chart where I have all the above data plotted on it.

    Below are the steps to create a regular chart using the above data (the snapshots are of Excel 2016):

      1. Select the Revenue and Profit Margin data (B1:C6 in this example).

    How to create a combo chart in excel

    How to create a combo chart in excel

    How to create a combo chart in excel

    How to create a combo chart in excel

    How to create a combo chart in excel

    • Select years that you want to plot in the chart (A2:A6 in this example).
    • Click OK.

    The above steps would give you a chart that has revenue and profit margin plotted as a clustered column chart. If you can’t see the Profit Margin bar in the chart, it’s because the value of Profit Margin is very less as compared to the Revenue value (but it’s there as we can see it’s listed in the legend in orange color).

    How to create a combo chart in excel

    Now to create a combination chart from this clustered column chart, follow the below steps:

      1. Right-click on any of the bars.

    How to create a combo chart in excel

    How to create a combo chart in excel

    How to create a combo chart in excel

    How to create a combo chart in excel

    The above steps would give you the combination chart (also called combo chart) as shown below.

    How to create a combo chart in excel

    You can now further customize the chart (such as change the title, remove the grid lines, etc.)

    Another good use case of creating combination charts is when you have to show actual vs target values in the same chart. You can create a simple clustered column chart, or create something more fancy by converting one bar into markers. Click here to read how to create an Actual vs Target combination charts in Excel.

    Creating Combination Charts in Excel 2010

    While the first part of creating a chart is the same in all versions of Excel, converting that chart into a combination chart is done a bit differently in Excel 2010.

    Below are the steps to convert a regular clustered column chart into a combo chart in Excel 2010:

    1. Click on any of the Profit margin bars. This will select all bars for profit margin.
    2. Right-click and select Format Data Series.

    How to create a combo chart in excel

    1. In the Format Data Series dialogue box, select Secondary Axis (in the Plot Series On group)
      • This will plot Profit Margin Data in a secondary Axis. You would be able to see a vertical axis on the right of the chart.

    How to create a combo chart in excel

    1. Right-click and select Change Series Chart Type

    How to create a combo chart in excel

    1. In the Change Chart Type dialogue box, select Line with Markers option.

    How to create a combo chart in excel

    1. That’s It. You would have the chart. Format it the way you want.

    How to create a combo chart in excel

    Combo charts combine more than one Excel chart type in the same chart. One way you can use a combo chart is to show actual values in columns together with a line that shows a goal or target value. In the chart shown in this example, daily sales are plotted in columns, and a line shows target sales of $500 per day.

    This example uses a combo chart based on a column chart to plot daily sales and an XY scatter chart to plot the target. The trick is to plot just one point in the XY scatter chart, then use error bars to create a continuous line that extends across the entire plot area.

    The data used to build this chart is shown below:

    How to create a combo chart in excel

    How to create this chart

    1. Select the sales data and insert a Column chart
      How to create a combo chart in excel
    2. Select target line data and copy. Then select chart > paste special:
      How to create a combo chart in excel
    3. Column chart after pasting target line data:
      How to create a combo chart in excel
    4. Right-click chart, then change chart type to Combo Chart:
      How to create a combo chart in excel
    5. Make Target Series an XY Scatter Chart
      How to create a combo chart in excel
    6. Select target data point, then add error bars to chart
      How to create a combo chart in excel
    7. Select X (horizontal) error bar; set Fixed value = 1.
      How to create a combo chart in excel
    8. Select Y error bars, then press delete to remove:
      How to create a combo chart in excel
    9. Current chart after adjusting X error bars and removing Y error bars:
      How to create a combo chart in excel
    10. Select secondary value axis, then set Maximum bounds = 1, and Label Position = “None”:
      How to create a combo chart in excel
    11. Select and delete secondary vertical axis.
    12. Select horizontal error bar and set end style = “No Cap”:
      How to create a combo chart in excel
    13. Set Target data series marker to “None”:
      How to create a combo chart in excel
    14. Current chart with sales in columns and target as edge-to-edge line :
      How to create a combo chart in excel

    From this point, you have the basic column chart with edge-to-edge target line.

    You can now format the chart as you like: add a title, set color and width for the target sales line, add data labels, etc.

    Note: I learned the error bar approach from Excel chart guru Jon Peltier.

    After you have created your Microsoft ® Excel ® spreadsheet, you can visually represent the worksheet data by creating a chart. Charts often make your data clearer and easier to understand.

    If you find that the data is not in sequence or in a group of cells, you may need to create your own series, specifying the labels and values. Data can be extracted from different worksheets and even workbooks.

    TIP: To create a quick chart, select any cell within the data range and press F11.

    In this tip, we are going to create a combination chart from scratch showing the difference between the Sales and Purchases.

    Note: Download the workbook to practice

    Applies To: Microsoft ® Excel ® 2007,2010 and 2013

    1. Open the practice workbook.
    2. Create a blank line chart:

    a. Select any blank cell on the worksheet.
    b. From the Insert tab, in the Charts group, select Line.
    c. Select 2-D Line.

    How to create a combo chart in excel

    3. Add data to the chart:

    a. Select the blank chart.
    b. From the Design tab, in the Data group, select Select Data.

    How to create a combo chart in excel

    1. To create the Sales series:
      a. Select the Add button in the Legend Entries (Series)
      i. Click in the Series name text box and select cell A4(Series name).
      ii. To highlight the data range, click in the Series values text box.
      iii. Clear the contents and select the range C5:C16.b. The Edit Series box should contain the following:

    How to create a combo chart in excel

    c. Select OK.

    1. To create the Purchases series:
      a. Select Add.
      i. Click in the Series name text box and select cell A21(Series name).
      ii. To highlight the data range, click in the Series values text box.
      iii. Clear the contents and select the range C22:C33.b. The Edit Series box should contain the following:

    How to create a combo chart in excel
    c. Select OK.

    1. Select the Edit button in the Horizontal (Category) Axis Labels.
      a. To display the months on the horizontal line, highlight the data range A5:A16 or A22:A33.
      b. Select OK.

    7. Add a chart title:
    a. Select the chart as below.

    How to create a combo chart in excel
    b. Enter the heading as Sales Vs Purchases.

    8. Move the chart to a new worksheet:
    a. From the Design tab, in the Location group, select Move Chart.
    b. Select New Sheet and name the worksheet as shown below.

    How to create a combo chart in excel
    c. Select OK.

    9. Right click on the Sales series.
    10. Select change series chart type.
    11. Select the down arrow under chart type at the bottom of the window.
    12. Select Clustered column and select OK.

    How to create a combo chart in excel

    You can place charts in different worksheets to make it easier for you to analyse your information. The chart that we have created compares Purchases to Sales, which can quickly help you with decision making.

    This post is brought to you by Robin Wakefield, a Program Manager in the Excel Team.

    Have you ever had two different types of data that you wanted to show in one chart? For example you may want to plot actual revenue with forecasted revenue to compare the two values.

    In an earlier post, we showed you how to create combination charts in Excel 2010, but we have revamped this experience as a part of Excel 2013 to make it much easier to create combo charts. I am going to walk through the same scenario used in that article so you can see the differences.

    How to create a combo chart in excel

    Microsoft Excel

    Turn data into insights.

    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 revenue generated. I’m hoping to identify trouble spots, such as high unit sales that result in low revenue, which may in turn indicate that the units are being sold too cheaply. I would like to create a single chart like the one below so I can see both data sets in one view.

    Combining different chart types and adding a secondary axis

    To follow along, use this sample workbook.

    1. Select the data you would like to use for your chart.

    2. Go to the Insert tab and click Recommended Charts.

    3. Click the All Charts tab and select the Combo category.

    At the top of the dialog you will see a couple pre-canned combo charts to get you started and Clustered Column – Line is the default. This combo chart will split the series 50/50 between a clustered column and a line chart. You can then use the table below the chart to change each series to a particular chart type and move series to the secondary axis.

    Given that units sold and total transactions have different scales we need to move one of them to a secondary axis so we can clearly see the values for both.

    4. Check the Secondary Axis box for the Total Transactions and click OK.

    Your chart should now look like this:

    As you can see it now takes four steps to create a simple combination chart instead of the eleven steps outlined in the earlier post!

    Finishing touches

    Now we can walk through the finishing touches to make your combo chart look more professional.

    There are a couple steps in the previous post that are no longer necessary since they are the new defaults for 2013:

    • Legend is already on the bottom.
    • Chart title is already added to the chart and all you need to do is edit the text.

    So the two steps left are to add axis titles and format the Total Transactions axis as $.

    Add axis titles

    1. Click the Add Chart Elements button next to the chart.

    2. Check Axis Titles.

    3. Edit the axis title text

    Format total transactions axis as $

    1. Right click on the secondary axis (Total Transactions) and choose Format Axis.

    2. Expand the Number category.

    3. Set the Category to Currency and the Decimal Places to zero.

    And now your chart is completed.

    Let us know what you think

    I think you will find that creating combo charts and modifying them to get to your final polished output is much easier now. Let us know what you think in the comments below!

    –Robin Wakefield, Excel Program Manager

    After you create a column chart from a pivot table, you might want to change it so the chart is a combination chart type. You’d like most of the series to remain as columns, and one of the series to be a line.

    In Excel 2007 there are no Combination Chart types that you can choose, as there were in the Excel 2003 Chart Wizard. However, in any version of Excel you can create your own combination charts.

    In this example, the chart is a Clustered Column chart type, with the series showing the sales of each category in each city.

    How to create a combo chart in excel

    You’d like to change the Cookies series to a line, so it stands out from the other categories.

    In the pivot chart, right-click on one of the Cookies columns.

    In the shortcut menu that appears, click Change Series Chart Type

    How to create a combo chart in excel

    In the Change Chart Type dialog box, click the Line chart type, and click one of the Line subtypes, then click OK.

    How to create a combo chart in excel

    The chart is now a combination chart, with columns for Bars, Crackers and Snacks, and a line for Cookies.

    How to create a combo chart in excel

    For more information on pivot tables, see the Pivot Table Topics on my Contextures web site.

    10 thoughts on “Create a Combination Pivot Chart”

    How do you create two pivot tables on spreadsheet/worksheet tab. I had bought a couple of books but it never talks about being able two pivot tables on one worksheet tab. Thanks Kelly

    🙂 SPENT OVER 2 HOURS RESEARCHING THIS ON 15-20 SITES…AND, IT WAS THIS SIMPLE…?

    Les, you’re welcome! Thanks for letting me know that it helped.

    After a long research i found the answer here… Tnx n Good Luck 🙂

    Is it possible to make the line portion the chart have a separate axis? For instance, the bars range from 0-5million but the line ranges from 1-10?

    Hi. Thanks for the guide.
    Just tried it out, but as soon as I change the selection on the pivot table, it will switch to the originally type of chart again. – In your case it woul be the cookie that is no longer a line.
    How can you ‘save’ the format for each different value of a pivot table?

    Best regards, Athene

    I’m having the same issue as Athene above. I create a combination pivot chart (based off a pivot table). I change one of the data series to a line chart while the other series remain displayed as a bar chart. As soon as I select/de-select data series’ to be displayed, the line chart I formatted reverts to a bar chart. Is there a way to prevent this from happening?

    Hi, I am having the same issue. Have you found a resolution for this? Thanks.

    Is it possible to have a pivot chart with multiple chart types and slicers? I have a column pivot chart with one series as a line graph. When I select a new option from the slicer, the chart type for that series reverts back to the original, a column chart. Is there a way to maintain the combination pivot chart with slicers?

    How do I make sure than when refreshing the combo settings are not lost? It happens every single time!
    A big hug to whomever can help
    Carmen

    Leave a Reply Cancel reply

    This site uses Akismet to reduce spam. Learn how your comment data is processed.