How to Fix Excel Secondary Axis Not Crossing at Zero
Question details
The user needs to align the zero points of a primary and secondary vertical axis on a single chart.
- Product
- Excel / Spreadsheet
- Device & OS
- not provided
- Scenario
- Creating a combination chart with dual vertical axes that use completely different scales or units, such as billions of dollars and percentages.
- Observed behavior
- The zero line on the secondary vertical axis does not align horizontally with the zero line of the primary vertical axis, creating a misleading visualization.
Before adjusting your axis bounds, duplicate your current chart or worksheet so you have a reference point to revert to if the new scale ratios distort your data visualization.
Manually Adjust Axis Minimum and Maximum Bounds
The most reliable way to align zero lines is to manually set the maximum and minimum values of both axes so they share the exact same positive-to-negative ratio.
Excel automatically scales chart axes based on the maximum and minimum data points of each specific series. When one series dips into negatives and another doesn't, or when their ranges differ drastically, the zero lines will drift apart. By calculating a consistent proportional ratio for both axes, you force the zero lines to meet.
Look at the highest and lowest values for both your primary data series (e.g., currency) and secondary data series (e.g., percentages).
Establish a ratio between the maximum and minimum bounds. For example, if your primary axis maximum is 10,000 and minimum is -5,000, the ratio of positive to negative is 2:1. You must apply this same 2:1 ratio to the secondary axis.
Right-click the primary vertical axis on your chart and select 'Format Axis' from the context menu.
In the Format Axis pane under Axis Options, change the Minimum and Maximum bounds from 'Auto' to your predetermined values (e.g., Maximum: 10000, Minimum: -5000).
Click on the secondary vertical axis. In the same Format Axis pane, set its bounds to match the calculated ratio. If the max percentage is 20%, set the Minimum to -10% to maintain the 2:1 ratio. The zero lines will immediately align.
Align Chart Axes Perfectly with WPS Spreadsheet
WPS Spreadsheet provides intuitive and highly responsive chart formatting tools, making it incredibly simple to manually adjust axis bounds and perfectly align your data's zero points.
- 1. Open your workbook: Launch WPS Spreadsheet and open the document containing your misaligned chart.
- 2. Access axis properties: Double-click on the secondary vertical axis in your chart to instantly open the Format Axis pane on the right side of your screen.
- 3. Navigate to Axis Options: Click the Axis Options icon (which looks like a small bar chart) at the top of the properties pane.
- 4. Input proportional bounds: Manually type in the Minimum and Maximum values so that their ratio matches the ratio of your primary axis bounds.
- 5. Apply changes: Press Enter to lock in the values. Your chart's zero lines will smoothly align.
Frequently Asked Questions
Why do my Excel chart axes not align at zero automatically?
Excel auto-scales each axis independently based on the highest and lowest values of the specific data series assigned to that axis. Since it prioritizes filling the chart area optimally for each series, it does not attempt to match the zero points between the two separate scales.
Can I align the axes without manually changing the maximum bounds?
Yes, but it requires either adding dummy 'invisible' data series to force the minimum/maximum limits artificially, or using a VBA macro to automatically calculate and apply the proportional bounds dynamically when your data updates.
How do I add a secondary axis to my chart in the first place?
Right-click the data series on your chart that you want to move to a new scale, select 'Format Data Series', and under Series Options, choose 'Secondary Axis'. Alternatively, right-click the chart, choose 'Change Chart Type', navigate to 'Combo', and check the 'Secondary Axis' box next to the desired series.
