# How to Use Absolute and Relative Cell References in Excel Formulas

Excel's absolute cell references, like \$A\$1, confuse many Excel users. But the explanation is easy. Each dollar signs serv only one important purpose.

20910 A reader sent me this question:

Could you send me more details about using \$’s like the following: =AVERAGE(\$6:\$6) averages all data in ROW 6. I’d like more details of usages, examples, as this is a great feature that I cannot find information on.

As I told my reader, 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:

 =B3 copies as: =C4 =\$B\$3 copies as: =\$B\$3 =B\$3 copies as: =C\$3 =\$B3 copies as: =\$B4

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: Type a reference like =B3 in your formula bar and then tap the F4 key. Each time you hit F4, Excel cycles to another option. To illustrate:

 =B3 tap {F4} to get: =\$B\$3 tap {F4} to get: =B\$3 tap {F4} to get: =\$B3 tap {F4} to get: =B3 (etc)

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)

(Keep in mind that these are relative addresses. So add \$ signs if you want them to be absolute references.)

Finally, to sum discontiguous rows or columns, that is, separate areas, differentiate each group with a comma.

To illustrate, this formula sums row 11 and rows 13 through 22:

=SUM(11:11, 13:22)

And this formula sums column K and columns M through P:

=SUM(K:K, M:P)

Hope this helps. Early in my career, I worked nearly 20 years as the CFO of turnarounds and startups. But I eventually got burned out fighting continual struggles with cash flow. That's when I started to write about Lotus 1-2-3, the spreadsheet software that I'd been using most of the time during the CFO days. When Excel was about to be introduced for the PC, one of my magazine editors set up a meeting for me to see the product, talk with the developers, and write a cover story about Excel. So I used the first version of Excel before it was launched. And I had also used the first version of VisiCalc before it was launched. And then,