1.10 Adjustment to Formulas

Now, if you click on one of the lower cells in Column C, you will see that the row number in the cell addresses is not 2, but rather the number of the new cell’s row. Generally, when a formula is copied from one cell to another, the cell addresses in the formula are automatically adjusted. For example, if the formula =D3+C7 is copied to a new cell, three columns right and two rows up from the old one, the pasted formula comes out as =G1+F5. (Three columns right from D is G, two rows up from 3 is 1, and so on.)

Sometimes you will want to prevent the automatic address adjustment. To do this, put a dollar sign before any row or column number you want to keep from changing. When the formula =D$3+$C7 is copied to a new cell, three columns right and two rows up from the old one, the pasted formula comes out as =G$3+$C5. We will call an address with two $ signs in an absolute address because it always refers to the same cell ($ fix the addresses in a formula), no matter where the formula is copied/pasted. For a cell with only one $ sign in it or none, we will call a relative address because the cell referred to can change as the formula is pasted from one location to another.