Excel Pivot Chart Secondary Axis: The Workaround

In Excel for Microsoft 365 on Windows, you generally cannot put a native PivotChart series on a true secondary axis because PivotCharts do not support combo charts. The reliable workaround is to make a regular chart from a separate range containing the PivotTable results, then use a combo chart and assign the differently scaled series to a secondary axis.

Why PivotCharts Have This Limitation

A PivotChart is tied to its PivotTable: its fields, filters, and summarized values are controlled by the pivot layout. That connection makes it useful for exploring data, but it also restricts some chart features. In particular, Excel does not provide the usual combo-chart setup for a native PivotChart, where one series can be a column and another a line plotted against a second value axis.

This matters when the measures have very different scales. Imagine a PivotTable showing monthly revenue of $10,000 to $50,000 and a conversion rate of 2% to 8%. Plotting both on one value axis can make the rate line appear almost flat. A second axis gives the rate its own scale, so both patterns can be seen. It does not change the source values or create a mathematical relationship between revenue and rate; it changes how the chart displays them.

The limitation is about the PivotChart itself, not PivotTables in general. You can still use the PivotTable to summarize and filter data, then chart its results with a separate, ordinary Excel chart. That chart has more formatting freedom, including combo types and a secondary axis.

What a Secondary Axis Does

A standard chart normally plots all value series against one vertical axis. A secondary axis adds a second vertical scale on the opposite side of the chart. Each axis can have its own units and bounds. For example, revenue can use the left axis in dollars while conversion rate uses the right axis in percentages.

Use a secondary axis when two meaningful measures differ substantially in scale or units, not simply to make every series look more prominent. If both series are revenue amounts in similar ranges, one axis is usually easier to interpret. If one is units sold and the other is a percentage, separate scales can be appropriate, but label them clearly so readers do not mistake the two lines or bars as directly comparable in height.

A secondary axis can also mislead if its minimum and maximum are chosen to make two series appear to move together. Keep the axis labels visible, use descriptive titles, and explain the measures. The chart should reveal a useful comparison without suggesting that different units share one scale.

Create a Regular Combo Chart from PivotTable Results

First decide which PivotTable fields and measures the chart needs. Refresh the PivotTable if its source data has changed, and make sure the visible results have clear headings and a sensible category order. Then build the chart from a separate range rather than inserting a PivotChart from the PivotTable.

Prepare a staging range. Copy the PivotTable headings, category labels, and values needed for the chart to an unused area or a new worksheet. Use Home > Paste > Paste Special > Values if you want a snapshot. Keep the category labels in the first column and put each value series in its own column.
Select the staging data. Include the headings and category labels. Check that the first row contains meaningful series names, such as Revenue and Conversion rate, and that each data column contains numeric values.
Insert a combo chart. On the Windows ribbon, select Insert > Insert Combo Chart > Create Custom Combo Chart. If you do not see that option, insert a regular chart first, then use Chart Design > Change Chart Type and choose Combo.
Choose a chart type for each series. A common arrangement is Clustered Column for revenue and Line for the percentage measure. In the combo chart settings, select Secondary Axis for the series that uses the different scale, then confirm the chart.
Label and check the axes. Add a chart title and axis titles where needed. Confirm that the left and right axis labels match the units in the data, and that percentages are displayed as percentages rather than unexplained decimals.

You can also select a series in a regular chart, right-click it, choose Format Data Series, and under Series Options select Secondary Axis. The exact pane layout can vary slightly with Excel updates, but the option applies to a series in an ordinary chart. It does not turn the original PivotChart into a supported combo chart.

Choose Which Series Belongs on the Second Axis

Usually, put the series with the smaller numerical range or different unit on the secondary axis. In the revenue and conversion example, revenue often stays on the primary axis as columns, while conversion rate becomes a line on the secondary axis. This pairing helps readers distinguish the measures as well as read their scales.

Before choosing, check the data itself. A rate stored as 0.06 may mean 6%, while a rate stored as 6 may mean 6 percentage points, depending on how the source was prepared. Format the cells and the chart axis consistently. Do not multiply or divide values just to make the line look right unless the data definition actually calls for that conversion.

Keep related measures on one axis when their units and ranges make a single scale readable.
Use the secondary axis for a measure with a substantially different scale or unit.
Choose distinct chart types, such as columns and a line, when that improves clarity.
Title both axes or otherwise identify the units, especially when the chart may be viewed without its source table.

