Making sense of dollar signs in Excel

 

http://blogs.office.com/2011/08/17/making-sense-of-dollar-signs-in-excel/

 

Dollar signs indicating absolute references

You probably know that a formula can refer to cells. That’s one reason Excel formulas are so powerful — the results can change based on changes made in other cells. When a formula refers to a cell, it uses a cell reference. In the “A1″ reference style (the default), there are three kinds of cell references: absolute, relative, and mixed.

 

Absolute cell references

When a formula contains an absolute reference, no matter which cell the formula occupies the cell reference does not change: if you copy or move the formula, it refers to the same cell as it did in its original location. In an absolute reference, each part of the reference (the letter that refers to the row and the number that refers to the column) is preceded by a “$” – for example, $A$1 is an absolute reference to cell A1. Wherever the formula is copied or moved, it always refers to cell A1.

 

Relative cell references

In contrast, a relative reference changes if the formula is copied or moved to a different cell (i.e., a cell other than where the formula was originally entered). The row and column portions of a relative reference are not preceded by a “$” –  for example, A1 is a relative reference to cell A1. If moved or copied, the reference changes by the same number of rows and coulmns as it was moved. So, if you move a formula with the relative reference A1 one cell down and one cell to the right, the reference changes to B2.

 

Mixed cell references

A mixed reference uses a dollar sign either in front of the row letter or in front of the column number, but not both – for example, A$1 is a mixed reference in which the row adjusts, but the column does not. So if you move a formula containing that reference one cell down and one cell to the right, it becomes B$1.

 

Which kind of cell reference should you use?

The kind of cell reference you use depends on what you are doing, but usually you want to use relative references. Excel uses relative references by default, which makes it easy to fill formulas down and across: the references automatically update, which is what you want, most of the time.

One case where you might want to use an absolute reference is when using the VLOOKUP function. Though the value you’re wanting to look up might change (for example, as you fill the VLOOKUP formula down a column), the actual location of the lookup table doesn’t change or adjust as you fill the column down.

 

But now that you know the differences between these kinds of cell references, you can make the decision for yourself, based on how you want your formulas to behave. Do you have some use-cases to share with the community? Share them by leaving a comment!