HOWTO: Using Excel - How To Use Absolute Addressing

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 thte 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

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 referencce 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 =+E2/E10.

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

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.

Copyright © 2007-2008 Terry A. Stockdale. All rights reserved.