Le Moyne College CSC-151 Microsoft Excel 2003 Reference

Types of cell references

When you are entering or modifying a formula, you may use the <F4> key to toggle a cell reference between relative, absolute, and mixed. Be sure that the cursor is in the cell reference whose type you want to change before stiking the <F4> key.

Type	Samples	Description
relative cell reference	=A10 =SUM(C10:C20)	Cell reference changes when a formula is copied. The change made to a relative cell reference during a copy operation depends on the direction and distance of the copy. Let's assume that cell D4 contains the formula =D1+D2+D3 If we copy the D4 formula to E4, the direction is to the right (considered a positive direction) and the distance is 1 column. Thus a 1 is added to each column identifier in the formula being copied. The result is that E4 contains the formula =E1+E2+E3 If we copy the D4 formula to C4, the direction is to the left (considered a negative direction) and the distance is 1 column. Thus a 1 is subtracted from each column identifier in the formula being copied. The result is that C4 contains the formula =C1+C2+C3 Let's assume that cell E10 contains the formula =SUM(A10:D10) If we copy the E10 formula to E12, the direction is down (considered a positive direction) and the distance is 2 rows. Thus a 2 is added to each row identifier in the formula being copied. The result is that E12 contains the formula =SUM(A12:D12) If we copy the E10 formula to E8, the direction up (considered a negative direction) and the distance is 2 rows. Thus a 2 is subtracted from each row identifier in the formula being copied. The result is that E8 contains the formula =SUM(A8:D8)
absolute cell reference	=$A$10 =SUM($C$10:$C$20)	Cell reference is the same when a formula is copied. Note that a formula may contain many kinds of cell references. So for example, let's assume that cell D4 contains the formula =D1+D2+$D$3 If we copy the D4 formula to E4, the result is that E4 contains the formula =E1+E2+$D$3 If we copy the D4 formula to C4, the result is that C4 contains the formula =C1+C2+$D$3
mixed cell reference	=$A10 =A$10	Combines the properties associated with relative and absolute cell references. When a dollar sign precedes the column identifier but not the row identifier, copying the formula results in no change to the column identifier. When a dollar sign precedes the row identifier but not the column identifier, copying the formula results in no change to the row identifier.
worksheet cell reference	='Sheet1'!B10 ='Sheet1'!$B$10	Reference a cell or cells on another worksheet. Note that this type of cell reference may also be a relative, aboslute, or mixed cell refernece. When you copy a worksheet cell reference to another cell, the worksheet name will not change. The column and/or row identifiers may change depending on whether the reference is relative, absolute, or mixed.
3-D cell reference	=SUM('Sheet1:Sheet3'!C3) =SUM('Sheet1:Sheet3'!D1:D10)	Reference a cell or cells on multiple worksheets. Note that this type of cell reference may also be a relative, aboslute, or mixed cell refernece. When you copy a 3-D cell reference to another cell, the worksheet names will not change. The column and/or row identifiers may change depending on whether the reference is relative, absolute, or mixed.
external cell reference	='Path\[FileName.xls]SheetName'!$D$7	Reference a cell or cells in another workbook (i.e., spreadsheet file). Note that, by default, this type of cell reference generates an abosolute cell reference.