Excel Formulas
Using 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. Those dollar signs serve only one purpose…
by
Charley Kyd, MBA Microsoft Excel MVP, 20052014
The Father of Spreadsheet Dashboard Reports

"Could someone 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."  Alan Y.

Managers... 
Charley Kyd can
personally help you to apply the Excel methods in this
article to your own organization.
Click here to learn more. 
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.
Take Your Next Steps
If you're looking for additional help with
this topic, I can help you in three ways. To learn
more, see
Excel Training, Coaching, and Consulting.
