So:

” The reference “C” became “D,” and

” The reference “D” became “E.”

Figure 19: Cell reference changes when a formula is copied and pasted

The examples in 2.1 on page 36 and 2.2 on page 37 show the use of “Copy

and Paste” to quickly replicate formula in a manner that maintains

referential parallelism.

COPYING AND PASTING A FORMULA TO OTHER

2.3

CELLS IN A DIFFERENT ROW AND COLUMN

Select the cell F223. Select the option EDIT/COPY. Choose the range

H224 (that is, two columns to the right and one row down from the copied

cell) and choose the menu option EDIT/PASTE. Observe how the column

and row references have changed automatically” the formula in H224 is

37

Statistical Analysis with Excel

“E224 + F224” while the formula in F223 was “C223 + D223”.

The next figure illustrates this. Because you pasted two columns to the

right and one row down, the cell references automatically shifted two

columns to the right and one row down. So:

” The reference “C” became “E” (that is, two columns to the right)

” The reference “D” became “F” (that is, two columns to the right)

” The references “223” became “224” (that is, one row down)

Figure 20: Copying and pasting a formula

CONTROLLING CELL REFERENCE BEHAVIOR

2.4

WHEN COPYING AND PASTING FORMULAE (USE

OF THE “$” KEY)

The use of the dollar key “$” (typed by holding down SHIFT and choosing

the key “4”) allows you to have control over the change of cell references in

the “Copy and Paste” process. The use of this feature is best shown with

some examples.

” The steps in copy and pasting a formula from one range to another:

” Click on the “copied from” cell F223.

” Select the option EDIT/COPY. (The menu can also be accessed by

right-clicking on the mouse or by clicking on the COPY icon.)

38

Chapter 2: Copying/Cutting and pasting formulae

” Choose the “pasted on” cell F219 by clicking on it, and

” Select the menu option EDIT/PASTE. (The menu can also be

accessed by right-clicking on the mouse or by clicking on the

PASTE icon.)

” Press the ENTER key.

” The formula “C219 + D219” will be pasted onto cell F219. (For a

pictorial reproduction of this, see Figure 21.)

Figure 21: The “pasted-on” cell

Change the formula by typing the dollar signs as shown Figure 22.

Figure 22: Inserting dollar signs in order to influence cell referencing

Copy cell F219. Paste into G220 (that is, one column to the right and one

row down). The dollar signs will ensure that the cell reference is not

adjusted for the row or column differential for the parts of the formula

that have the dollar sign before them4” see the formula in cell F220

(reproduced in Figure 23).

In this example, the parts are the “C” reference and “219” reference in “$C$219” part of

4

the formula.

39

Statistical Analysis with Excel

Figure 23: The “copied-from” and “pasted-on” cells with the use of the dollar sign

For the parts of the cell that do not have the dollar sign before them, the

cell references adjust to maintain referential integrity5.