Using Secondary Axes for Different Measurements in Excel

Course: Advanced Excel

Topic: Using Secondary Axes for Different Measurements

Course Link: Advanced Excel Course – JustAcademy

Demo Link: Register for a Course Demo

Website: JustAcademy Official Website

1. Introduction to Secondary Axes in Excel

Microsoft Excel provides advanced charting features that allow users to analyze multiple data series within a single visualization. In many business situations, the data being compared may have significantly different numerical ranges or different units of measurement. Plotting such values on a single vertical axis can make one series difficult to interpret.

A secondary axis is an additional axis that allows a data series to use a separate scale within the same chart. In a typical Excel chart, the primary vertical axis appears on the left, while the secondary vertical axis appears on the right.

For example, a business may want to compare monthly sales revenue with profit margin. Sales revenue might range from ₹50,000 to ₹5,00,000, while profit margin ranges from 5% to 30%. If both series use the same vertical scale, the percentage values may appear too small to interpret properly.

By assigning profit margin to a secondary axis, Excel can display revenue and profit margin together while using separate numerical scales. This makes it easier to examine their patterns and relationships.

Secondary axes are commonly used in sales reports, financial analysis, inventory monitoring, marketing dashboards, production reports, and other business analytics applications.

2. Learning Objectives

After completing this topic, learners will be able to:

• Understand primary and secondary axes in Excel charts.
• Identify situations where a secondary axis is useful.
• Create combination charts with different measurements.
• Assign a data series to a secondary vertical axis.
• Format primary and secondary axis scales independently.
• Add axis titles, legends, and data labels.
• Compare measurements such as revenue and percentages in one chart.
• Avoid misleading visual comparisons caused by inappropriate axis scales.
• Troubleshoot common secondary-axis problems.
• Apply dual-axis charts to real-world business reports.

3. Understanding Primary and Secondary Axes

3.1 What Is a Primary Axis?

The primary axis is the default axis used by a chart to display a data series. In a standard two-dimensional Excel chart, the primary vertical axis generally appears on the left side of the chart.

For example, in a monthly revenue chart, the primary vertical axis may display values from ₹0 to ₹5,00,000.

3.2 What Is a Secondary Axis?

A secondary axis is an additional axis that allows another data series to be displayed using a separate scale. The secondary vertical axis usually appears on the right side of the chart.

For example, if the same chart also displays profit margin, the secondary axis may show values from 0% to 35%.

3.3 Primary Axis vs Secondary Axis

4. Why Use a Secondary Axis?

A secondary axis is useful when two or more data series need to be displayed together but cannot be interpreted clearly using a single vertical scale.

4.1 Different Numerical Ranges

Suppose monthly sales are measured in hundreds of thousands of rupees, while the number of customer complaints ranges from 5 to 50. On a single scale, the smaller values may appear almost flat. A secondary axis can make both series easier to inspect.

4.2 Different Units of Measurement

Businesses often compare measurements expressed in different units. Examples include:

• Revenue in rupees and profit margin in percentages.
• Units sold and average selling price.
• Website visits and conversion rate.
• Production volume and machine utilization percentage.
• Inventory quantity and inventory turnover rate.
• Temperature and energy consumption.

4.3 Identifying Trends and Relationships

Displaying two series together helps analysts examine whether their patterns move in similar or different directions. For example, a marketing team can compare monthly advertising expenditure with conversion rate to investigate how campaign spending and performance change over time.

However, a dual-axis chart alone does not prove that one measurement causes changes in another. Further analysis may be needed to establish a meaningful relationship.

5. Understanding Combination Charts

A combination chart, also called a combo chart, combines two or more chart types in a single visualization. It is often used with a secondary axis because different measurements can be displayed using different chart types and scales.

For example, a chart can use columns for monthly sales revenue and a line for profit margin.

5.1 Common Combination Chart Types

The available combinations depend on the Excel version and chart types. A secondary axis is not required for every combination chart; use it only when it improves interpretation.

6. Sample Data for a Secondary-Axis Chart

Consider a company that wants to compare monthly sales revenue with its profit margin.

In this example, sales revenue is measured in rupees and ranges from ₹150,000 to ₹400,000. Profit margin is measured as a percentage and ranges from 12% to 27%.

If both series are plotted against the same vertical axis, the profit-margin series may be difficult to interpret because its values are much smaller than the revenue figures. Assigning profit margin to a secondary axis provides a separate percentage scale.

