• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel Chart Secondary Axis

You are here: Home / Excel Charts / Excel Chart Secondary Axis
Excel Chart Secondary Axis
August 3, 2011 by Mynda Treacy

One of the Excel questions I get asked often is; how do I add a secondary axis to my chart? It’s actually quite easy but there is a trick to it.

Excel Secondary Axis Trick Step 1

Excel Secondary AxisLet's take this data. Now, the first thing you want to do is simply insert your chart. Insert > Charts > Select Line Chart.

I hear you…you might not want a line chart, but trust me this is the easiest way to perform the secondary axis manoeuvre. You can change it to the chart type you want after you’ve inserted the secondary axis.

Now, what you may find is that you can’t even see the data you want to move to the secondary axis since it’s on a massively different scale.

As you can see in the example below the percentages are so small you can’t even see the line for them. Note: this is in Excel 2010, but in Excel 2007 you can typically always see the line at the bottom.

Tip: as a general rule the line that you want to plot on the secondary axis is generally the data on the smaller scale. So in this example it’s the cumulative percentage.

Excel Secondary Axis

Excel Secondary Axis Trick Step 2

If you can’t see the line you want to plot on the secondary axis at the bottom of your chart simply squash the chart until it comes into view. Click the chart > grab the handle on the right side and drag to the left.

Voila! Now your chart should show the second line at the bottom like this:

Excel Secondary Axis

Alternatively you can select it via the Chart Tools: Format tab, in the 'Current Selection' group click on the drop down list to reveal all of the chart components:

Excel chart tools

Excel Secondary Axis Trick Step 3

Click on the line you want to plot on the secondary axis > Right-click > Format Data Series > Series Options > Secondary Axis.

Tip: If you’ve got Excel 2010 you can double click the line to open the Format Data Series dialog box. That’ll save you a few clicks 🙂

Excel Secondary Axis

Now your chart will look like this. Ugly! But now that you have your secondary axis you can go about fixing the formatting and change the chart type for the Sales value to your preferred chart etc.

Excel Secondary Axis

Here’s what I’ve done:

Excel Secondary Axis

What can I interpret from my chart?

  1. Monthly sales are higher in the first half of the year.
  2. Over 50% of my sales are achieved by May
  3. Over 80% of the annual sales are made with only 2/3rds of the year gone.

If you tried to get that same insight from a plain table of numbers it would take you a lot longer than the few seconds you can look at a chart and understand what’s going on.

The chart formatting steps I took:

  1. Removed the grid lines.
  2. Widened the chart.
  3. Changed my sales data to a column chart.
  4. Changed my number format to thousands.
  5. Modified my legend to show ‘Sales $’000’.
  6. Moved my legend to give space for my axis labels so they aren’t vertical aligned.
  7. Changed my secondary axis maximum to 1.
  8. Added data labels to my secondary axis to aid with interpretation.
  9. Removed every second data label to reduce clutter.
  10. Changed the colours of my chart to allow labels to be more easily read.

Those 10 steps took me less than 2 minutes.

Enter your email address below to download the sample workbook.



By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the workbook here and try it for yourself. Note: This is a .xlsx file. Please ensure your browser doesn't change the file extension on download.

If you’d like to learn the exact steps to make these changes you’ll find comprehensive video tutorials in our Premium Excel Training. Click here to sign up.

Excel Chart Secondary Axis

More Excel Charts Posts

excel speedometer charts

Excel Speedometer Charts

How to build Excel Speedometer Charts or Gauge Charts as they're also know, why they are BAD and what to use instead.
burn up burn down charts

Excel Project Management Burn Down and Burn Up Charts

Excel Burn Down and Burn Up Charts are easy to make with line or scatter charts. They are useful for monitoring the progress of a project.
wee people font charts

Excel WeePeople Font Charts

Excel WeePeople Font Charts are a nice change from generic shapes for waffle charts, bar/column charts and more.
excel dot map charts

Excel Dot Map Charts

Interactive Excel dot map charts are not built-in, but with some creative use of Excel’s built-in tools we can create something unique.
Excel S Curve Charts

Excel S-Curve Charts

Easy Excel S-curve Charts made with PivotTables for project management. Track progress by including budget amounts.
chart axis switch

Excel Chart Axis Switch

Use radio button form controls to create an Excel chart axis switch enabling you to toggle pannel charts between same axis and own axis.

Excel Charts with Shapes for Infographics

Excel Charts with Shapes for Infographic styling and increased interest in your charts. Easy to insert but there are a few tricks required.
excel pyramid chart

Excel Pyramid Charts

