Pivot Chart Secondary Axis: How to Add and Format One

Yes. In Excel for Microsoft 365 on Windows, select the PivotChart series you want to rescale, open Format Data Series, and under Series Options choose Secondary Axis. Excel adds a vertical scale on the chart’s right side, letting series with different units or magnitudes share the same chart; use it carefully because the two axes have independent scales.

What a secondary axis does

A chart’s primary vertical axis is usually on the left. A secondary vertical axis is a second value scale, usually on the right. Assigning a series to it changes the scale used to draw that series; it does not change the underlying PivotTable values, calculations, or source data. The chart still responds to PivotTable filters and slicers as before.

This is useful when two measures differ substantially in size or unit. For example, a PivotChart might show monthly revenue in dollars, ranging from $20,000 to $80,000, and customer count, ranging from 200 to 900. If both series use the same scale, the smaller values may appear nearly flat against the baseline. A secondary axis gives customer count its own range so its changes are visible.

The axes do not become mathematically related simply because they appear together. Excel scales each independently, so the visual height of one series cannot be compared directly with the visual height of another. A line that appears to rise at the same rate as a column series may only look that way because of the chosen axis bounds.

How to add one to a PivotChart

These steps apply to Excel for Microsoft 365 on Windows. First, make sure the chart contains at least two value series; a secondary axis is not useful if there is only one measure. Then assign the appropriate series to the second scale:

Click the PivotChart, then click the specific line, column, or other data series you want to move. If necessary, click a point in that series again so the whole series is selected rather than one point.
Right-click the selected series and choose Format Data Series. Alternatively, select the series and press Ctrl+1 to open its formatting pane.
In the pane, open Series Options and choose Secondary Axis. The series should now use the right-hand vertical scale.
Check that the primary and secondary axis labels are visible and understandable. Add a title for the right-hand axis if readers might mistake its units.

On Excel for Mac, Control-click the series and choose Format Data Series, then look for the same axis choice under Series Options. The pane layout and ribbon details can vary slightly by release. Excel for the web has fewer chart-formatting controls than the desktop app; if the axis option is unavailable there, open the workbook in the desktop version.

Do not select the entire chart when looking for the setting: the option belongs to an individual data series. Also, the secondary axis is a value axis, not a second category axis. Month names, product names, and other categories generally remain along the chart’s horizontal axis.

Choose a series and chart type that make sense

Choose the series for the secondary scale based on its unit and magnitude, not just on which line looks crowded. Common pairings include revenue and units sold, sales and percentage margin, or website visits and conversion rate. Give each axis a descriptive title, such as Revenue ($) and Orders, so the reader knows which scale applies to which measure.

For a mixed-unit comparison, a column series for the primary measure and a line for the secondary measure is often easier to read than two sets of columns. However, Excel PivotCharts have an important limitation: they do not support the usual combination-chart feature that assigns different chart types to different series. A standard chart can use a combo chart, but a PivotChart may not offer that chart-type arrangement. If you can assign the secondary axis but cannot make one series a line, the limitation is about the chart type, not necessarily the axis itself.

For instance, a PivotChart with monthly revenue columns and a customer-count line would be a common design. If the PivotChart cannot provide that combination, consider whether two column series on separate axes are clear enough. If they are not, make a standard chart from a stable summary range or helper table and set its series types there. That chart will not behave like a PivotChart in every respect, so plan how it will respond when the PivotTable layout changes.

Format and check both scales

After adding the axis, format it so readers can distinguish the measures. Select the right-hand axis to open Format Axis. Set a suitable number format, such as percentage or currency, and review the minimum and maximum bounds. Excel commonly chooses bounds automatically; that can be appropriate, but it may cause the apparent relationship between series to shift when filters change.

Use axis titles and consistent visual cues. For example, a blue line and a blue right-axis title can connect the line to the customer-count scale. Avoid relying on color alone, since charts may be printed in grayscale or viewed by people with color-vision differences. A legend, direct labels, distinct line markers, and explicit units can help.