7. Method 1: Create a Combination Chart with a Secondary Axis

This is a convenient method in supported versions of Microsoft Excel, including Microsoft 365 and recent desktop editions.

Step 1: Enter the Data

1. Open Microsoft Excel.
2. Enter the monthly sales data in three columns.
3. Use the column headings Month, Sales Revenue, and Profit Margin.
4. Check that the values are stored as numbers and that the month names are consistent.

Step 2: Select the Data Range

Select the complete data range, including the column headings. For example, if the table occupies cells A1:C7, select A1:C7.

Step 3: Insert a Combination Chart

1. Open the Insert tab.
2. Select Recommended Charts or open the chart insertion options.
3. Choose the Combo chart category, if available.
4. Choose a combination such as Clustered Column – Line on Secondary Axis.

Step 4: Assign the Secondary Axis

1. In the combination chart settings, locate the Profit Margin series.
2. Choose a suitable chart type, such as Line with Markers.
3. Enable the Secondary Axis checkbox for Profit Margin.
4. Keep Sales Revenue on the primary axis.
5. Click OK to create the chart.

Excel now displays revenue as columns and profit margin as a line, using separate vertical scales.

Step 5: Add a Chart Title

Use a meaningful title such as Monthly Sales Revenue and Profit Margin. The title should explain the two measurements being compared.

Step 6: Add Axis Titles

Add clear titles to distinguish the two scales:

• Primary vertical axis: Sales Revenue (₹).
• Secondary vertical axis: Profit Margin (%).
• Horizontal axis: Month.

To add axis titles, select the chart, open Chart Design, select Add Chart Element, and choose Axis Titles. Add titles for the primary vertical axis and secondary vertical axis where supported by the chart type and Excel version.

Step 7: Format the Chart

• Format the primary axis as currency or a suitable rupee-based number format.
• Format the secondary axis as a percentage.
• Use distinct, readable formatting for the revenue columns and profit-margin line.
• Keep the legend visible and easy to understand.
• Review the axis bounds to avoid exaggerating the apparent relationship.

8. Method 2: Add a Secondary Axis to an Existing Chart

If you already have a chart, you can assign one of its data series to a secondary axis without rebuilding the entire visualization.

Step 1: Select the Chart

Click the existing chart to display the Chart Design and Format tabs.

Step 2: Select the Required Data Series

Click the line, column, bar, or other series that should use the secondary axis. Ensure that the entire data series is selected rather than a single data point.

Step 3: Open the Formatting Options

Right-click the selected data series and choose Format Data Series.

Step 4: Enable the Secondary Axis

In the Format Data Series pane, locate Series Options and select Secondary Axis.

Step 5: Verify the Result

Check whether a second vertical axis appears on the right side of the chart. Format its bounds, number format, and title as required.

Alternative method: In supported Excel versions, select the chart, open Chart Design, choose Change Chart Type, select Combo, and enable Secondary Axis for the appropriate series.

9. Formatting the Secondary Axis

After adding a secondary axis, formatting it correctly is essential. An unsuitable scale can make a chart difficult to interpret or visually misleading.

9.1 Adjust Minimum and Maximum Bounds

Bounds define the lowest and highest values displayed on a numerical axis. For example, the secondary axis for profit margin may range from 0% to 30%.

1. Select the secondary vertical axis.
2. Right-click and choose Format Axis.
3. Locate the Bounds section.
4. Enter appropriate minimum and maximum values if supported.
5. Check that the selected range accurately represents the data.

When profit margin is being compared across multiple periods or reports, a consistent scale can make the comparisons easier and more reliable.

9.2 Adjust Major Units

Major units control the intervals between the primary scale marks. For a percentage axis ranging from 0% to 30%, intervals of 5 percentage points may provide a clear scale.

To adjust the intervals, select the secondary axis, open Format Axis, and modify the Major unit setting where available.

9.3 Format Percentages Correctly

Percentage data must be stored and formatted consistently. Excel commonly stores 12% as the underlying decimal value 0.12. If the data contains the number 12, applying the standard percentage format may display 1200%.

To avoid this problem:

• If the data is stored as 0.12, apply Percentage formatting to display 12%.
• If the data is stored as 12 to represent 12%, convert it to 0.12 before applying Percentage formatting, or use an appropriate custom format that does not multiply the displayed value.
• Verify the result by comparing a few displayed values with the original data.

9.4 Format Currency Values

