8.5 Combo Chart with a Secondary Axis
Combo chart with a secondary axis with example — in 30 seconds
What: A combo chart shows two series with different chart types. The second series gets its own axis on the right.
Why: Sales in ₹ lakh (3 to 6) and orders in thousands (1,450 to 1,980) on one axis would flatten the sales bars.
Tiny example: Pune sales as columns on the left axis, Pune orders as a line on the right axis.
Combo chart with a secondary axis with example — 30 सेकंदात
काय: Combo chart दोन series वेगवेगळ्या chart प्रकारांनी दाखवतो. दुसऱ्या series ला उजवीकडे स्वतःचा axis मिळतो.
का: ₹ lakh मधले sales (3 ते 6) आणि हजारांमधल्या orders (1,450 ते 1,980) एकाच axis वर टाकल्या तर sales चे bars सपाट दिसतील.
छोटं उदाहरण: Pune sales डाव्या axis वर columns म्हणून, Pune orders उजव्या axis वर line म्हणून.
Combo chart with a secondary axis with example — 30 सेकंड में
क्या: Combo chart दो series को अलग-अलग chart प्रकार में दिखाता है. दूसरी series को दाईं ओर अपना axis मिलता है.
क्यों: ₹ lakh वाली sales (3 से 6) और हज़ारों वाले orders (1,450 से 1,980) एक ही axis पर हों तो sales के bars सपाट दिखेंगे.
छोटा उदाहरण: Pune sales बाएँ axis पर columns में, Pune orders दाएँ axis पर line में.
First look at the data (before → after):
Expected result — Chart setup you should end with
| Series | Chart type | Axis | Range shown |
|---|---|---|---|
| Sales (₹ lakh) | Clustered Column | Primary (left) | 3.1 to 5.6 |
| Orders | Line with Markers | Secondary (right) | 1,450 to 1,980 |
About this data: Made-up monthly ₹ sales and % margin for a Solapur bakery. Fictional figures for the combo chart.
Before table and formula as text
Before — Chart data (Pune, fictional)
| Month | Sales (₹ lakh) | Orders |
|---|---|---|
| Aug | 3.1 | 1450 |
| Sep | 3.4 | 1520 |
| Oct | 4.2 | 1710 |
| Nov | 5.6 | 1980 |
About this data: Chart-type reminder: columns for big ₹ amounts, line for a small % series on a second axis.
Formula / click path
Insert › Charts › Insert Combo Chart › Create Custom Combo Chart…
Sales → Clustered Column · Orders → Line with Markers + Secondary Axis
Do it step by step:
- Select the Month, Sales and Orders columns.
- Click Insert › Charts › Insert Combo Chart › Create Custom Combo Chart….
- Set Sales to Clustered Column.
- Set Orders to Line with Markers and tick Secondary Axis. Click OK.
- Add axis titles with + › Axis Titles: "Sales (₹ lakh)" and "Orders".
स्टेप बाय स्टेप करून बघा:
- Month, Sales आणि Orders columns select करा.
- Insert › Charts › Insert Combo Chart › Create Custom Combo Chart… वर click करा.
- Sales ला Clustered Column द्या.
- Orders ला Line with Markers द्या आणि Secondary Axis tick करा. OK करा.
- + › Axis Titles ने axis titles द्या: "Sales (₹ lakh)" आणि "Orders".
स्टेप बाय स्टेप करके देखें:
- Month, Sales और Orders columns select करें.
- Insert › Charts › Insert Combo Chart › Create Custom Combo Chart… पर click करें.
- Sales को Clustered Column दें.
- Orders को Line with Markers दें और Secondary Axis tick करें. OK करें.
- + › Axis Titles से axis titles दें: "Sales (₹ lakh)" और "Orders".
When two measures have very different scales (sales in ₹ lakh vs order count in thousands), use a combo chart.
Steps in Excel
- Select Month, Pune and Orders (Pune) columns (Ctrl-select non-adjacent columns).
- Insert › Charts › Insert Combo Chart › Create Custom Combo Chart…
- Pune → Clustered Column; Orders (Pune) → Line with Markers and tick Secondary Axis › OK.
- Add axis titles: + › Axis Titles › "Sales (₹ lakh)" and "Orders".
- Match colours: the line colour = its axis label colour to help reading.
Worked example. Pune sales (columns) and orders (line): in November both peak, but the AOV (sales ÷ orders) is also higher – Diwali baskets are bigger.
Ravindra Bagale's Tip
After adding a secondary axis, many students can't tell which series is on which axis, and the manager misreads it. Give both axes titles and match the colours. Don't cram three or four measures into one combo – two are enough.
Ravindra Bagale's Tip – मराठी
Secondary axis लावल्यावर कोणती series कोणत्या axis वर आहे हे बऱ्याच students ना कळत नाही, आणि manager चुकीचा अर्थ काढतो. दोन्ही axes ला titles द्या आणि रंग match करा. तीन-चार measures एकाच combo मध्ये घुसडू नका – दोन पुरेसे.
Ravindra Bagale's Tip – हिंदी
Secondary axis लगाने के बाद बहुत से students को समझ नहीं आता कि कौन सी series किस axis पर है, और manager गलत मतलब निकाल लेता है. दोनों axes को titles दो और रंग match करो. तीन-चार measures एक ही combo में मत ठूँसो – दो काफ़ी हैं.
Worked example — plot ₹ sales and % returns on one chart
Why
₹ sales are large (2,50,000) and return rate is small (2.5%). On one axis the % line looks flat. A secondary axis fixes that.
What
A combo chart draws one series as columns and another as a line. The line can use a secondary vertical axis.
How
- Make a small table: Month | Sales ₹ | Return % for Jan–Jun (fictional Solapur bakery run by Meera Joshi).
- Select the table → Insert → Combo Chart → Clustered Column – Line on Secondary Axis.
- If Excel put both on one axis: Chart Design → Change Chart Type → Combo. Tick Secondary Axis for Return %.
- Format Sales as ₹ and the secondary axis as 0%.
- Add a clear chart title: Solapur bakery — Sales ₹ vs Return %.
Formula
Insert → Combo Chart → Clustered Column – Line on Secondary AxisMeaning: Secondary axis = two scales on one chart. Use it when the series have very different sizes (₹ vs %, or units vs $).
Practice tasks (10+)
Try these on a blank sheet. Open a hint only if you are stuck. No quiz — just practice.
1. Build a combo chart: columns for Sales ₹, line for Margin %.
Hint: Insert → Combo Chart. Put Margin % on the secondary axis.
2. Move Return % to the secondary axis after the chart is made.
Hint: Change Chart Type → Combo → tick Secondary Axis.
3. Format the left axis as ₹#,##0 and the right axis as 0.0%.
Hint: Click each axis → Format Axis → Number.
4. Add a chart title that names both measures.
Hint: Click the title. Write Sales ₹ and Return % so a new reader is not confused.
5. Swap the line to markers only (no connecting line).
Hint: Format Data Series → Line → No line; Markers on.
6. Make a combo for London store: Revenue £ and Orders count.
Hint: Same method — £ on columns, Orders on a secondary axis line.
7. Why is a secondary axis risky in a meeting?
Hint: People may think both series share one scale. Label axes clearly.
8. Create the same chart from an Excel Table (Ctrl+T).
Hint: When you add a July row, the chart should grow after refresh.
9. Change column colour to Excel green and the line to orange.
Hint: Click series → Format → Fill / Line colour.
10. Add data labels to the % line only.
Hint: Click the line → Chart Elements → Data Labels.
11. Build a combo with € revenue and NPS score (0–10).
Hint: NPS needs the secondary axis because it is a tiny scale.
12. Export the chart as a picture for WhatsApp.
Hint: Right-click chart → Save as Picture.
Loading practice tasks...
- Total clicks (Easy): Add up the "Clicks" column.
- Average spend (Easy): Average of the Spend (€) column, rounded to 2 decimals.
- Rows in Google (Easy): How many rows have Category/group "Google"? (the "Platform" column)
- Largest clicks (Easy): The largest value in the "Clicks" column.
- Clicks for Google (Medium): Total of the Clicks column where Platform is "Google".
- Google in Paris (Medium): Total of the Clicks column where Platform is "Google" and City is "Paris".
- Best in Google (Medium): Largest value in the "Spend (€)" column where "Platform" is "Google".
- Value of row 2 (Medium): Multiply the first-row Clicks by the first-row Spend (€).
- Look up Neha Gupta (Medium): The Clicks for "Neha Gupta" (use a lookup on the "Name" column).
- Total value (Hard): Sum of "Clicks" times "Spend (€)" for all rows.
- Share of Google (Hard): Share of the "Clicks" column that is "Google", rounded to 3 decimals.
- Total "Clicks" for LinkedIn (Hard): Add up the "Clicks" column for the rows where "Platform" is "LinkedIn".
- Above average? (Hard): "Above" if the "Spend (€)" value in the first row is above the average of the "Spend (€)" column, else "Below".
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