Use the same unit on the same axis where possible; do not combine dollars and percentages on one scale.
Keep the secondary scale visible when its series is visible. Hiding the scale removes important context.
Review the chart after changing a report filter or slicer. A filtered subset can have a very different range.
Check whether the axis starts at zero when that matters to the comparison. A truncated scale can exaggerate variation, especially in column charts.

There is no universally correct matching of axis bounds. The right range depends on the data and the question the chart answers. If the goal is to show trends, clearly labeled scales may be sufficient. If the goal is to compare magnitudes, a dual-axis display can mislead; separate charts or indexed values may communicate the comparison more honestly.

PivotChart limits and common problems

If the secondary-axis control is missing or unavailable, verify that you selected a data series rather than an axis, chart area, or PivotChart field button. Some chart types and series configurations expose different formatting choices. Try a chart type that supports value-axis formatting, such as a line or column chart, and check the Series Options pane again. If the chart still does not allow the change, use a standard chart based on the summarized data as a workaround.

Another common issue is confusing the PivotChart’s field layout with the chart’s series settings. The PivotChart Fields pane controls which fields appear in the categories, legend, filters, and values. It does not set a series’ axis. Conversely, changing an axis does not move a field between Rows and Columns in the PivotTable.

PivotCharts are connected to their PivotTable structure, and their chart-type options are more restricted than those of ordinary charts. In particular, a PivotChart cannot use Excel’s normal combo-chart setup to give series different chart types. Avoid assuming that every chart command available for a regular chart will work identically for a PivotChart. If a required display is not available, build a normal chart from a separate summary range and test it after refreshing or filtering the PivotTable.

When a PivotChart refreshes, its data points and series can change as fields or filters change. Recheck the axis assignment and labels after major layout edits. If a measure disappears and is later added again, Excel may treat it as a changed series, so confirm that the intended measure still uses the secondary axis.

When to use another chart design

A secondary axis is not automatically the best answer to a scale mismatch. If both measures are important and have very different units, two small charts aligned by month can make each scale explicit without suggesting a direct magnitude comparison. A standard combo chart can also work when one measure is a count or currency and another is a percentage, provided both axes are labeled clearly.

For trend comparison, another option is to index both series to a common starting point, such as setting each series’ first month to 100. This highlights relative change rather than the original units, but it requires a helper calculation and should be labeled as an index. Do not present indexed values as actual sales, customers, or percentages.

If interactive PivotTable filtering is essential, keeping the PivotChart may matter more than using a particular chart type. If a precise combo design is essential, a standard chart may be worth the extra maintenance. A helper range can reference PivotTable results, but layout changes may move the results or alter the range, so test refreshes, filters, and added categories before relying on it.

Frequently Asked Questions

Can a PivotChart have a secondary axis?
Usually, yes. Select the series and choose Secondary Axis in the Format Data Series pane. Availability can depend on the chart type and configuration. PivotCharts also have chart-type limitations, including the inability to create a normal combo chart.

How do I put only one PivotChart series on the right axis?
Select that series, open Format Data Series, and choose Secondary Axis under Series Options. Selecting the axis itself or the chart background will not show the series setting.

Can I make one PivotChart series a line and another a column?
Not with Excel’s standard combo-chart feature on a PivotChart. If that design is required, create a regular chart from a summary or helper range, then set the chart types and secondary axis there.

Why does my secondary-axis chart look misleading?
The two value axes are scaled independently. Excel can make lines and columns appear to move together even when their real values have different units or rates of change. Label both axes, inspect their bounds, and consider separate charts if readers need to compare actual magnitudes.

Will a secondary axis change my PivotTable calculations?
No. It changes how a chart series is plotted, not the PivotTable’s values or calculation. Refreshes and filter changes can alter the displayed series, so check that the intended series remains assigned to the right axis.

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.