For revenue, use a suitable currency format, such as ₹150,000, or a compact display such as ₹150K if that format is appropriate for the report. Consistency helps readers understand the unit of measurement.

9.5 Add Axis Titles

Every secondary axis should have a clear title that identifies the measurement and its unit. Avoid using vague titles such as "Value 2". Instead, use titles such as "Profit Margin (%)", "Conversion Rate (%)", or "Average Selling Price (₹)".

10. Practical Example: Website Traffic and Conversion Rate

A digital marketing team wants to analyze website performance by comparing monthly website visitors with the conversion rate.

10.1 Chart Design

• Primary axis: Website Visitors.
• Secondary axis: Conversion Rate (%).
• Horizontal axis: Month.
• Chart type: Columns for visitors and a line for conversion rate.

10.2 Steps to Build the Chart

1. Select the complete table.
2. Insert a Combo chart.
3. Set Website Visitors to Clustered Column.
4. Set Conversion Rate to Line with Markers.
5. Enable Secondary Axis for Conversion Rate.
6. Add descriptive axis titles.
7. Format the secondary axis as a percentage.
8. Review the chart to ensure that labels and scales are easy to read.

10.3 Business Interpretation

The chart can help the marketing team observe whether website traffic and conversion rate rise or fall together. For example, the sample data shows both measurements increasing over the six-month period.

However, this visual pattern does not establish that the increase in visitors caused the higher conversion rate. The team should also consider campaign changes, traffic sources, audience quality, landing-page design, and other factors before drawing conclusions.

11. Practical Example: Units Sold and Average Selling Price

A retail company may want to display monthly units sold alongside the average selling price of its products.

11.1 Recommended Chart Configuration

• Use columns for units sold.
• Use a line for average selling price.
• Keep units sold on the primary axis.
• Assign average selling price to the secondary axis.
• Label the axes with their respective units.

11.2 Business Benefits

This chart allows managers to review changes in sales volume and average selling price in one place. It can support pricing discussions, sales performance reviews, and product planning.

12. Secondary Axes in Financial Analysis

Secondary axes are frequently used in financial reports to compare absolute amounts with ratios or percentages.

Examples include:

• Revenue and gross profit margin.
• Operating expenses and expense ratio.
• Sales value and sales growth percentage.
• Investment value and percentage return.
• Actual revenue and target achievement percentage.

When creating financial charts, it is important to distinguish monetary values from rates. Clear axis titles and number formats prevent readers from confusing currency amounts with percentages.

13. Advantages of Using Secondary Axes

• Improved readability: Different data ranges can be displayed using separate scales.
• Multiple measurements: Different units can be shown in one chart.
• Better trend analysis: Users can inspect changes in two series over the same periods.
• Compact reporting: Related measurements can be displayed together instead of using separate charts.
• Dashboard integration: Combo charts can present key business indicators in a compact format.
• Flexible visualization: Different chart types can be used for different series.

14. Limitations and Risks of Secondary Axes

Although secondary axes are useful, they should be used carefully.

• Misleading visual relationships: Different axis bounds can make unrelated series appear to move together.
• Reader confusion: Viewers may not realize that the two series use different scales.
• Complexity: Too many series and axes can make the chart difficult to interpret.
• Scale sensitivity: Changing the minimum or maximum bounds can significantly alter the apparent relationship.
• Unit confusion: Missing axis titles can make values difficult to understand.

Use a secondary axis only when it serves a clear analytical purpose. When the measurements can be understood more clearly in separate charts, consider using two aligned charts instead.

15. Best Practices for Secondary-Axis Charts

• Use the primary axis for the main measurement and the secondary axis for the additional measurement.
• Give each vertical axis a descriptive title and include units.
• Format currency, percentages, quantities, and dates correctly.
• Choose chart types that help distinguish the series visually.
• Use clear legends and consistent series names.
• Avoid unnecessary 3D effects and decorative elements.
• Use consistent axis scales when comparing similar charts across reports.
• Check whether the chart suggests a relationship that is not supported by the data.
• Do not change axis bounds solely to make a trend appear stronger.
• Consider separate charts when the dual-axis presentation becomes confusing.
• Review the final chart at its intended display size.

16. Common Problems and Troubleshooting

17. Hands-On Practice Exercises

Exercise 1: Sales Revenue and Profit Margin

Create a combination chart using six months of sales revenue and profit margin data.

