Pivot Table Combo Chart: How to Build and Format One

In Excel for Microsoft 365 on Windows, you cannot directly turn a PivotChart into a combo chart. The practical workaround is to summarize the data in a PivotTable, link its results to a regular worksheet range, and create a standard combo chart from that range using Insert > Insert Combo Chart > Create Custom Combo Chart.

What a PivotTable combo chart means

A combo chart displays two or more data series using different chart types in one chart—for example, columns for monthly sales and a line for profit margin. A PivotTable can summarize and rearrange the source data by fields such as month, region, and product. Combining those ideas is useful when you want a chart to reflect a PivotTable’s filtering and grouping, but also need the flexibility of different chart types.

People often call the result a PivotTable combo chart even when the chart itself is not a PivotChart. That distinction matters in Excel. A PivotChart is directly linked to a PivotTable and inherits its field buttons and filtering behavior. A standard combo chart is built from a normal cell range. The workaround keeps the summary in a PivotTable but uses a separate, linked range as the chart’s data source.

For instance, a sales report might show revenue as blue columns and gross margin percentage as a line. The columns communicate the size of sales, while the line shows how profitability changes. Putting both series on one axis may make the margin line nearly flat, so a secondary axis can make the two measures readable.

Why a PivotChart cannot be a combo chart

Excel’s PivotChart tools do not offer the same combination of chart types as a regular chart. If you select a PivotChart and open the chart-type options, you will not find the normal custom combo-chart setup. This is a feature limitation, not a problem with the PivotTable’s data or a setting you have overlooked.

PivotCharts are designed to remain tied to the PivotTable structure. Their series and categories are driven by fields in the pivot, and their filters can be controlled by field buttons or slicers. A combo chart needs the chart author to assign a chart type and, when appropriate, a separate axis to each series. Excel does not provide that full combination of controls for PivotCharts.

Copying a PivotChart and changing its type does not reliably solve the issue: it remains a PivotChart. Instead, create a normal chart from cells outside the PivotTable. That chart can still update when its linked cells update, but it will not behave exactly like a PivotChart or acquire PivotChart field buttons.

Build a combo chart from PivotTable results

Start with a tidy source table: one header row, one record per row, consistent data types, and no blank rows or columns splitting the data. Convert it to an Excel Table with Insert > Table if the source is likely to grow. Then create and configure the PivotTable before building the chart.

Select a cell in the source data and choose Insert > PivotTable. Confirm the table or range and choose where to place the PivotTable.
In the PivotTable Fields pane, place a time field such as Month in Rows, and measures such as Revenue and Profit in Values. Add fields such as Region or Product to Filters, Columns, or a slicer as needed.
Check that each value field uses the correct summary. Select a value in the PivotTable, open Value Field Settings, and choose Sum, Count, Average, or another appropriate summary. Format the values as currency, whole numbers, or percentages as appropriate.
On a separate worksheet area, make a small helper table with the exact categories and series you want to chart. Link its category and value cells to the PivotTable results, or use GETPIVOTDATA where a stable field-based reference is more suitable. Do not include PivotTable subtotals or grand totals unless you intentionally want them charted.
Select the helper table, then choose Insert > Insert Combo Chart > Create Custom Combo Chart. In the dialog, choose a chart type for each series—for example, Clustered Column for Revenue and Line for Margin—and select Secondary Axis for the percentage series if the scales differ substantially.
Choose OK, then add a clear chart title, axis titles, number formats, and a legend that identifies each measure. Review the chart after changing a PivotTable filter to confirm the linked values still line up with the intended categories.

The exact combo-chart command can appear differently depending on the ribbon layout. Another route in Excel for Windows is Insert > Recommended Charts, then the All Charts tab and Combo. On Excel for Mac, use Insert > Recommended Charts and choose the Combo category where available; names and placement can vary slightly by version. Excel for the web has a more limited set of chart-editing controls, so desktop Excel is the safer choice when you need to assign chart types and axes precisely.

Choose series and axes carefully

Use a combo chart when the series answer related questions but have different visual roles or scales. A column series works well for amounts by category; a line can emphasize a trend or rate. The secondary axis is not automatically necessary just because the chart is a combo chart. If both measures use comparable units and ranges, one axis may be clearer and less likely to mislead.

Consider a report with monthly revenue of roughly $20,000 to $50,000 and a margin of 15% to 30%. Plotting both on one vertical scale can make the margin values visually indistinguishable. A secondary axis gives the percentage its own scale. Label both axes with units, such as Revenue ($) and Margin (%), and make the line’s color and markers distinct from the columns.

Be especially careful with percentages. Averaging transaction-level margins can produce a different answer from calculating total profit divided by total revenue. If the chart is meant to show the overall margin for each month, use the monthly total profit divided by monthly total revenue—not a simple average of row-level percentages. Depending on how the PivotTable is built, a helper calculation or a measure in the Data Model may be needed to calculate that ratio correctly.

