When working with charts that use a secondary axis in Excel, you might encounter an issue where the scales don’t align properly, especially around the zero, making the chart difficult to interpret. In order to make Excel secondary axes align to zero you need some helper data.
Table of contents
YouTube: Make Excel secondary axes align to zero
Using helper columns to align scales
The above image illustrates the problem of misaligned scales. You can ensure your chart scales align by using helper columns to set consistent minimum and maximum values for both axes.
As shown in the image below, start by creating two new helper columns in your dataset, naming them Primary scale and Secondary scale(1 & 2). For the primary scale, use a formula to calculate the minimum value across all relevant data points. In the first cell of the Primary scale helper column, enter =MIN($B$8:$C$34), as in the image below. For the secondary scale, reference the same minimum value to ensure consistency. Similarly, calculate the maximum values for both scales using =MAX($B$8:$C$34).
Next, include these helper columns in your chart by highlighting your entire dataset, including the original data and the new helper columns, and add them to your existing chart. At first, the chart may look cluttered, but this will be addressed in the next steps.
Change the series chart type
Right-click on any series on the chart and select Change Series Chart Type.
In the dialog box, ensure the series for the Primary scale is not assigned to the secondary axis, while the Secondary scale is correctly placed on the secondary axis. After making these adjustments, the scales should now align.
The image below illustrates the updated chart with the corrected scales.
Formatting the scales for clarity
To make the chart visually clean, format the helper columns’ series to ensure they don’t interfere with the main chart data. For the Primary scale (2) series, go to the Format(1) tab, select the series, and set its line and marker styles to No Line and No Marker.
Repeat this for the Secondary scale series (3). If necessary, match their colors to the chart’s background to make them invisible.
Tips and troubleshooting
- Always ensure that the helper column calculations include the full range of relevant data to avoid missing values and to update when the data changes.
- If the helper series appears in the legend, manually remove it by selecting the legend, then selecting the series and deleting the unwanted entries.
- For complex charts, consider testing the setup on a smaller dataset before applying it to larger datasets.