3rd Y axis in Excel Graph

[amberlee]

I am graphing 4 sets of data in a chart. I have the two axis chart set up, but to really display what I want, I would love to have a third axis.

Can this be done?

 

[Airfix9]

Calling Mr Peltier!!

http://peltiertech.com/Excel/Charts/TertiaryAxis.html

 

[amberlee]

I googled and found this website as well. But it seems to not recommend doing the 3rd axis, and in this case I would like to.

Can it be done in Excel, without extra software, our company is pretty strict on extra downloads.

 

[gardnertoo]

You can do it, but it takes a little work. Begin by plotting the first two series, and move the second one to the secondary axis. The third series is where all the work comes in. The basic idea is that your third Y axis won't be a true axis, but another series with all the same value of X so as to plot as a vertical line. You'll add this series to the chart, with chart labels (using the XY Chart Label utility) to display the scale numbers along side it. You also will not be charting the true third series data; you will create a "dummy" series which scales your third series to plot within the range of the secondary axis, and plot that. Hopefully the example below makes it clear:

Book1
	A	B	C	D	E	F	G	H	I	J
2					Scale min	0	100	-20
3					Scale max	5	400	20
4	Third Axis				Scales:	0-5	100-400	-20 - +20	0-5
5	X values	Y values	Y labels		Series:	Y1	Y2	Y3	Y3 converted to secondary axis units
6	10.5	100	-20		1	4.95	202	-16.36	127.33
7	10.5	130	-16		2	0.76	208	10.28	327.09
8	10.5	160	-12		3	1.70	345	16.47	373.56
9	10.5	190	-8		4	3.39	301	14.95	362.10
10	10.5	220	-4		5	4.04	237	19.86	398.93
11	10.5	250	0		6	2.01	392	14.83	361.23
12	10.5	280	4		7	0.58	370	-1.96	235.27
13	10.5	310	8		8	2.95	311	-17.57	118.20
14	10.5	340	12		9	1.05	158	8.00	309.96
15	10.5	370	16		10	1.52	104	-17.64	117.70
16	10.5	400	20			real data	real data	real data	dummy data
Sheet1

The cells with borders are the three plotted series. The range off to the left creates the "axis" for the third Y axis. A drawback: if you like to show the point value as the data label, you can't do that with your "dummy" third series. Instead of displaying -16.36 for the first data point, the value 127.33 would be shown.

 

[amberlee]

This is an excellent idea. Do you have details on "using the XY Chart Label utility".

I think I have used this int he past but recently got a new computer at work and no longer have it. Can you send me a web link to some info on it, or the download link. Thank you!

 

[gardnertoo]

Here's the download link.

 

[Jadamz]

Just draw two charts and put them on-top of each other.

Draw graph <#1> and graph <#2> first.
Then change the background colour of <#2> (of both the chart area and the chart background) to "no fill".
Then drag it over the top.

You will also have to create a blank row of data, so that the bars on graph <#1> start late, so that there is room for the two axis on the left.
e.g. if plotting 12 months of data, Jan-15 to Dec-15, put an extra month in the data array for Dec-14 and make it have no data in it.

I also like to make the colour of the axis writing match the colour of the bars, so people know what scale belongs to which bars.

 

[gustridge]

Gadnertoo - This looks like a great solution to a problem I have been trying to solve myself.

Can you please explain how you converted the Y3 data into the dummy data, I cant figure it out!

 

[Akmialra]

Here's the download link.

Do you make youtube videos, so I can watch it, just wondering !!