Absolute versus Relative References In Excel
(How to prevent your formulas from
updating on each row)
Excel has a great feature that allows you to quickly replicate formulas
down a column, across a row, over over a whole area. In later versions of
Excel it’s referred to as AutoFill.
Let’s look at a column of numbers accompanied by some calculations on those
numbers. When you first type in the formula, you see a reference to a range
as a letter and a number; here, A2.
(double-click the Fill Handle to automatically copy to the
end of the column!)
The black blob in the bottom right-hand corner of the cell (here circled
in red) is called the Fill Handle (no relation to Phil Handel, the
post-classical composer). If you place your pointer over the fill handle and
drag down, the formula will be copied into each cell you drag over. Note
that it’s not an exact copy… it’s better. For each row, the
formula will be updated to the appropriate cell. In row 3, the formula
becomes =A3*0.0825 and in row 4 it becomes =A4*0.0825, etc.
A nice feature in Excel also allows you to double-click the Fill Handle
if you just want to fill your formula all the way to the bottom of the
column of numbers in A.
What about a running total though? In the case of a running total, we
don’t want the formula changing too much. In row 3, our formula should say
=SUM(A2:A3) and in row 4, it should say =SUM(A2:A4). Notice that the 3
changed to 4, but 2 stayed the same! If we just drag our formula down the
column, it will show a sum of the last two rows all the way down the column.
The thing to do is to change the A2 relative reference (the
default in Excel) into an absolute reference. In other words,
you don’t want it to change depending on where in the spreadsheet you are.
Go into cell C2 and begin typing =SUM(A2 and then hit the F4 key. When
you do, Excel will add a dollar sign in front of the reference and your
formula will change to =SUM($A$2. Continue typing “:A2)" and hit return. Now
double-click on the fill handle and walk through the formulas created. They
should look like the ones here:
See how the absolute reference has remained the same, while the relative
one has updated on each row. You can use this same technique on either the
row or the column reference by hitting F4 repeatedly while typing in your
formula.
You’ll see the reference cycle through A2, $A$2, $A2, and A$2. The last
two are useful if you are filling a table with formulas and want the column
to update, but not the row or want the row to update, but not the column.
|
|