You can copy or move formulas to use them in different cells of the same worksheet. When you move a formula or the data it refers to, your spreadsheet makes any necessary changes so that the formula still refers to the same data. However, when you copy a formula, you can make the formula refer to the original data or different data by using relative, absolute, or mixed references to cell addresses in the formula.
More power with formulas describes all the basic parts of a formula and the different types of formulas.
When you copy a formula that contains cell addresses, your spreadsheet adjusts the addresses to their new location(s) in the sheet. Addresses that adjust when you copy them are called relative references. A relative reference can be an address, such as A:B25, or a range name.
Suppose you create a formula that adds the values in column A, and you want to use the same formula to add the values in column B. To do this, copy the formula in column A and paste it into column B. As shown below, your spreadsheet automatically adjusts the cell addresses in the formula to refer to cells relative to the formula's new location.
If you do not want a cell or range address to change when you copy a formula, use an absolute reference.
Sometimes you want a formula to refer to the same cells, no matter where and how many times you copy and paste it. To ensure that the formula always refers to the same cells, you change the references to those cells to absolute references. An absolute reference always refers to the same cell or range.
To make a relative reference absolute, you add a $ (dollar sign) before the column letter, and row number. For example: $A:$B$25. To make a range name absolute, enter a $ (dollar sign) before the range name. For example: $INTEREST.
Suppose you're calculating the interest on a series of principal values, and you want to use the same interest rate each time. You can make the formula that calculates interest use an absolute reference to the cell containing the interest rate.
When you copy the formula and paste it into other cells, the formula multiplies all of the principal amounts by the same interest rate in A2. The copied formulas will still refer to the single cell that contains the interest rate because of the absolute reference.
Sometimes when you copy or move a formula that refers to a cell or range, you want part of the address to stay the same and part of the address to adjust. For example, you may want the column letter to stay the same but the row number to change. To do this, you create a mixed reference by making part of the address absolute and leaving the other part relative.
Absolute references, prceded by $ (a dollar sign) refer to the same cells no matter where you copy or move the formula. Relative references adjust relative to their new location when you copy or move the formula.
Suppose you want to calculate interest on a series of principal values, using three different interest rates. You can use the same formula to do all the calculations, as long as it contains mixed references.
In the example below, the formula in B4, +$A4*B$1, uses an absolute reference to the column that contains the various principal values, but a relative reference to the row, so that your spreadsheet looks in the same column but moves down a row each time.
The formula refers to the interest rate by using an absolute reference to the row that contains the interest rates, but a relative column reference, so that your spreadsheet looks in the same row, but moves across a column each time.