Adding Secondary Axes in Excel
In Excel, a secondary axis allows you to compare two sets of data that have different units of measurement or scales on the same chart. This tutorial explains how to add secondary axes to charts in Excel and provides real-time examples of their use.
Step-by-Step Tutorial
Step 1: Prepare Your Data
Before adding a secondary axis, ensure that your data is ready. For example, suppose you have the following data:
| Month | Sales (in thousands) | Profit (in percentage) |
|----------|----------------------|------------------------|
| January | 500 | 20 |
| February | 600 | 25 |
| March | 700 | 30 |
| April | 800 | 35 |
| May | 750 | 40 |
| June | 900 | 45 |
In this example, you want to create a chart that compares Sales (in thousands) and Profit (in percentage), which requires different scales, so you will use a secondary axis for Profit.
Step 2: Create a Basic Chart
Select the data range for both Sales and Profit (A1:C7). Then, go to the "Insert" tab and choose a chart type that supports secondary axes, such as a "Line and Column" chart.
- Go to the "Insert" tab on the ribbon.
- In the "Charts" group, choose a chart type like "Combo" (Line and Column) or any other type that works well with dual axes.
- Click on your selected chart type, and Excel will generate a chart using the selected data.
Step 3: Add a Secondary Axis
Now, you need to add a secondary axis for the Profit data:
- Click on the chart to select it.
- Right-click on the data series you want to plot on the secondary axis (in this case, the Profit data series).
- Choose "Format Data Series" from the context menu.
- In the "Format Data Series" pane, check the option "Secondary Axis."
- Click "Close" to apply the secondary axis to the chart.
Step 4: Customize the Chart
You can customize the chart to make the data more readable and visually appealing:
- Change the chart title to something relevant, like "Sales vs. Profit" or "Sales and Profit Comparison."
- Adjust the axis labels for both the primary and secondary axes to make sure they are clear.
- Use different colors or styles for the Sales and Profit data series to distinguish them easily on the chart.
- Customize the gridlines and axis formatting to suit your data presentation needs.
Real-Time Examples
Example 1: Comparing Sales and Profit
Scenario: You have the Sales data in thousands and Profit data in percentages, and you want to visualize both data sets on the same chart.
In this example, you will create a Combo chart with Sales as a column chart and Profit as a line chart. The Sales data will be plotted on the primary axis, and the Profit data will be plotted on the secondary axis.
| Month | Sales (in thousands) | Profit (in percentage) |
|----------|----------------------|------------------------|
| January | 500 | 20 |
| February | 600 | 25 |
| March | 700 | 30 |
| April | 800 | 35 |
| May | 750 | 40 |
| June | 900 | 45 |
By following the steps, you will create a chart with two different axes, making it easier to compare Sales (in thousands) and Profit (in percentage). The Sales data will be represented as columns, and the Profit data will be a line graph with the secondary axis.
Example 2: Comparing Temperature and Rainfall
Scenario: You want to compare the monthly temperature (in °C) and monthly rainfall (in millimeters) for a year. These two variables use different units and scales, making a secondary axis ideal for this comparison.
| Month | Temperature (°C) | Rainfall (mm) |
|----------|------------------|----------------|
| January | 5 | 120 |
| February | 7 | 100 |
| March | 10 | 90 |
| April | 12 | 60 |
| May | 18 | 30 |
| June | 25 | 15 |
Create a Combo chart where the Temperature is represented by a line graph and plotted on the primary axis, while the Rainfall is represented by columns and plotted on the secondary axis. This setup allows you to compare temperature trends and rainfall amounts in one chart.
Example 3: Comparing Revenue and Expenses
Scenario: A business wants to compare monthly Revenue and Expenses over the course of a year. Since Revenue and Expenses might be in different units (such as Revenue in thousands and Expenses in actual units), a secondary axis will help in visualizing both datasets properly.
| Month | Revenue (in thousands) | Expenses (in units) |
|----------|------------------------|---------------------|
| January | 500 | 200 |
| February | 600 | 250 |
| March | 700 | 300 |
| April | 800 | 350 |
| May | 750 | 400 |
| June | 900 | 450 |
For this example, Revenue can be plotted as columns on the primary axis and Expenses can be plotted as a line on the secondary axis. This allows easy comparison of the two metrics on different scales.
Tips for Using Secondary Axes
- Use secondary axes when comparing two data series with different units or vastly different ranges.
- Ensure that the chart remains readable and not too cluttered. Use clear labels and distinct colors for different data series.
- Keep the primary axis for the data series with larger or more important values, and use the secondary axis for the smaller data series.
- Always label both axes clearly to avoid confusion between the primary and secondary axes.
Conclusion
Adding a secondary axis in Excel is a powerful way to compare two sets of data that have different scales. By following the steps in this tutorial, you can effectively create and customize charts with dual axes, making it easier to analyze complex data. Whether you are comparing sales and profits, temperature and rainfall, or revenue and expenses, secondary axes provide a clear and insightful way to present your data.