Relative and Absolute Cell Addresses

A relative cell address has the form ColRow (e.g., A1, Z25). An absolute cell address either has the form $ColRow (e.g., $A1, $Z25), Col$Row (e.g., A$1, Z$25), or $Col$Row (e.g., $A$1, $Z$25). The $ doesn't change the cell reference; it is just to help you build your spreadsheet model faster. That is, A1, $A1, A$1, and $A$1 all refer to the top left cell of the spreadsheet.

Excel attempts to help the user by automatically updating all cell formulas as they are moved or copied from one location to another. If the formula includes relative cell addresses, then all references are changed based on the relative distance between the source (i.e., copied from) and destination (i.e., copied to) locations.

For example, if user copies the formula AVERAGE(A1:A20) from cell C4 to cell D4 (i.e., to the right one column), then Excel will change the formula to AVERAGE(B1:B20). That is, the cell reference will change from A1:A20 to B1:B20 (i.e., to the right one column). If the user copies the same formula from C4 to C14 (i.e., down ten rows), then Excel will change it to AVERAGE(A11:A30).

Most of the time these automatic changes to a copied formula are exactly what the user wants. Sometimes, however, the user does not want to have the cell references changed. This is when absolute addresses should be used. The $ argument simply tells Excel not to change a column or row references if the formula is copied to another location. Cell references of the form $ColRow will not be changed if the cell is copied to the right or left, but will be changed if it is copied up or down. Cell references of the form Col$Row will not be changed if the cell is copied up or down, but will be changed if it is copied to the right or left. Cell references of the form $Row$Col will not be changed for any type of copying.

For example, if user copies the formula AVERAGE($A1:$A20) from cell C4 to cell D4 (i.e., to the right one column), then Excel will keep the formula as AVERAGE($A1:$A20). That is, the column labels will not change since they were preceded with a $. If the user copies the same formula from C4 to C14 (i.e., down ten rows), then Excel will change it to AVERAGE($A11:$A30) because the row numbers were not preceded with a $.

Continuing with this example, if user copies the formula AVERAGE(A$1:A$20) from cell C4 to cell D4 (i.e., to the right one column), then Excel will change the formula to AVERAGE(B$1:B$20). If the user copies the same formula from C4 to C14 (i.e., down ten rows), then Excel will leave the formula as AVERAGE(A$1:A$20).

If used properly, absolute addresses allow the user to type only one "generic" version of each formula, then simply copy it to all of the other cells that need it.
 
 

Additional Excel Information

Getting Started With Excel
Menu Bar
Button Bars
Cell Ranges
Drag Copying
Function List
Function Wizard
Chart Wizard
Excel Example

r_button.gif (896 bytes)    Home | Lectures | Handouts | Assignments |


news.gif (1977 bytes) This website was originally developed by John Mote for his MIS 311F class. This site is now maintained by the Department of Civil Engineering at the University of Memphis. Your comments and questions are welcomed.