See More About: , ,

How to Add A Second Axis to Your Charts in Excel

by

excelscreen How to Add A Second Axis to Your Charts in Excel

When using Excel and working with two data sets that differ greatly in range it can be difficult to chart those values due to the larger range of one of your data sets. A classic example of this would be your monthly data and your YTD values for each month. When you put these together in a chart the monthly data is diminished by the much larger YTD values. In order to overcome this you can assign each data set an axis.

The default for all your data sets is the Primary axis, you have the option of assigning your second data set to the second axis which makes your chart readable. Below you will find the instructions on how to add a second axis to an Excel graph.

If you have any comments or questions, please post them below.

Note: I used Excel 2003 for this demonstration, but it should hold true for 97-2007.

Graph without Second Axis

excelbef How to Add A Second Axis to Your Charts in Excel

The following steps were written assuming you have a chart in excel with more than one data set.


If you wish to view the step-by-step, click the pic tutorial icon ->pictuticon How to Add A Second Axis to Your Charts in Excel or you can follow the steps below.

1. Select a point on the graph for the data set you want to put on the secondary axis.

2. Right-click and select Format Data Series…

3. Click on the Axis tab and select the Secondary axis radio button

4. Click OK

5. Your selected data set should now reside on the second axis of your chart.

Graph with Second Axis Enabled

excelaf How to Add A Second Axis to Your Charts in Excel

See More About: , ,
Originally posted Jan 24, 2008

We close comments after 30 days on GilsMethod.com and ask that you post questions and issues on our Answers website.

This helps us better serve you by allowing us to keep track of unanswered questions that are usually hidden in long comment threads.

Answers.GilsMethod.com is a community-powered support website where you will be able to post questions, and other users including us will respond with expert advice.

