Imagine that you can instantly see the growth curve of your sales and the market share percentage on the same chart: one displays thousands of units, the other tens of percent. Without a secondary axis, your minority data disappears at the bottom of the graph and those with a large scale saturate the primary axis. I found myself in this situation during a quarterly report, facing an almost unreadable chart. Since then, I systematically use a secondary axis in my combined charts. In this article, I guide you step by step to master this technique and obtain clear, impactful, and perfectly readable visuals.
Why use a secondary axis?
When crossing two data series with different orders of magnitude — for example, the number of leads generated and the conversion rate in percentage — a single axis is not enough. The first can climb up to 10,000, the second stagnates around 15%. Without adjustment, the “percentage” series becomes almost flat, stuck to the horizontal axis. The secondary axis allows you to overlay two distinct graduations and visually compare trends and inflection points. It is a real gain in readability, especially for:
- Comparing absolute value and ratio
- Visualizing a variable with two scales (volume vs growth)
- Highlighting a key indicator while maintaining the overall view
Creating a combined chart in Excel
Excel has offered a “Combined Chart” type for several versions that facilitates setting up a second axis without manually fiddling with options. Let’s look at the essential steps to achieve this.
Step 1: Selecting the data
Start by organizing your table: place the period (days, months, quarters) in column A, the first series (values with large amplitude) in column B, and the second (percentage or smaller volume) in column C. If you had to split cells to improve the readability of your header, make sure to include each range in your selection. Then select all your data in one block.
Step 2: Choosing the combined type
In the Insert tab, click on “Insert a combined chart” then “Custom combined chart.” You get a dialog box listing your series. For each series, you can choose a chart type (histogram, curve, area…) and assign the primary or secondary axis. For example, select a histogram for quantities and a curve for the percentage.
Step 3: Assigning the secondary axis
Still in the combined chart window, check the “Secondary axis” box for the series most sensitive to its own scale. Click “OK”: Excel instantly generates your combined chart with two vertical axes. You can then distinguish the percentage curve climbing elegantly on the right and the large bars on the left.
Customizing the secondary axis
A basic secondary axis remains neutral; but to enhance clarity, a few adjustments are useful. Here’s how to refine the scale, format, and style.
Change the scale and format
Right-click on the secondary axis then “Format Axis”. You access a panel where it is possible to:
| Parameter | Description |
|---|---|
| Minimum | Lowest displayed value |
| Maximum | Highest displayed value |
| Major unit | Interval between two main graduations |
| Minor unit | Step for secondary graduations |
| Number format | Display in %, decimal or scientific |
Thanks to these settings, you adapt the secondary axis to the dynamics of your data without distorting the visual perception.
Adjust layout and style
To make the chart more professional, make sure to:
- Standardize colors: bars vs lines, moderate contrast
- Add data labels on each series
- Insert a clear legend to distinguish axes and chart types
- Keep a readable font (Arial, Calibri 10–11 pts)
You can also play with 3D effects or textures, but be careful not to sacrifice readability!
Practical cases and tips
Here are two scenarios where the secondary axis changes the game:
Compare sales and satisfaction rate
Imagine a table listing, month by month, the volume of products sold and the customer satisfaction rate (from 0 to 5/5). The volume sometimes reaches 8,000 units while the satisfaction index remains between 3.8 and 4.7. By combining a histogram for sales and a curve for the rate, you immediately identify if a sales increase impacts satisfaction. To calculate these totals under conditions, nothing is more practical than the SUMIFS function before feeding your chart with the aggregated values.
Track leads and conversion via a Pivot Table
In a marketing tracking table, you can generate a combined chart from a Pivot Table. By adding slicers, you filter your data (source, campaign, region) and observe in real time the evolution of the number of leads (bars) and the conversion rate (curve). The secondary axis reveals micro-variations of the percentage, invisible on the primary axis.
Common mistakes and solutions
- Incomplete selection: if you forget a series, the chart refuses the secondary axis.
- Desynchronized scales: a graduation step too high or too low distorts interpretation.
- Poorly placed legend: check its positioning to avoid it hiding the data.
- Missing data: empty cells generate “gaps” in the curve or erratic bars.
Tip: double-clicking on any area of the axis directly opens the format menu. Time saved guaranteed!
Conclusion
Adding a secondary axis in a combined Excel chart is not just a gimmick: it is a powerful tool to reveal correlations between heterogeneous series. By following the steps of selection, configuration and customization, you obtain visuals that are both aesthetic and informative. Whether you report monthly sales figures, performance indicators or survey results, mastering this feature will enrich your reports and facilitate decision-making.
Next challenge? Explore the use of VLOOKUP to dynamically aggregate your series and automate the chart update. You will see that the combination of advanced functions and combined charts changes the game!