Using Excel Absolute References

Have you ever created a spreadsheet where you wanted a grid of formulas but had to write them one-at-a-time? Or perhaps have you created a spreadsheet where you needed a column of formulas, but you didn't want the formulas to change when you copied them to another cell. Never fear, absolute references are here.

Relative References

Normal Excel formulas use relative references. A relative reference adjusts the formula when copied. If you copy it to another row, the row is adjusted. If you copy it to another column, the column is adjusted. Here is an example:

The formula in cell A2 was copied to all of the other cells. Notice that when copied to the right, Excel adjusted the column letter to B and C respectively. When it was copied down to rows 2 and 3, the row number was adjusted accordingly. On the majority of spreadsheets, this works. On others, however, it does not. To solve that problem, you need to use Absolute References.

Absolute References

When you use an absolute reference, you lock either the column letter or the row number, or both. The copy function will then copy that part of the formula without adjustments. The vehicle to accomplish this action is the dollar sign ($). Place a dollar sign in front of the part of the formula you want to remain constant. Here is what happens if you put a dollar sign in front of the column letter (A) in the formula above:

Note that wherever you copy the formula, the A remains constant, but the row number continues to change relative to the new row. Here is what happens when you place the dollar sign in front of the row number:

Note that the row number (1) never changes, but the column letter (A) changes when copied into a new column. Finally, here is what happens when you put dollar signs in front of both the column letter and the row number:

You have locked both parts of the reference and they will never change.

When you lock either the row or the column, but not both, it is referred to as a mixed reference. When both are locked, it is called an absolute reference.

Here is a simple multiplication grid. The problem is to write a single formula at cell B2 that can be copied to all of the cells and provide the product of the cell's row multiplied by the cell's column.

We would always like to refer to column A when choosing the proper cell from the vertical and we would always like to refer to row one when choosing the proper cell from the horizontal. The solution is:

Copied to all cells, it yields these formulas:

And these result:

We will put this knowledge to good use on a loan payoff sheet in the next of this series of articles.

Written by Steven Tuttle - sdt@unh.edu