Excel Pyramid charts are useful for visualising demographic data across multiple categories. Let’s look at 3 ways we can build them in Excel.

Highlighting Periods in Excel Charts

Highlighting Periods in Excel Charts helps your users interpret them more quickly and or focus their attention on a point or area.
stacked bar waffle chart

Stacked Bar Excel Waffle Charts

Stacked Bar Excel Waffle Charts are an alternate to using conditional formatting to build waffle charts, and some say they're easier.
Category: Excel Charts
Previous Post:Save Time with Hyperlinks for Word, Excel and OutlookSave Time with Hyperlinks for Word, Excel and Outlook
Next Post:Excel Chart Axis Label TricksExcel Chart Axis Label Tricks

Reader Interactions

Comments

  1. Deirdre Leigh

    August 24, 2018 at 8:39 pm

    These are great ideas but I’m looking to add in a line to separate sections of a chart. I’ve tried adding an xy line but it’s working as I want it (it won’t go where I want it to!). Can it be done?

    Reply
    • Mynda Treacy

      August 24, 2018 at 9:23 pm

      Hi Deirdre,

      You can add error bars to your XY scatter chart for vertical separators. It’s a bit complicated to explain step by step here, but if you get stuck you can post your question and Excel file on our forum where we can help you further.

      Mynda

      Reply
  2. rick

    February 3, 2016 at 3:01 am

    Thx, but i’m looking for how to add a title to the secondary axis. Haven’t been successful yet..

    Reply
    • Mynda Treacy

      February 3, 2016 at 9:27 am

      Hi Rick,

      I’ll assume you have Excel 2010: click on the chart > Chart Tools: Layout tab > Axis Titles. There you’ll find the options to turn on/off the secondary axis title.

      Let me know if you get stuck or have a different version of Excel.

      Kind regards,

      Mynda

      Reply
  3. Rock poth

    October 11, 2014 at 4:57 am

    What a great post. Can you please show step by step of how you removed the data labels to every other. thanks
    regards
    Rock

    Reply
    • Mynda Treacy

      October 11, 2014 at 8:17 pm

      Hi Rock,

      To delete select labels you click on the labels once and then click the one you want to delete again. This will select just one of the labels and you can then press the DELETE key to remove it 🙂

      Kind regards,

      Mynda

      Reply
      • Rock poth

        October 16, 2014 at 6:15 am

        Wow, thank you so much
        Regards,
        rock

        Reply
  4. Thiyagu

    July 21, 2014 at 7:06 pm

    Hi Mynda,

    Thanks for the very useful explanation.
    The question I’m going to ask has already been asked, but I could not find the answer here, hence asking the same question.
    1. My Primary Axis has numbers.
    2. My Secondary Axis has % – almost the same as in your example, except that my Primary Axis has negative numbers. The % in secondary axis is cumulative, hence positive only.

    My issue is that Primary axis’s “0” and Secondary axis’s “0%” is not on same lines. How could I do this?
    This is how it is:
    3 _______________ 50%
    2 _______________ 40%
    1 _______________ 30%
    0 _______________ 20%
    -1 _______________ 10%
    -2 _______________ 0%

    This is how i want:
    3 _______________ 30%
    2 _______________ 20%
    1 _______________ 10%
    0 _______________ 0%
    -1 _______________
    -2 _______________

    Thanks & Regards
    Thiyagu

    Reply
    • Mynda Treacy

      July 21, 2014 at 8:04 pm

      Hi Thiyagu,

      Thanks for providing an example.

      In order to align the zeros on both axes at the same level you need to fix the Minimum and Maximum for both axes to the same range of values. e.g. the Primary axis must have a max of 6 and min of -3 and the secondary axis will have a max of 60% and min of -30%.

      You will find that the zero will then be on the same line.

      To set your Min and Max select the primary axis > right-click > format axis > Axis Options – set min and max. Repeat for Secondary Axis.

      Kind regards,

      Mynda

      Reply
      • Thiyagu

        October 13, 2014 at 1:10 am

        Hi Mynda,

        I never thanked you for the immediate and wonderful response to my query. For many months now I have been using the tip you had given me to adjust and align the primary and secondary axis.

        My hearfelt thanks to you.

        Warm Regards

        Thiyagu.

        Reply
        • Mynda Treacy

          October 13, 2014 at 9:53 am

          Hi Thiyagu,

          Thank you for your kind words. You’ve made my day 🙂

          Mynda

          Reply
  5. Alison

    June 12, 2014 at 2:24 pm

    Hi Mynda,

    I’m trying to created a two sided two column chart but in excel 2010 one column gets hidden behind the other. I can see that the column/line combination works but do you have a suggestion for two colums side by side?
    Thanks

    Reply
    • Mynda Treacy

      June 12, 2014 at 7:46 pm

      Hi Alison,

      It sounds like the Series Overlap is set to 100%. To change it right-click on the series > format data series > change Series overlap to -40% of to the desired effect.

      Kind regards,

      Mynda

      Reply
  6. Brandon

    October 1, 2013 at 12:33 am

    This is an excellent description of ways to add a secondary vertical axis in Excel 2010. However, what I am seeking to know is whether there is a way to add a secondary vertical axis for the same data?

    I have a single set of lines in a line graph, and on the primary vertical axis (left y-axis) I have one set of units. I would like a different set of units on the secondary axis (right y-axis) which are used to showcase the same set of data.

    For example, m3 on the primary vertical axis and acre-feet on the secondary vertical axis; as m3 are substantially larger than acre-feet, the primary vertical axis would have less space between each tick mark while the secondary vertical axis would have more space between each tick mark.

    Excel 2010 seems to want to plot a second set of data for the secondary axis. There are no data sets to serve as a secondary set, just the original data displayed for different end-users who use different units.

    Any help on this matter is much-appreciated. Thank you.

    -Brandon

    Reply
    • Mynda Treacy

      October 1, 2013 at 7:20 pm

      Hi Brandon,

      That’s an interesting question. I’d say you have to add a second series of data for your acre-feet axis (you can use a simple formula to convert the m3 to feet).

      Once the series is added you can then plot it on then secondary axis and then hide it by formatting the fill/line (depending on your chart) with no colour.

      I hope that helps.

      Kind regards,

      Mynda.

      Reply
  7. Mourad Limame

    September 1, 2013 at 9:12 pm

    dear treacy,

    could you please help. I need to draw a diagram from a table but i need to have a choice of what i need to be in the X axis and Y axis. thanks in advance

    Reply
    • Mynda Treacy

      September 2, 2013 at 7:39 am

      Hi Mourad,

      I’m not sure what you mean by ‘draw a diagram’ using a chart, but you can switch the axes by right clicking on the chart > select data > Switch Row/Column.

      I hope that helps.

      Kind regards,

      Mynda.

      Reply
  8. Poltak

    July 9, 2013 at 9:58 am

    Mynda,
    I really thank you. It is 7.00 AM in the morning and I have to submit a report with graphics like above. I didn’t know how to make it before, and I came to your website.

    Reply
    • Mynda Treacy

      July 9, 2013 at 1:11 pm

      You’re welcome, Poltak 🙂 Glad I could help.

      Reply
  9. venkatraman r

    June 21, 2013 at 4:17 pm

    very helpful & useful tips..

    Reply
    • Mynda Treacy

      June 21, 2013 at 4:43 pm

      Thanks, Venkatraman 🙂

      Reply
  10. Amy Houke

    March 23, 2013 at 5:27 am

    I totally track with this and it all makes sense but the only option in the Series Options for me is Gap Depth, no ‘Plot Series On’ option.

    Reply
    • Carlo Estopia

      March 23, 2013 at 12:34 pm

      Hi Amy,

      If you have further questions,
      try sending it with us : HELP DESK.

      Cheers.

      CarloE

      Reply
  11. Helen

    March 21, 2013 at 2:05 am

    Hi,
    THanks for this. I have followed your steps and my graph works, but my secondary axis contains some negative numbers. I’d like the horizontal axis to hit zero on both the the primary and secondary axis (so the secondary axis and the negative numbers should hang below the graph, if you know what I mean). I have tried setting the min and max and also tried changing the horizontal axis cross but none of these options seem to work – instead I have a graph with the secondary axis information “floating” in mid air, which I think is much more difficult to read, and just looks a bit daft. Any ideas?

    Reply
    • Carlo Estopia

      March 21, 2013 at 6:28 pm

      Hi Helen,

      Let’s address this one at a time.
      1) Regarding the “negative” issue, You must use the set of values with some negative ones with your primary axis.
      2) If you mean you want your lines/series touch the vertical axis, you must format your horizontal axis:
      Format Axis, Position Axis: On Tick Marks.

      Of course, I couldn’t really diagnose this without seeing your file.
      Why don’t you send it to HELP DESK.

      Cheers.

      CarloE

      Reply
  12. Kat Swalwell

    December 21, 2012 at 7:57 pm

    Hello
    I can plot my two data sets onto a line chart to produce two lines but when i click on one data series (series 2) and create a secondary axis, the secondary axis appears but my series 1 data disappears and I cant find out how to get both lines on with a secondary axis added! Hope this makes sense
    Please help
    thanks

    Reply
    • Kat Swalwell

      December 21, 2012 at 8:00 pm

      Ignore that previous comment!
      i worked it out, the scale was wrong so as soon as i changed that, the line appeared along the bottom 🙂
      thanks anyway

      Reply
      • Mynda Treacy

        December 21, 2012 at 8:09 pm

        Glad you figured it out, Kat 🙂

        Reply
  13. Farah

    December 6, 2012 at 9:51 pm

    Dear Sir,
    How can I move secondary axis?
    Thanks

    Reply
    • Mynda Treacy

      December 7, 2012 at 10:01 am

      Hi Farah,

      I’m not sure where you want to move it to. Perhaps you want a different chart style? Alternatively all axis options can be found on the Layout tab in the Chart Tools on the Ribbon.

      Kind regards,

      Mynda.

      Reply
  14. Nidhi Jain

    October 9, 2012 at 8:01 pm

    Thanks this was really good.. and I could get me work soon.

    Regards,
    Nidhi

    Reply
    • Mynda Treacy

      October 10, 2012 at 9:45 am

      Cheers 🙂

      Reply
  15. Wendit

    September 6, 2012 at 6:47 am

    To add two or more axes to Excel, try Multy_Y or EZplot from Office Expander (www.OfficeExpander.com).

    There is a free demo version to try.

    Cheers.

    Reply
    • Mynda Treacy

      September 6, 2012 at 1:16 pm

      Thanks for sharing, Wendit.

      Reply
  16. stuart

    August 23, 2012 at 1:09 am

    Thanks!

    Reply
    • Mynda Treacy

      August 23, 2012 at 8:16 pm

      You’re welcome, Stuart 🙂

      Reply
  17. naresh sharma

    August 7, 2012 at 5:46 pm

    I want to create a chart say for following data:
    Ldg Rate ttl cgo Time in Hrs
    2000 2000 1
    5000 7000 2
    5000 12000 3
    5000 17000 4
    5000 22000 5
    5000 27000 6
    5000 32000 7
    5000 37000 8
    5000 42000 9
    5000 47000 10
    5000 52000 11
    2000 54000 12

    0 1hr 2hr 3hr 4hr 5hr 6hr 7hr 8hr
    ———x——–x——-x——–x——x——-x——-x——x—-[Cargo 1]

    ———x——–x——-x——–x——x——-x-[Cargo 2]

    ———x——–x——-x——–x——x-[Cargo 3]

    0 2K 7K 12K 17K 22K 27K 32K 37K
    But I want to show Ttl cgo and time on X-Axis. In short, I want one bar representing value of Cgo corrosponding to Time in Hrs. Is it possible??? I had spent enough time on this. Pls help.

    Reply
    • Mynda Treacy

      August 7, 2012 at 10:12 pm

      Hi Naresh,

      You can use a bar chart with a secondary axis for the time.

      You only need the last values for each cargo load. Your data table will look like this:

      Load Amount Time
      Cargo 1 37000 8
      Cargo 2 32000 7
      Cargo 3 47000 10

      Set the minimum and maximum values for each axis as follows:

      Amount min: 0, max: 47000
      Time min: 0, max: 10

      I hope that helps.

      Kind regards,

      Mynda.

      Reply
  18. BSJ

    June 12, 2012 at 1:43 am

    Great info presented clearly! Thank you!

    Reply
    • Mynda Treacy

      June 12, 2012 at 2:23 pm

      Thanks BSJ 🙂

      Reply
  19. D NARAYANA

    March 25, 2012 at 8:09 pm

    QUITE INFORMATIVE, THANKS FOR THE SERVICE

    Reply
    • Mynda Treacy

      March 27, 2012 at 2:59 am

      You’re welcome 🙂

      Reply
  20. Elisha

    March 7, 2012 at 9:49 pm

    How do you do the same thing but have the secondary axis be horizontal (X-axis). I can not seem to find a place to change vertical to horizontal (Excel 2010 Mac)
    Thanks!

    Reply
    • Mynda Treacy

      March 9, 2012 at 12:41 pm

      Hi Elisha,

      If you want two horizontal axes you can use a Bar Chart and then plot one of your series on a secondary axis. Essentially you still have one X axis and two Y axes but the Y axes are horizontal. I’m not sure how to do this on a Mac, sorry.

      Kind regards,

      Mynda.

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Shopping Cart

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now
  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
 
  • About My Online Training Hub
  • Contact
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x