Page for Notes

Statistical Analysis with Excel

CHAPTER 2

COPYING/CUTTING AND

PASTING FORMULAE

This chapter teaches the following topics:

” COPYING AND PASTING A FORMULA TO OTHER CELLS IN

THE SAME COLUMN

” COPYING AND PASTING A FORMULA TO OTHER CELLS IN

THE SAME ROW

” COPYING AND PASTING A FORMULA TO OTHER CELLS IN A

DIFFERENT ROW AND COLUMN

” CONTROLLING CELL REFERENCE BEHAVIOR WHEN

COPYING AND PASTING FORMULAE (USE OF THE “$”

KEY)

” USING THE “$” SIGN IN DIFFERENT PERMUTATIONS AND

COMPUTATIONS IN A FORMULA.

” COPYING AND PASTING FORMULAS FROM ONE

WORKSHEET TO ANOTHER

” SPECIAL PASTE OPTIONS

” PASTING ONLY THE FORMULA (BUT NOT THE FORMATTING

AND COMMENTS)

” PASTING THE RESULT OF A FORMULA, BUT NOT THE

FORMULA ITSELF

” CUTTING AND PASTING FORMULAE

” THE DIFFERENCE BETWEEN “COPYING AND PASTING“

FORMULAS AND “CUTTING AND PASTING” FORMULAS

34

Chapter 2: Copying/Cutting and pasting formulae

” SAVING TIME BY WRITING, COPYING AND PASTING

FORMULAS ON SEVERAL WORKSHEETS

SIMULTANEOUSLY

COPYING AND PASTING A FORMULA TO OTHER

2.1

CELLS IN THE SAME COLUMN

Often one wants to write analogous formulae for several cases. For

example, assume you want to write a formula analogous to the formula in

F223 into each of the cells F224 to F2352. The quick way to do this is to:

” 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.)

” Highlight the “pasted on” cells F224 to F235 and

” Choose 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 is pasted onto the cells F224 to F235 and the cell

The formula in F223 adds the values in cells that are 3 and 2 columns to the left (that

2

is, cells in columns in C and D.)

35

Statistical Analysis with Excel

references within each formula are adjusted3 for the location

difference between the “pasted on” cells and the “copied from” cell.

Figure 18: Pasting a formula

COPYING AND PASTING A FORMULA TO OTHER

2.2

CELLS IN THE SAME ROW

Select the range F223” F235 (which you just created in the previous sub“

section). Select the option EDIT/COPY. Choose the range G223” G235

(that is, one column to the right) and choose the menu option

EDIT/PASTE. Now click on any cell in the range G223” G235 and see

how the column reference has adjusted automatically. The formula in

The formula in the “copied cell” F223 is “C223 + D223” while the formula in the

3

“pasted on” cell F225 is “C225 + D225.” (Click on cell F225 to confirm this.) The cell

F225 is two rows below the cell F223, and the copying-and-pasting process accounts

for that.

36

Chapter 2: Copying/Cutting and pasting formulae

G223 is “D223 + E223” while the formula in F223 was “C223 + D223”.

The next figure illustrates this. Because you pasted one column to the