So if you are having trouble with your PC, Mac, iPad, iPhone, or other piece of technology, head over to Answers.GilsMethod.com and ask away.

  • Jennifer

    This was very helpful – after reading several other sites, you had me fixed in 1 minute. THANK YOU!

  • Jennifer

    This was very helpful – after reading several other sites, you had me fixed in 1 minute. THANK YOU!

  • Jennifer

    This was very helpful – after reading several other sites, you had me fixed in 1 minute. THANK YOU!

  • http://intensedebate.com/people/gjperera gjperera

    @Jeniffer – I'm glad you found it useful and thank you for visiting.

  • http://intensedebate.com/people/gjperera gjperera

    @Jeniffer – I'm glad you found it useful and thank you for visiting.

  • http://intensedebate.com/people/gjperera gjperera

    @Jeniffer – I'm glad you found it useful and thank you for visiting.

  • caroline

    theres no option for Axis when i go on format data seris… on excel 2003

  • caroline

    theres no option for Axis when i go on format data seris… on excel 2003

  • caroline

    theres no option for Axis when i go on format data seris… on excel 2003

  • ATM

    Thanks so much.This is very helpful.

  • ATM

    Thanks so much.This is very helpful.

  • ATM

    Thanks so much.This is very helpful.

  • Pedro

    Gil, how is it going, I am working with 12 charts to a page. I need the graph areas of those charts along with the formatting to be the same. So I started off with one graph I copied it and sourced the information to the charts but that did not work because the axis had different numbers of digits for the different sources. Is there an easier way to format these charts? I am currently using office 2003. Would 2007 have a solution for this or may be an add in I can use to make identical charts that you would recommend.

  • Pedro

    Gil, how is it going, I am working with 12 charts to a page. I need the graph areas of those charts along with the formatting to be the same. So I started off with one graph I copied it and sourced the information to the charts but that did not work because the axis had different numbers of digits for the different sources. Is there an easier way to format these charts? I am currently using office 2003. Would 2007 have a solution for this or may be an add in I can use to make identical charts that you would recommend.

  • Pedro

    Gil, how is it going, I am working with 12 charts to a page. I need the graph areas of those charts along with the formatting to be the same. So I started off with one graph I copied it and sourced the information to the charts but that did not work because the axis had different numbers of digits for the different sources. Is there an easier way to format these charts? I am currently using office 2003. Would 2007 have a solution for this or may be an add in I can use to make identical charts that you would recommend.

  • Joseph

    secondary axis is not clickable

  • Joseph

    secondary axis is not clickable

  • Joseph

    secondary axis is not clickable

  • http://intensedebate.com/people/gjperera gjperera

    Can you be more specific? It should be clickable…what version of Excel are you using?

  • http://intensedebate.com/people/gjperera gjperera

    Can you be more specific? It should be clickable…what version of Excel are you using?

  • http://intensedebate.com/people/gjperera gjperera

    Can you be more specific? It should be clickable…what version of Excel are you using?

  • pano

    In the Format Data Series, Plot Series On –> Primary/Secondary Axes, both buttons unclickable, i.e. both options are disabled. In Excel 2007

  • pano

    In the Format Data Series, Plot Series On –> Primary/Secondary Axes, both buttons unclickable, i.e. both options are disabled. In Excel 2007

  • pano

    In the Format Data Series, Plot Series On –> Primary/Secondary Axes, both buttons unclickable, i.e. both options are disabled. In Excel 2007

  • jonah

    thanks this really worked!
    L

  • Kath

    Thanks for this! A quick question – what if the AXIS tab is locked. I cannot click the Secondary Axis option? Do I have to do something to unlock this?

    By the way, I am using 2003 Excel.

    Thanks in advance!

  • http://www.gilsmethod.com Gilberto J Perera

    Kath,

    The axis shouldn’t be locked…do you have full rights to the Excel spreadsheet. Does the option appear at all? Is it greyed out? Let me know.

  • http://www.gilsmethod.com Gilberto J Perera

    Kath,

    The axis shouldn’t be locked…do you have full rights to the Excel spreadsheet. Does the option appear at all? Is it greyed out? Let me know.

  • http://www.gilsmethod.com Gilberto J Perera

    Kath,

    The axis shouldn’t be locked…do you have full rights to the Excel spreadsheet. Does the option appear at all? Is it greyed out? Let me know.

  • Kath

    Hello again,
    Thank you for the prompt reply.

    Yep I have access to it. I right-clicked, and chose the Format Data Series then on the AXIS tab, the Plot Series On section is in grey area. It has two options : Primary Axis and my Secondary Axis. And it is currently bulleted on the Primary Axis.

    And I was just informed that the original excel file that I am trying to modify is a 2007 version. And I am currently using a 2003. I guess this would matter?

    Thank you again!

    Kath

  • http://www.gilsmethod.com Gilberto J Perera

    Kath,

    It shouldn’t matter because the feature is supported in Office 2003, however it could be the problem…can the person who is sending you the file save it as Excel 2003. I also found the instructions that are specific to Excel 2003 on the Microsoft Website:

    When the values in a 2-D chart vary widely from data series to data series in a 2-D chart, or when you have mixed types of data (such as price and volume), you can plot one or more data series on a secondary value (y) axis. The scale of the secondary axis reflects the values for the associated data series.

    1. On a chart sheet or in an embedded chart, click the data series that you want to plot along a secondary value axis.
    2. On the Format menu, click Selected Data Series.
    3. On the Axis tab, click Secondary axis.
    TIP – After you add a secondary value axis to a chart, you can also add a secondary category (x) axis, which may be useful in an xy (scatter) chart. Select a chart that displays the secondary value axis, and then click Chart Options on the Chart menu. On the Axes tab, select the Category (X) axis check box.

    Let me know if this sheds any light, I currently do not have Excel 2003 installed so I cannot confirm, last resort I can install it and if you don’t mind sending me the file I can try and figure it out for you. Have a great day.

    Via – Microsoft Office Help Page

  • http://www.gilsmethod.com Gilberto J Perera

    Kath,

    It shouldn’t matter because the feature is supported in Office 2003, however it could be the problem…can the person who is sending you the file save it as Excel 2003. I also found the instructions that are specific to Excel 2003 on the Microsoft Website:

    When the values in a 2-D chart vary widely from data series to data series in a 2-D chart, or when you have mixed types of data (such as price and volume), you can plot one or more data series on a secondary value (y) axis. The scale of the secondary axis reflects the values for the associated data series.

    1. On a chart sheet or in an embedded chart, click the data series that you want to plot along a secondary value axis.
    2. On the Format menu, click Selected Data Series.
    3. On the Axis tab, click Secondary axis.
    TIP – After you add a secondary value axis to a chart, you can also add a secondary category (x) axis, which may be useful in an xy (scatter) chart. Select a chart that displays the secondary value axis, and then click Chart Options on the Chart menu. On the Axes tab, select the Category (X) axis check box.

    Let me know if this sheds any light, I currently do not have Excel 2003 installed so I cannot confirm, last resort I can install it and if you don’t mind sending me the file I can try and figure it out for you. Have a great day.

    Via – Microsoft Office Help Page

  • http://www.gilsmethod.com Gilberto J Perera

    Kath,

    It shouldn’t matter because the feature is supported in Office 2003, however it could be the problem…can the person who is sending you the file save it as Excel 2003. I also found the instructions that are specific to Excel 2003 on the Microsoft Website:

    When the values in a 2-D chart vary widely from data series to data series in a 2-D chart, or when you have mixed types of data (such as price and volume), you can plot one or more data series on a secondary value (y) axis. The scale of the secondary axis reflects the values for the associated data series.

    1. On a chart sheet or in an embedded chart, click the data series that you want to plot along a secondary value axis.
    2. On the Format menu, click Selected Data Series.
    3. On the Axis tab, click Secondary axis.
    TIP – After you add a secondary value axis to a chart, you can also add a secondary category (x) axis, which may be useful in an xy (scatter) chart. Select a chart that displays the secondary value axis, and then click Chart Options on the Chart menu. On the Axes tab, select the Category (X) axis check box.

    Let me know if this sheds any light, I currently do not have Excel 2003 installed so I cannot confirm, last resort I can install it and if you don’t mind sending me the file I can try and figure it out for you. Have a great day.

    Via – Microsoft Office Help Page

  • Kath

    Oh no. No need to install a 2003. I really appreciate it. I have figured out how to add the secondary axis.

    Here’s what I did –
    1.) I selected the data I want to be the secondary axis and COPY
    2.) Clicked on the chart then EDIT – PASTE SPECIAL
    3.) then checked ADD CELLS AS and ticked NEW SERIES, VALUES IN = ROWS and all other boxes are unticked then OK
    4.) then clicked the new data series the right click FORMAT – AXIS TAB then SECONDARY AXIS.

    Thought I should share. I asked somebody from the excel forum. :)
    Thank thank thank you so much for the help!

    Kath

  • adam

    I would like to add a “Secondary Axis” but here is the problem:

    I right click on the data and click “Format Data Series”
    Click on “Series Options” tab
    Under “Series Options” says “Plot Series On”
    “Primary Axis”
    “Secondary Axis” with bullets next to each

    The words”Primary Axis” and “Secondary Axis” are greyed out, disabled, as well as the bullets. “Primary Axis” is the default.

    I have full rights to the file. Other files are not giving me this problem. Please help.

  • http://www.gilsmethod.com Gilberto J Perera

    Kath,

    Thank you so much for sharing your solution with others and with me. I am very glad you were able to resolve it.

  • http://www.gilsmethod.com Gilberto J Perera

    Kath,

    Thank you so much for sharing your solution with others and with me. I am very glad you were able to resolve it.

  • http://www.gilsmethod.com Gilberto J Perera

    Kath,

    Thank you so much for sharing your solution with others and with me. I am very glad you were able to resolve it.

  • http://www.gilsmethod.com Gilberto J Perera

    Adam,

    Try this solution which was submitted by another reader:

    1.) I selected the data I want to be the secondary axis and COPY
    2.) Clicked on the chart then EDIT – PASTE SPECIAL
    3.) then checked ADD CELLS AS and ticked NEW SERIES, VALUES IN = ROWS and all other boxes are unticked then OK
    4.) then clicked the new data series the right click FORMAT – AXIS TAB then SECONDARY AXIS.

    Let me know if it helps.

  • http://www.gilsmethod.com Gilberto J Perera

    Adam,

    Try this solution which was submitted by another reader:

    1.) I selected the data I want to be the secondary axis and COPY
    2.) Clicked on the chart then EDIT – PASTE SPECIAL
    3.) then checked ADD CELLS AS and ticked NEW SERIES, VALUES IN = ROWS and all other boxes are unticked then OK
    4.) then clicked the new data series the right click FORMAT – AXIS TAB then SECONDARY AXIS.

    Let me know if it helps.

  • http://www.gilsmethod.com Gilberto J Perera

    Adam,

    Try this solution which was submitted by another reader:

    1.) I selected the data I want to be the secondary axis and COPY
    2.) Clicked on the chart then EDIT – PASTE SPECIAL
    3.) then checked ADD CELLS AS and ticked NEW SERIES, VALUES IN = ROWS and all other boxes are unticked then OK
    4.) then clicked the new data series the right click FORMAT – AXIS TAB then SECONDARY AXIS.

    Let me know if it helps.

  • Diane

    gjperera / Kath
    Thank you for sharing on how to add the secondary axis.
    I send this useful link to my coworkers and they like it so much.

    Diane

  • http://www.gilsmethod.com Gilberto J Perera

    Diane,

    Glad you found the article useful and thanks for sharing.

  • http://www.gilsmethod.com Gilberto J Perera

    Diane,

    Glad you found the article useful and thanks for sharing.

  • http://www.gilsmethod.com Gilberto J Perera

    Diane,

    Glad you found the article useful and thanks for sharing.

