Microsoft Excel, like most spreadsheets, has a really convenient feature. When you copy any given cell into another cell, Excel will automatically adjust the formula references to other cells so refer to cells in the same relative position.
First, a look at Relative Addressing
In the example below, I copied the formula in cell C2 into cells C3 through C8.
Excel automatically adjusted the formulas so that each formula referenced cells in the corresponding locations (columns a and b — it changed the row numbers) to the cells referenced by the cell that I was copying.
Let’s look at another example of relative addressing. In this case, I’m creating a running subtotal of the values in columns a and b.
Absolute addressing is very different than relative addressing. In some cases, relative addressing makes creating our spreadsheets much easier. In others, though, relative addressing changes cell references that we want to be constant.
For example, if we had a string of related numbers and wanted to find out the percentage each is of the total, we’d need to divide them by the sum. Rather than typing each formula manually, we can use absolute addressing for the cell that has the sum.
Instead of automatically changing the formula to point to a different cell, when we copy a cell whose formula uses absolute addressing to refer to a cell, the new copies still point to the same cell.
Excel and OpenOffice (and Lotus 1-2-3 and probably others, too) use the dollar sign ($) to indicate an absolute reference.
We can reference the column absolutely, the row absolutely or both the column and the row. The possibilities are:
- C2 — normal, relative addressing
- $C2 — absolute addressing of column C (new copies of the formula still refer to column C
- C$2 — absolute addressing of row 2 (new copies of the formula still refer to row 2, and
- $C$2— absolute addressing of column C and row 2 (new copies of the formula still refer to cell C2
Absolute Addressing Example
Continuing with the same example, let’s take the first running subtotal (cell G2) and calculate its percentage of the total. Our Relative Addressing formula to do this would be
That will calculate answer 13.48% correctly. However, when we copy the formula to rows 3 through 8, both the E2 and the E10 would change, resulting in wrong answers.
So, in cell G2, we want to reference cell E10 absolutely. We will refer to it as $E$10. (Remember, the dollar sign says "hold this constant," so we’re holding both the column number and the row number constant).
The F4 key is an easy way to trigger absolute referencing. Select the cell you want to copy, click your mouse in the formula bar on the part of the formula you want to change, and then press the F4 key. The F4 key will apply the dollar sign to both column and row. Continue hitting the F4 key and it will cycle through the other referencing options (absolute row, absolute column and no absolute referencing).
Now, I can copy cell G2 into cells G3 through G8.
By using the dollar sign ($) in the formula, the copied formula will correctly reference the relatively addressed cell in each row and the absolutely referenced total cell E10.