Relative and Absolute Cell References

The reference of a cell is its location, given by the intersection of its column and row. For example, the cell at the intersection of column E and row 5 has the cell reference E5.

Relative Cell References

A relative cell reference is relative to the cell's position in the spreadsheet. If a formula is copied from one cell and pasted into another, the relative cell reference will change.

For example, perhaps the formula =A1+A2 is in cell A3, and you have copied cell A3. Then:

 

Absolute Cell References

An absolute cell reference always refers to a cell at a specific location, regardless of the position in the sheet. Absolute cell references can also refer to a specific column or row. When copying and pasting formulas, absolute references do NOT change.

To make an absolute cell reference, simply type a dollar sign ($) before the row or column that you wish to keep constant:

Reference

Description

A1

This is a relative cell reference. When copied and pasted, both the row and column will change accordingly.

$A1

The column A has an absolute reference. When copied and pasted, the column will not change; however, the row has a relative reference, and will change accordingly.

A$1

The row 1 has an absolute reference. When copied and pasted, the row will not change; however, the column has a relative reference, and will change accordingly.

$A$1

This is an absolute cell reference. A1 will remain constant; when copied an pasted, the row and column will not change.

 

For example, perhaps the formula =$A1+$A2 is in cell A3, and you have copied cell A3. Then:

Or, perhaps the formula =A$1+A$2 is in cell A3, and you have copied cell A3. Then:

Or, perhaps the formula =$A$1+$A$2 is in cell A3, and you have copied cell A3. Then:

 

Constant Values

Absolute references are particularly useful for entering constant values in a spreadsheet.

Your spreadsheet may calculate tax values based on a tax rate. Let this tax rate be 2.5%. Rather than enter .025 in every cell where you needed to multiply by the tax rate, you could enter .025 in, for example, cell A10. In every cell where you needed to multiply by this tax rate, you could multiply the value by $A10. An advantage of this approach is that if the tax rate were to change, it would be unnecessary to adjust all of the cells using this tax rate. Simply change the value of cell A10. Perhaps the tax rate increased to 3%. Then, change the value of cell A10 to .03. If all the formulae using this tax rate read it from A10 (i.e., use $A10), your spreadsheet will be updated with the new rate.

 

Notes on Dollar Signs

Spreadsheets are often used to calculate dollar amounts. Hence, using the dollar sign ($) in formulas can be confusing. It is important to remember that including a dollar sign in a formula has nothing to do with money. It simply tells EasySpreadsheet not to change the row or column if the cell formula is pasted elsewhere, or if the cell is moved elsewhere.

Dollar signs in front of values in cells have nothing to do with dollar signs in front of cell references in formulas.