Microsoft Office Tutorials and References

In Depth Information

How to Set Up X-Axis Common Scaling

It’s very easy to set up any number of charts to use the same

values for their X axis. There are two frequently used ways to do

this. Both ways rely on a range of cells that contain the X axes

values you want to use.

AB

C

D

E

F

This figure illustrates one method.

Here, the SERIES functions in

both charts reference the same

range for their X-axis values.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

Mar-04

Jun-04 Sep-04 Dec-04

Firm 1

2

4

3

6

Firm 2

3

1

4

2

Max

3

4

4

6

Min

2

1

3

2

Firm 1

8

6

As discussed earlier, the SERIES

function contains these

arguments…

4

2

0

Mar-04

Jun-04

Sep-04

Dec-04

Firm 2

8

=SERIES(name_ref, categories,

values, plot_order)

6

4

2

0

…where “categories” typically

refers to the X axis.

Mar-04

Jun-04

Sep-04

Dec-04

Here are the two SERIES functions from the two charts:

=SERIES(Sheet1!$B$2, Sheet1!$C$1:$F$1, Sheet1!$C$2:$F$2, 1)

=SERIES(Sheet1!$B$3, Sheet1!$C$1:$F$1, Sheet1!$C$3:$F$3, 1)

Both of these SERIES formulas get their X-axis data from the range

C1:F1 in the figure. Typically, that range of dates would be

controlled by one value in one cell, perhaps named CurQtr. In this

instance, cell F1 would have the formula: =CurQtr. And cell E1

would have the formula:

=DATE(YEAR(F1),MONTH(F1)-3,1)

Cell E1 would be copied to the range C1:D1.