Click here for great value web hosting.

ALL ABOUT OFFICE

HOME

ACCESS

EXCEL

WORD

OUTLOOK

POWERPOINT




Search for
 

help


EXCEL

Adding a Secondary Axis

Sometimes you need to plot two sets of data on the same chart but they either have totally different units of measurement or the values are widely different.  If you try to plot small values like 25, 39, etc on the same graph as a set of data with values into the millions the small values will all end up along the x-axis as they will be virtually zero compared to the large values.  To get around this you can set up a secondary axis in Excel on which to plot the second category of values.

Suppose we want to plot sales of umbrellas on the same chart as the rainfall.  The figures for this run into the millions as shown below .

 

Rainfall

Umbrella Sales

Month

2000

2001

2002

2003

2000

2001

2002

2003

Jan

29.30

35.60

78.90

70.40

1,000,000

1,500,000

3,000,000

2,500,000

Feb

79.00

43.00

109.90

53.10

2,000,000

1,500,000

5,000,000

2,000,000

Mar

28.20

55.40

56.50

32.10

1,000,000

2,000,000

2,000,000

1,500,000

Apr

105.60

63.30

113.25

31.50

5,000,000

2,000,000

5,500,000

1,500,000

May

45.20

51.70

103.10

108.60

2,000,000

2,000,000

5,000,000

5,000,000

Jun

51.50

51.50

76.70

89.30

3,000,000

2,000,000

2,500,000

4,000,000

Jul

13.40

41.10

97.70

78.70

500,000

1,500,000

4,000,000

3,500,000

Aug

110.60

88.10

41.40

17.40

5,000,000

3,000,000

1,500,000

1,500,000

Sep

89.60

52.20

39.90

52.20

2,000,000

2,000,000

1,500,000

3,000,000

Oct

109.10

80.20

158.10

31.60

5,000,000

3,000,000

7,000,000

2,000,000

Nov

102.10

41.60

133.90

64.60

4,000,000

2,000,000

6,000,000

3,500,000

Dec

116.50

43.10

50.80

53.80

5,000,000

1,500,000

2,000,000

2,000,000

We can add the series for the umbrella sales using the copy and paste method but they now end up along the x-axis.  To set up a secondary axis, select one of the data series you want to plot on the secondary axis using the Chart Item selector on the Chart Toolbar and then press CTRL + 1 to format the series.  Click on the Axis section and put a bullet beside Secondary Axis as shown below .

Repeat this for all the series that you want to plot on the secondary axis.  If you need to set up labels for your secondary axis, click on the Chart drop-down menu and choose Chart Options.  You can set the labels for the secondary Y axis under the Titles section of this window as shown below.

Here is the finished chart, it looks a bit messy but we can change that by changing the chart type for umbrella sales, say, to columns instead of lines.

Click here to return to the Excel index.

webmaster@allaboutoffice.co.uk