Home > Excel for Business > 

Using Absolute and Relative
References in Excel Formulas

Alan,

The $ sign in a cell reference affects only one thing. It tells Excel how to treat your references when you copy the cell. If a dollar sign precedes a row number or column letter, the row or column doesn't change when you copy it. Otherwise, it does change.

To illustrate, suppose the following formulas are in cell D3 and you copy them to cell E4. Here are the results in each case:

That is, the $ sign "anchors" a row number or column letter when you copy it.

Here's an easy way to set the $ sign combination you want: Enter a reference like =B3 and then tap the F4 key. Each time you hit F4, Excel cycles to another option. To illustrate:

You also asked about a reference to an entire row. To reference one or more entire rows or columns, refer only to the row number(s) or column letter(s). Examples:

=SUM(11:11)
=SUM(13:22)
=SUM(K:K)
=SUM(M:P)

Finally, to sum discontiguous rows or columns, separate each group with a comma.

This formula sums row 11 and rows 13 through 22:

=SUM(11:11, 13:22)

This formula sums column K and columns M through P:

=SUM(K:K, M:P)

Hope this helps,

Charley Kyd
July, 2006