XLChat

How to Align Primary and Secondary Axis in Excel

AS
aspardo
3-1-2025

When working with charts in Excel, you might often need to display two different data sets that have different scales. This is where using a primary and secondary axis can be incredibly useful. However, aligning these axes to ensure your chart is clear and easy to read can be a bit tricky. In this blog post, we'll walk you through the steps to align the primary and secondary axis in Excel.

Step 1: Create Your Chart

First, you need to create a chart with both a primary and secondary axis. Here’s how you can do it:

  1. Select Your Data: Choose the data you want to include in your chart.
  2. Insert a Chart: Go to the Insert tab, and select the chart type you want to use (e.g., a line chart or a column chart).
  3. Add a Secondary Axis: Right-click on the data series you want to plot on the secondary axis, select Format Data Series, and then choose Secondary Axis.

Your chart should now have two vertical axes: one on the left (primary) and one on the right (secondary).

Step 2: Adjust the Scale of the Axes

To align the primary and secondary axes, you need to ensure that the scales are set in a way that makes the data comparable. Here’s how to do it:

  1. Select the Primary Axis: Click on the primary axis (the one on the left).

  2. Format Axis: Right-click and select Format Axis.

  3. Set the Minimum and Maximum Values: In the Axis Options section, set the Minimum and Maximum values to fixed numbers that make sense for your data. For example, if your primary axis ranges from 0 to 100, set the minimum to 0 and the maximum to 100.

  4. Select the Secondary Axis: Click on the secondary axis (the one on the right).

  5. Format Axis: Right-click and select Format Axis.

  6. Set the Minimum and Maximum Values: In the Axis Options section, set the Minimum and Maximum values to the same numbers you used for the primary axis. For example, set the minimum to 0 and the maximum to 100.

Step 3: Fine-Tuning the Alignment

Sometimes, even after setting the same minimum and maximum values, the axes might not look perfectly aligned. This can happen due to different major and minor unit settings. Here’s how to fine-tune the alignment:

  1. Adjust Major and Minor Units: In the Format Axis window for both the primary and secondary axes, set the Major Unit and Minor Unit to the same values. For example, if you set the major unit to 10 and the minor unit to 2 for the primary axis, do the same for the secondary axis.

  2. Check the Display Units: Ensure that the Display Units are the same for both axes. If one axis is set to display in thousands and the other in units, they won’t align properly.

Step 4: Verify and Finalize

After making these adjustments, take a close look at your chart to ensure that the primary and secondary axes are aligned as expected. If everything looks good, you’re done! If not, you might need to go back and tweak the settings a bit more.

Example

Let’s say you have two data series: Sales (ranging from 0 to 1000) and Profit Margin (ranging from 0 to 100). Here’s how you would align the axes:

  1. Primary Axis (Sales): Set the minimum to 0 and the maximum to 1000. Set the major unit to 100 and the minor unit to 20.
  2. Secondary Axis (Profit Margin): Set the minimum to 0 and the maximum to 100. Set the major unit to 10 and the minor unit to 2.

By following these steps, you should be able to create a chart where the primary and secondary axes are aligned, making it easier for your audience to interpret the data.

Conclusion

Aligning the primary and secondary axes in Excel can significantly improve the readability and effectiveness of your charts. By carefully setting the minimum and maximum values, as well as the major and minor units, you can ensure that your data is presented clearly and accurately. Happy charting!