Also avoid using a secondary axis merely to make a line look more dramatic. Different axis ranges can exaggerate or minimize apparent relationships. Show the axis scales, choose sensible minimum and maximum bounds, and explain the measures in the title or labels. If readers need to compare absolute values, separate charts or small multiples may be easier to interpret.

Keep the chart updated when the PivotTable changes

A regular chart updates when the cells in its source range change, but the helper range must continue to refer to the correct PivotTable results. Refresh the PivotTable with Data > Refresh or Data > Refresh All after its source data changes. If the source is an Excel Table, new rows are generally included when the PivotTable is refreshed; a fixed source range may need to be expanded or changed.

The most common maintenance issue is a PivotTable changing shape. Adding a new month, expanding a field, or changing the layout can move values to different rows or columns. Simple cell links may then point at the wrong measure or category. A helper range designed around a fixed list of categories can be more dependable, especially when its values are retrieved by field and item rather than by an assumed row position. Test the chart after changing filters, sorting, expanding dates, and refreshing.

GETPIVOTDATA can retrieve a value by naming the data field and PivotTable item, which may be more robust than pointing at a cell whose position can shift. It still depends on the field captions and items being correct, and it does not remove the need to handle categories that appear or disappear. For a compact report with a stable set of months, direct cell references may be simpler. For a frequently changing report, a carefully designed formula-driven helper table or a separate summary built from the underlying data can be safer.

If you add a slicer, connect it to the PivotTable as usual. The normal chart does not get PivotChart slicer controls of its own, but it can follow the PivotTable if its helper cells reflect the filtered results. If the helper range contains pasted values, it will not follow the slicer or refresh. This is why a linked helper range is preferable when interactivity matters.

Common problems and how to fix them

The chart includes Grand Total. Exclude the grand-total row and column from the helper range. Totals can distort the scale and appear as false categories.
The line or columns disappear. Check that the helper cells contain numeric values rather than numbers stored as text, and confirm that the chart series references the intended range.
Months appear in the wrong order. Use real Excel dates in the source data and group them appropriately in the PivotTable. Text labels such as Apr, Aug, Dec may sort alphabetically instead of chronologically.
The chart stops matching the PivotTable after refresh. Inspect the helper range for shifted references, newly added categories, and missing items. Adjust its design or use a field-based retrieval method.
The percentage line looks misleading. Confirm that the calculation represents the intended ratio, label the secondary axis, and avoid extreme axis bounds chosen only to amplify movement.

Another frequent misconception is that changing the PivotTable’s layout automatically changes the chart’s category labels. That is true only when the chart data source and helper cells still map to the same fields. A visual check after changes is worthwhile, especially in recurring reports.

When to use another approach

A standard PivotChart is a good choice when filtering and drilling into the PivotTable are more important than mixing chart types. It supports PivotTable interactions directly and is often easier to maintain for straightforward bar, column, line, or pie views. If you need a combo chart only for a one-time presentation, copying the PivotTable summary and pasting values into a separate range is quick, but the chart will not reflect later refreshes.

For a report that must refresh regularly, consider a formula-driven summary table or a Data Model measure as the chart source. A regular chart built from that summary supports combo-chart formatting while keeping calculations explicit. Power Query can also help prepare a clean summary table when the source needs repeatable transformation. These options require more setup than a simple PivotChart, but can be easier to audit when the PivotTable layout changes often.

The right choice depends on what should remain interactive. Choose a PivotChart for direct pivot filtering and a standard chart for chart-type flexibility. Use the linked-helper workaround when you want PivotTable summaries and combo-chart formatting together, and test its links as the report evolves.

Frequently Asked Questions

Can you make a combo chart directly from a PivotTable in Excel?
No. In Excel for Microsoft 365, a PivotChart cannot be configured as a standard combo chart. Create a linked helper range from the PivotTable results and insert a regular combo chart from that range.

How do I add a secondary axis to a PivotTable combo chart?
Build a regular combo chart from linked cells, open the custom combo-chart setup, and select Secondary Axis for the series that needs a separate scale. Label both axes so readers can tell which units each series uses.

Will a combo chart update when I refresh the PivotTable?
It can, provided the chart’s source helper cells are linked to the PivotTable and still refer to the right results. A chart made from pasted values will not update, and a changed PivotTable layout can break cell references.

Can a slicer control a regular chart based on a PivotTable?
Not directly as a PivotChart control. A slicer can filter the PivotTable, and a regular chart can reflect the change if its linked helper cells update with the filtered results. Static pasted values will not respond.

Should I put a percentage series on a secondary axis?
Use one when the percentage and amount have substantially different scales, and label it clearly. First make sure the percentage is calculated appropriately—for example, total profit divided by total revenue when showing overall margin—rather than averaging row-level percentages by default.

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.