How to Insert a Combo Pivot Chart in Excel: Workaround

Excel for Microsoft 365 on Windows does not let you combine chart types in a true PivotChart. To make the equivalent of a combo PivotChart, create a PivotTable, link a small helper range to its results, and insert a regular combo chart from that range. The chart can then reflect refreshed PivotTable values, while still allowing one series to appear as columns and another as a line.

What You’ll Need

Excel for Microsoft 365 on Windows, with a workbook containing the data you want to summarize. The steps also work in many recent desktop versions of Excel.
A source list with a header row and one record per row. For example, include Date, Region, Sales, and Units columns, with dates stored as real Excel dates and Sales and Units stored as numbers.
A clear idea of which measure should use columns and which should use a line. A secondary axis may help when the measures have very different scales.

Step-by-Step

Prepare the source data. Check that every column has a unique, descriptive heading, that there are no completely blank rows or columns inside the list, and that amounts and dates use consistent formats. Click a cell in the list and press Ctrl+T to turn it into an Excel table; confirm that My table has headers is selected and choose OK. A table makes it easier for the PivotTable source to include rows you add later. On Mac, use Command+T to create a table; the dialog may look slightly different.
Insert a PivotTable. Select a cell in the source table, then choose Insert > PivotTable > From Table/Range. In the dialog, confirm the table or range, choose New Worksheet, and select OK. Excel creates an empty PivotTable and opens the PivotTable Fields pane. If that pane is not visible, click inside the PivotTable and choose PivotTable Analyze > Field List.
Build the summary you want to chart. Drag a category such as Date into Rows. If you want month-by-month results, use a date column and group it by months: right-click a date in the PivotTable, choose Group, select Months (and Years if the data covers multiple years), and click OK. Drag Sales and Units into Values. Confirm that each is summarized as Sum, not Count: open the field’s menu in Values, choose Value Field Settings, select Sum, and click OK. If Sum is unavailable or Excel shows Count, check that the source values are genuinely numeric.
Make the PivotTable output easy to link. For this example, arrange the PivotTable so its category labels are in column A, summed Sales is in column B, and summed Units is in column C, with field headings in row 3 and the first category in row 4. Your actual locations may differ; use the cells where your own PivotTable displays those items. To avoid accidentally charting a total, choose PivotTable Design > Grand Totals > Off for Rows and Columns. For a clean category list, you can also choose PivotTable Design > Subtotals > Do Not Show Subtotals.
Create a helper range outside the PivotTable. In an unused area of the worksheet, add three headings: Month, Sales, and Units. In this example, put them in E3:G3. The helper range gives Excel an ordinary cell range to use for the combo chart; it is not itself a PivotTable. Keep it outside the PivotTable’s footprint so a refresh cannot overwrite it. Use the same order of categories as the PivotTable.
Link the first helper row to the PivotTable. With the layout described above—PivotTable category in A4, Sales in B4, Units in C4, and helper headings in E3:G3—enter =IF($A4="","",$A4) in E4. It shows the category from A4, or a blank if A4 is blank. In F4, enter =IF($A4="","",$B4); it returns the Sales value from B4 when a category is present. In G4, enter =IF($A4="","",$C4) to return the Units value under the same condition. These references are examples: adjust the column letters and row numbers to match your PivotTable.
Fill the helper formulas down. Select E4:G4 and drag the fill handle down for the number of category rows in your PivotTable. Avoid including a grand total. If you expect the number of categories to increase after a refresh, leave room for additional rows and fill formulas for those rows too. When the PivotTable grows beyond the rows covered by the helper formulas, extend the formulas and the chart’s source range as described in Troubleshooting.
Insert a regular combo chart. Select the populated helper range, including its headings, such as E3:G15. Choose Insert > Insert Combo Chart > Create Custom Combo Chart. In the dialog, set Sales to Clustered Column and Units to Line. If Excel offers a Secondary Axis checkbox for Units, select it when Units and Sales differ substantially in scale. Click OK. This creates a standard chart based on the helper cells, rather than a PivotChart.
Check the series and axes. Select the chart and use Chart Design > Change Chart Type > Combo if you need to revise the setup. Confirm that Sales is the column series and Units is the line series. A secondary axis puts Units on a separate scale at the right of the chart; use it only if it makes the comparison easier to read. Add axis titles and a clear chart title using Chart Design > Add Chart Element.
Refresh and verify the result. Change or add source data, then click inside the PivotTable and choose PivotTable Analyze > Refresh, or use Data > Refresh All. Check that the PivotTable values have updated and that the helper cells show the new values. The regular chart follows changes in the cells it already uses. If a refresh adds new categories, extend the helper formulas and the chart’s selected data range so those categories appear too.