Like Gil's Method on Facebook

Please enter your email address above.

Top Tech News

Giveaway: Get Blu-ray Decrypter to Help Safely Backup Your Blu-rays

If you’re building up an extensive collection of movies in the new Blu-ray format, chances are that you’ve spent a couple of bucks putting it together. To help protect that small fortune that you call a movie collection, there are a couple of apps out there that will help you backup these discs so that…Continue Reading

Giveaway: Take Your Favorite Movies to School with BDlot Video Converter

If you are looking for a quick, easy and FREE method to convert your favorite movies and TV shows to a format you can take with you anywhere, you need to check out BDlot Video Converter. BDlot Video Converter is part of a greater suite of applications that allows you to convert videos to any…Continue Reading

iTunes Match Beta Available for Developers

With the imminent launch of iOS 5 for the iPhone and the iPad, Apple is moving forward with its new music offering for iTunes, Match. iTunes Match stores your entire music library in iCloud (another new service from Apple debuting this fall). When you store your music in iCloud you can access it from anywhere…Continue Reading

Latest How-to Guides

How to Hide Morning and Night Hours in Google Calendar

One of the settings I always enjoyed in Outlook is the ability to customize work hours so that my available time (work time) would appear on the calendar as a reminder not to schedule anything past a certain time. Google has taken this concept event further and now allows users to hide those morning and…Continue Reading

How to Print iCal Calendars in Mac OS X

Just the other day I needed to print a monthly calendar in Mac OS X and realized that I had never done it before. When I opened the print dialog box I was amazed at the number of options available to print a calendar in iCal. So I decided to write this guide to help…Continue Reading

How to Create a Smart Folder in Mac OS

Have you ever found yourself looking for a particular set of files that are either named similarly or are of the same file type? Rather than perform a search in Mac OS to locate these files each time you want to access them, you have the option of creating a Smart Folder to simplify this…Continue Reading