There is no universal rule that percentages must always use the secondary axis. If a percentage and another measure can be understood together on one scale, a second axis may add unnecessary complexity. The right choice depends on the question the chart is meant to answer.

Keep the Chart Current When the PivotTable Refreshes

A chart made from pasted values is a snapshot. Refreshing the PivotTable will not automatically replace those copied values, so the chart can become outdated. This is the most important trade-off in the workaround: the ordinary chart is more flexible, but its data connection is no longer the PivotChart connection.

For an occasional report, copying the refreshed PivotTable results into the staging range may be enough. For a recurring report, link the staging cells to the PivotTable with worksheet references or formulas, or use GETPIVOTDATA to retrieve specific results. A linked staging area can update when the PivotTable values change, and the ordinary chart can continue to use that area as its source.

Plan for changes in the PivotTable layout. A fixed chart range may omit new categories added below it or include blank rows after categories disappear. GETPIVOTDATA can retrieve a named item reliably while that item exists, but it does not automatically create a new staging row for every newly appearing category. Depending on the report, you may need to extend the chart range or update the staging layout after refresh.

If users filter the PivotTable with a report filter or slicer, linked staging cells may reflect the visible PivotTable results, but a pasted snapshot will not. Test the complete refresh-and-filter routine before distributing the workbook. If the chart must respond to changing categories and filters with minimal maintenance, consider whether a standard chart built from a deliberately structured summary range is a better fit than a direct PivotChart.

Formatting and Troubleshooting

If the combo option is missing, first confirm that the chart is an ordinary chart based on the staging range, not a PivotChart. Selecting cells inside a PivotTable and inserting a chart commonly creates a PivotChart, which brings back the limitation. Select the separate range instead, or use Change Chart Type on the chart created from that range.

If the secondary-axis checkbox is unavailable or the chart looks wrong, check that the intended series is selected and that its values are numeric. Text-formatted numbers, blank cells, or headings mistakenly included in the data can prevent Excel from interpreting a series as expected. Also verify that the category labels are in the first column and the series names are in the first row.

Keep the secondary axis from overwhelming the chart. Set readable number formats and sensible bounds, and avoid excessive gridlines. If the two axes use similar-looking scales, viewers may assume the plotted heights are directly comparable. A line with markers, a contrasting color, and a clear axis title can help show which scale it uses. After formatting, compare the plotted values with the PivotTable to catch accidental range or unit errors.

Alternatives and Platform Notes

If a regular chart is not essential, keep the PivotChart and use separate charts for measures that need different scales. Two aligned charts can be clearer than dual axes, especially in a presentation or report where readers need to compare values precisely. You can also choose measures with compatible units for one PivotChart, or use a PivotTable with conditional formatting when the goal is to identify high and low values rather than show a trend.

On Excel for Mac, the same general workaround applies: create an ordinary chart from a separate range and use the chart design controls to set a combo type and secondary axis. Ribbon labels and dialog placement may differ by release. Excel for the web has more limited chart-creation and editing controls than desktop Excel; if the combo settings are unavailable, set up or modify the chart in the Microsoft 365 desktop app.

Frequently Asked Questions

Can you add a secondary axis directly to an Excel PivotChart?

Excel does not support the usual combo-chart setup with a series-specific secondary axis in a native PivotChart. Create a normal chart from a separate range of PivotTable results, then assign the relevant series to a secondary axis.

Can I make a PivotChart into a combo chart?

Not as a supported PivotChart feature in Excel for Microsoft 365 on Windows. Copy or link the PivotTable output to a staging range and create an ordinary chart from that range. In the ordinary chart, use Chart Design > Change Chart Type > Combo.

Will a regular chart update when I refresh the PivotTable?

It will update only if its source range changes along with the PivotTable. Pasted values are static; worksheet links or suitable GETPIVOTDATA formulas can keep staging cells connected. You may still need to adjust the range when the number of PivotTable categories changes.

Should the line or the columns use the secondary axis?

Put the series that needs a different scale or unit on the second axis. A common choice is columns for a large currency measure on the primary axis and a percentage line on the secondary axis, but the data and intended comparison should determine the choice.

Will a slicer control the workaround chart?

A slicer filters its connected PivotTable, but it does not update a chart made from a pasted snapshot. If the chart uses a staging range linked to the PivotTable, it can reflect changed cell values; check that the linked range and chart categories still cover the filtered results.

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.