• Use columns for revenue.
• Use a line for profit margin.
• Assign profit margin to a secondary axis.
• Format the primary axis as currency and the secondary axis as a percentage.
• Add appropriate axis titles and a chart title.

Exercise 2: Website Visitors and Conversion Rate

Build a chart showing monthly website traffic and conversion rate.

• Display website visitors on the primary axis.
• Display conversion rate on the secondary axis.
• Use a line chart for conversion rate.
• Explain the observed trends without assuming causation.

Exercise 3: Production Volume and Machine Utilization

Create a chart that compares monthly production quantity with machine utilization percentage.

• Use columns for production quantity.
• Use a line for utilization percentage.
• Apply appropriate axis scales and units.
• Identify months with changes in both measurements.

Exercise 4: Actual Sales and Target Achievement

Create a chart that displays actual sales and target achievement percentage for each quarter.

• Use actual sales as the primary measurement.
• Use target achievement percentage as the secondary measurement.
• Apply clear axis titles.
• Check that the percentage values are calculated correctly.

18. Interview Questions and Answers

Q1. What is a secondary axis in Excel?

Answer: A secondary axis is an additional chart axis that allows a data series to use a different scale from the primary axis.

Q2. Why is a secondary axis used?

Answer: It is useful when data series have substantially different numerical ranges or different units of measurement, such as revenue and profit margin.

Q3. Where does the secondary vertical axis normally appear?

Answer: It normally appears on the right side of a two-dimensional Excel chart.

Q4. What is a combination chart?

Answer: A combination chart displays multiple chart types, such as columns and lines, within a single chart.

Q5. How can you add a secondary axis to an existing chart?

Answer: Select the data series, right-click it, choose Format Data Series, and select Secondary Axis under Series Options. The Combo chart settings provide another method in supported Excel versions.

Q6. Can a secondary axis display percentages while the primary axis displays currency?

Answer: Yes. For example, the primary axis can display sales revenue in rupees while the secondary axis displays profit margin as a percentage.

Q7. What is the difference between a primary and secondary vertical axis?

Answer: Both display numerical scales, but they can use different bounds and units. The primary vertical axis is usually on the left and the secondary vertical axis is usually on the right.

Q8. Why should secondary axes have clear titles?

Answer: Clear titles tell readers which measurement and unit each scale represents, reducing confusion and the risk of misinterpretation.

Q9. Can a secondary axis make a chart misleading?

Answer: Yes. Different scales can make unrelated series appear visually similar or suggest a stronger relationship than the data supports. Appropriate axis bounds and clear labels are important.

Q10. When should separate charts be used instead?

Answer: Separate charts may be preferable when the measurements are difficult to compare, when the dual-axis design is crowded, or when separate scales would confuse the audience.

Q11. What is the purpose of a secondary-axis title?

Answer: It identifies the measurement displayed on the secondary scale, such as Conversion Rate (%) or Average Selling Price (₹).

Q12. What is one common use of a secondary axis in marketing analytics?

Answer: A marketing team may use columns for website visitors and a line on a secondary axis for conversion rate to inspect how both measurements change over time.

19. Quick Revision Checklist

• Understand the difference between primary and secondary axes.
• Identify when measurements have different units or ranges.
• Insert a suitable combination chart.
• Assign the appropriate series to the secondary axis.
• Format each axis using the correct number format.
• Add descriptive axis titles and a chart title.
• Use clear legends and distinguishable series styles.
• Review bounds and intervals to avoid misleading comparisons.
• Check the source data and verify the displayed values.
• Consider separate charts if the dual-axis design is unclear.

20. Conclusion

Using secondary axes for different measurements is an important Advanced Excel skill that helps users create more informative charts from data with different scales or units. By combining columns, lines, and separate vertical axes, analysts can display related measurements such as revenue and profit margin, website visitors and conversion rate, or production volume and machine utilization in one visualization.

The most important part of creating a dual-axis chart is not simply adding a second scale. It is making both measurements understandable, formatting the axes correctly, and presenting the information without exaggerating the relationship between the series.

Regular practice with combination charts, axis formatting, and business datasets will help learners build clearer Excel reports and professional dashboards.

21. Learn Advanced Excel with JustAcademy

Explore advanced spreadsheet skills, professional charting, PivotTables, data analysis, interactive reporting, and dashboard creation through the Advanced Excel course at JustAcademy.

Course Details: Explore the Advanced Excel Course

Course Demo: Register for a Course Demo

Official Website: Visit JustAcademy