How to Keep the Secondary Axis Visible in an Excel Pivot Chart
Question details
The user wants to prevent the secondary Y-axis from disappearing in an Excel PivotChart when filtering data with a slicer.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Filtering data in a PivotChart that combines measures with different scales (e.g., Sales and Rate) using slicers.
- Observed behavior
- The secondary Y-axis is automatically removed or reset when slicer filters are applied, disrupting the visualization of the data.
Ensure your PivotChart is set to a Combo Chart type and verify that the data points mapped to the secondary axis actually contain values under your current slicer filter selection.
Verify and Re-assign the Secondary Axis Settings
Excel sometimes resets chart formatting when filters temporarily hide the data. Manually enforcing the secondary axis on the specific data series after filtering can resolve this.
PivotCharts are highly dynamic and automatically regenerate when the underlying PivotTable changes. If a slicer temporarily removes all data for a specific series, the chart may drop the secondary axis to save space.
Click on your slicer to apply the specific filter that causes the secondary axis to disappear.
Right-click on the data series (e.g., the Rate line) in the chart. If it is difficult to click, navigate to the Format tab under Chart Tools, and select the series from the Current Selection dropdown on the far left.
Choose Format Data Series from the right-click context menu.
In the Format Data Series pane that appears on the right, look under Series Options and select the Secondary Axis radio button.
Use a Standard Chart Linked to a Helper Range
If the PivotChart dynamically resets its structure too aggressively, linking a standard chart to the PivotTable data ensures formatting remains locked permanently.
Use WPS Spreadsheet for Reliable Data Visualization
WPS Spreadsheet offers robust PivotTable and PivotChart functionalities. It provides a stable environment for building combo charts with secondary axes that remain intact when applying slicer filters, ensuring your data analysis remains uninterrupted.
- 1. Open your data in WPS Spreadsheet: Launch WPS Office and open your existing Excel workbook containing the raw data or PivotTable.
- 2. Insert a PivotTable and Chart: Go to the Insert tab, click PivotTable to organize your data, and then insert a PivotChart based on the aggregated fields.
- 3. Configure the Combo Chart: Right-click the chart, select Change Chart Type, choose Combo, and check the Secondary Axis box for your secondary measure.
- 4. Add a Slicer: Click on the PivotChart, navigate to the Analyze tab, and click Insert Slicer to dynamically filter your data without losing the chart formatting.
Frequently Asked Questions
Why does the secondary axis disappear when I use a slicer?
This typically happens because the slicer filters out all data for the series assigned to the secondary axis. When a PivotChart detects no visible data points for a specific series, it may automatically hide the associated axis to adjust the chart layout dynamically.
Can I lock the formatting of an Excel PivotChart?
PivotCharts are inherently dynamic, meaning their formatting often resets when the underlying data shape changes. While you cannot strictly lock the formatting, you can bypass the issue by creating a standard chart linked to the PivotTable's data cells.
Does this secondary axis issue happen in standard combo charts?
No, standard combo charts generally retain their secondary axis configuration even if some data points become empty. The automatic resetting behavior is specifically tied to how PivotCharts dynamically regenerate based on the PivotTable cache.
How do I show empty items in my PivotChart to prevent the axis from hiding?
You can force the PivotTable to display empty items by right-clicking a field in the PivotTable, selecting Field Settings, navigating to the Layout & Print tab, and checking the 'Show items with no data' option. This ensures the data series remains present, keeping the secondary axis visible.
