Relative and Absolute Cell References

Learning how to effectively use cell references can make your experience with Excel much easier. This is particularly true if you plan on performing more than one calculation on a table as it allows you to copy one formula into adjacent cells and keep the references to the first still the same (absolute) or changed (relative). 

Cell references in Excel use the dollar sign ($) to denote one as either being relative or absolute. 

You may recall the below list from our recent post on SUMIF/SUMIFS. Let's use it again to show how cell referencing works.

	A	B	C	D
1	Date	Store	Item	Cost (dollars)
2	Monday	McDonald's	Lunch	8.54
3	Tuesday	Rah Sushi	Lunch	23.45
4	Tuesday	Target	Clothing	13.21
5	Wednesday	Kmart	Clothing	45.41
6	Wednesday	McDonald's	Lunch	7.45
7	Thursday	Circle K	Gas	43.98
8	Friday	McDonald's	Dinner	12.32

We're going to first do a simple SUMIF calculation to total the amount spent on each day of the week. We'll use a combination of absolute and relative cell references to complete this.

	A	B	C	D	E	F
10		Monday	Tuesday	Wednesday	Thursday	Friday
11	Total	$8.54	$36.66	$52.86	$43.98	$12.32

By entering correct references in cell B11, we can simply copy the formula into C11, D11, E11, and F11, and the references will automatically change. Here's what we put in B11:

B11:=SUMIF($A$2:$A$8, B$10, $D$2:$D$8)

The $ preceding the number or letter has an important significance. It says "keep the reference to this column or row untouched when you copy and paste it into an adjacent cell". For example, the set of cells "$A$2:$A$8" will always stay the same if you copy this formula and paste it in another cell. If it had only said "A2:A8" (without absolute references) and you moved pasted the formula one cell to the right, the entire reference would automatically shift to "B2:B8". Allowing it to move is called relative referencing.

You can also do a mix of absolute and relative referencing. For example, the second parameter "B$10" holds the row 10 constant but allows the column to shift.

To complete the rest of the table, simply copy cell B11 (the actual cell rather than the text that makes up the formula) and paste it into cells C11:F11.

Inserting References

There are a couple of ways to enter cell references. First, you can simply type a '$' as you're entering your formula in the function line. This is tedious and not ideal.

The best way to do cell referencing is to use the shortcut (F4 on Windows and ⌘-T in Mac) to toggle through the four possible scenarios.

For example, if you are typing in a cell the formula "=A2" and you hit the shortcut key, it will toggle through the following four possibilities:

1. =$A$2 - holds both column A and row 2 constant
2. =A$2 - holds only row 2 constant and allows column A to float
3. =$A2 - holds only column A constant and allows row 2 to float
4. =A2 - both column A and row 2 can freely float

Let's try a slightly different example:

	A	B	C
13		Option 1	Option 2
14	Lunch	39.44	39.44
15	Clothing	58.62	58.62
16	Gas	43.98	43.98
17	Dinner	12.32	12.32

Option 1 and Option 2 have slightly different formulas but yeild the same results. Here are the two formulas and why the difference is irrelevant:

B14:=SUMIF($C$2:$C$8, $A14, $D$2:$D$8)

C14:=SUMIF($C$2:$C$8, A14, $D$2:$D$8)

The minor difference is with the second parameter (A14) - the B14 formula has an absolute cell reference on row A while the C14 formula does not. In this case, you are copying the formulas down (rather than across) into rows 15, 16, and 17. You are keeping your formulas in the same column and consequently the column references in your formulas have no reason to change. This is why the dollar sign in front of your "A14" is not meaningful.

If instead you had transposed your table and had four Items listed across four columns rather than just in column A, the column reference would have been necessary.