How to Use a Secondary Axis with Stacked Charts in Excel
Question details
The user wants to display small stacked columns or bars on a secondary axis because their values are significantly smaller than the primary chart series.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Creating a visualization where one group of stacked data has a vastly different numerical scale compared to the primary stacked data.
- Observed behavior
- Excel does not natively support placing an entire stacked group on a secondary axis while preserving the side-by-side stacked arrangement without overlapping.
Verify your dataset to ensure the smaller values are correct, and consider if a combination chart might represent your data more clearly than a pure stacked column chart.
Combine Chart Types (Recommended)
Since you cannot stack columns on a secondary axis without overlapping the primary columns, converting the smaller series to a line chart on a secondary axis is the most effective workaround.
Excel does not allow side-by-side stacked columns across two different axes natively. By changing the secondary data into a line chart, you maintain visibility for the smaller values without disrupting the stacked columns of the primary data.
Click on the small data series in your existing stacked chart. If the values are too small to click, go to the 'Format' tab under Chart Tools and select the series from the 'Current Selection' dropdown on the far left.
Right-click the selected series and choose 'Change Series Chart Type' from the context menu.
In the dialog box, navigate to the 'Combo' tab. Locate the data series containing your smaller values.
Check the 'Secondary Axis' box for those specific series and change their 'Chart Type' dropdown to 'Line'. Click 'OK' to apply the changes.
Create Two Separate Aligned Charts
If you absolutely must keep the stacked column format for both datasets, use two separate charts visually combined into a single dashboard panel.
Try WPS Office for Flexible Data Visualization
If you frequently work with complex datasets and encounter charting limitations, WPS Office provides a comprehensive, lightweight, and free alternative. It seamlessly handles complex visualizations, combination charts, and massive datasets with a highly familiar interface.
- 1. Download and Install: Download WPS Office for free from the official website and install it on your device.
- 2. Open Your Data: Launch WPS Spreadsheet and easily open your existing Excel (.xlsx) files without losing any formatting.
- 3. Insert Complex Charts: Highlight your data, navigate to the 'Insert' tab, and select 'Chart' to explore versatile visualization options including custom combination charts.
Frequently Asked Questions
Why can't I stack columns on both a primary and secondary axis in Excel?
Excel's charting engine does not natively support clustered-stacked combinations across two different axes. When you move a stacked series to a secondary axis, it overlays directly on top of the primary axis columns, hiding them instead of sitting side-by-side.
How do I select a chart series in Excel that is too small to see?
Click anywhere on your chart to activate it. Go to the 'Format' tab under 'Chart Tools' (or 'Chart Format' in newer versions), and use the dropdown menu in the 'Current Selection' group on the far left to select the hidden or tiny series.
Can I use a secondary axis with a bar chart?
Yes, you can add a secondary axis to a horizontal bar chart in the same way you do for a column chart. However, just like with column charts, attempting to stack elements on both axes simultaneously will cause the bars to visually overlap.