Mac and web note: The chart commands can be arranged differently in Excel for Mac. Select the helper range and look under Insert for Combo, or select a chart and use Chart Design > Change Chart Type. Excel for the web may not expose the same custom combo-chart controls; if you cannot choose separate types or a secondary axis, open the workbook in desktop Excel.

Common Mistakes

Trying to change the PivotChart itself to a combo. Excel limits PivotChart chart types, so the combo option may be unavailable. Use the helper-range method instead of repeatedly searching the PivotChart menu.
Charting the wrong cells. Selecting the PivotTable may create a PivotChart, or selecting just the numeric columns may leave out the category labels. Select the helper headings and all three helper columns before inserting the combo chart.
Using Count instead of Sum. A Count of Sales or Units usually means Excel read some source values as text. Correct the source data, refresh the PivotTable, and verify Value Field Settings before charting.
Including a grand total or subtotal. Totals can appear as oversized extra bars or points and make the trend misleading. Turn off totals and subtotals for this layout, or carefully exclude those rows from the helper range.
Forgetting that the helper range has a fixed size. A chart will not automatically include newly added category rows if its linked range does not include them. Extend both the formulas and chart range when the PivotTable gains categories.
Adding a secondary axis without checking the scale. A second axis can make two measures easier to compare, but it can also exaggerate apparent movement. Label both axes and choose scales that represent the data clearly.

Tips

Use a line for a measure that shows a trend across an ordered sequence, such as monthly Units, and columns for totals such as monthly Sales. Avoid combining unrelated measures just because Excel allows it.
For more than one year of monthly data, include Years as well as Months when grouping dates. Otherwise, January from different years can be combined into one category.
Keep the helper range simple and visible while setting up the chart. Once the chart works, you can move the helper cells to a clearly labeled support sheet, then check that the chart still points to them.
After changing PivotTable filters or slicers, review the helper range and chart. They should reflect the visible summary, but category order and the number of displayed categories can change.
Use descriptive series names in the helper headings. Those headings normally become the chart legend labels, so names such as Sales and Units are clearer than generic labels such as Series 1.

Troubleshooting

Combo is missing or unavailable: You likely selected a PivotChart. Create the chart from the helper range instead. If you are in Excel for the web, open the file in desktop Excel to access the full custom combo controls.
The line or columns do not appear: Check that the helper formulas point to the correct PivotTable columns and that the source values are numeric. Then select the chart and choose Chart Design > Select Data to inspect the chart data range and series.
The chart shows a total as a category: Turn off PivotTable grand totals, or change the helper range so it ends at the last category row rather than including a total row. Recheck the chart source through Chart Design > Select Data.
New categories are missing after refresh: Fill the helper formulas down far enough to cover the new PivotTable rows. Then select the chart and use Chart Design > Select Data to expand the chart data range to include the additional helper rows.
Values stay old after editing the source: Refresh the PivotTable first. If the source is not an Excel table, check that the PivotTable source range includes the new records; use PivotTable Analyze > Change Data Source if necessary. Then confirm that the helper formulas point to the refreshed output.
The chart looks misleading: Check whether the line should use a secondary axis, add axis titles, and inspect the minimum and maximum values on both axes. If the scales make the visual comparison confusing, use separate charts instead.

Frequently Asked Questions

Can I insert a combo chart directly from a PivotTable?
Not as a true combo PivotChart in Excel for Microsoft 365 on Windows. Build the PivotTable, link its results to a regular worksheet range, and create the combo chart from that helper range.

Will the combo chart update when I refresh the PivotTable?
It will update for changes in the cells already linked through the helper range. If refresh adds categories beyond the existing formulas or chart range, extend both before expecting those categories to appear.

Can I use a secondary axis?
Yes. In the custom combo chart dialog, select Secondary Axis for the series that uses a very different scale. Add an axis title so readers can tell which values the second scale represents.

Why does my PivotTable show Count instead of Sum?
Excel may be treating one or more source values as text or blanks. Correct the source column so the values are numeric, refresh the PivotTable, and select Sum in Value Field Settings.

Can I use slicers with this setup?
Yes. A slicer can filter the PivotTable, and the helper formulas can reflect the displayed PivotTable results. After using a slicer, check that your helper range and chart include the categories you intend to show.

Is the helper-range chart still a PivotChart?
No. It is a regular combo chart linked to cells that reference PivotTable results. That distinction is what allows you to combine chart types while retaining a refreshable PivotTable summary.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.