Tuesday, September 20, 2005

Absolute, Relative, Mixed Formulas in Excel

Here is a quick review of absolute, relative, and mixed formulas. Normally if you enter a formula like =SUM(A2:A4403) in D1 and then copy the formula to E2, your formula in E2 will change to =SUM(B3:C4403). This is a cool feature of worksheets called "relative addressing", but sometimes we do not want that to happen. In this case, we want every formula to refer to the range A2:B4403. As we copy the formula from cell to cell, it should always point to A2:B4403. While entering the formula, hit F4 once after entering the range, and your formula will change to =SUM($A$2:$A$4403). The dollar sign indicates that that portion of the reference will not change as you copy the formula. This is called absolute addressing. It is possible to lock only the column with the $ and allow the row to be relative. This is called a mixed reference and would be entered as =$A4406. To lock the row but allow the column to be relative, use =B$4405. As you enter a formula, use F4 to toggle among the four flavors of relative, absolute and mixed references.

(from link)

No comments: