Any cell references in formulas are adjusted as they are copied: If the formula =SUM(B3:B9) is copied from B10 to C10 and D10 it will become SUM(C3:C9) and SUM(D3:D9).

A

B

C

D

9

10

Total

SUM(B3:B9)

SUM(C3:C9)

SUM(D3:D9)

Example: The result of copying the formula in cell B10 to C10. The cell references are adjusted.

AutoFill to Copy Formulas

Select the area or cell to copy

Move the mouse to the tiny black square Its at the bottom right corner of the selected area. Your cursor will change to a small plus sign

Click and drag to highlight where to copy to

Release the mouse button The formulas will be copied and adjusted automatically. See Also “Using AUTOFILL To Make Copying Easy!”

“Moving And Copying Things”

Absolute Cell References & F4

Normally when cell references are copied, they are adjusted (as described in “Copying Formulas” - page 47). Sometimes you will want this reference to stay fixed. (For instance, when the interest rate is stored in one cell, and is to be used in all formulas.)
To keep a cell reference fixed, insert a $ sign before it in the formula.

Relative Cell Reference

d5*c2

Absolute Cell Reference

d5*$c$2

Thus if a formula in D9 reads d5*$c$1, when copied to E9 it will read e5*$c$1, still referring to the same C1 cell.

Insert the $ (Absolute Cell Reference ) sign by tapping the F4 key at the top of your keyboard!!

Creating A Formula With Absolute Cell References.

Select cell where the result of the formula is to appear

Type =

Select or type in first cell reference Does this cell reference need to be fixed?

Press the F4 key to put the dollars in

Type the maths bit such as + or *

Select or type in next cell reference Does this cell reference need to be fixed?

The first time you hit F4 when entering or editing a formula two dollar signs are put in, but if you hit it again Excel actually cycles through the following:

As you have seen Cell References can be absolute, but they can also be relative, which is without the dollar ($) sign and even a mixture of both! An example of mixed cell references is $A1 or A$1. $A1 refers to column A regardless of the position of the cell containing the formula. The 1 refers to the cell containing the formula.