How to Add A Second Axis to Your Charts in Excel
Do you like this article?
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
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 ->
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
Published on January 24, 2008
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.
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.
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
Kath,
Thank you so much for sharing your solution with others and with me. I am very glad you were able to resolve it.
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
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
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!
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.
thanks this really worked!
L
In the Format Data Series, Plot Series On –> Primary/Secondary Axes, both buttons unclickable, i.e. both options are disabled. In Excel 2007
secondary axis is not clickable
Can you be more specific? It should be clickable…what version of Excel are you using?
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.
Thanks so much.This is very helpful.
This was very helpful – after reading several other sites, you had me fixed in 1 minute. THANK YOU!
@Jeniffer – I'm glad you found it useful and thank you for visiting.
theres no option for Axis when i go on format data seris… on excel 2003