You can copy, move, and paste one cell into another using standard copy-and-paste tools such as the Windows Clipboard. When you copy formulas that contain cell addresses, Calc updates the cell references so they become
relative references . For example, suppose that you enter this formula in cell
Relative reference A cell that is referenced in relation to the current cell.
=A2 + A3
This formula contains two cell references. The references are relative because the references
A2 and
A3 change if you copy the formula elsewhere. If you copy the formula to cell
B5 , for example,
=B6 + B7
45. Copy and Move Formulas
absolute reference . The reference
$B$5 is an absolute reference. If you want to sum two columns of data (
A1 with
B1, A2 with
B2 , and so on) and then multiply each sum by some constant number, for example, the constant number can be a cell referred to as an
absolute reference . That formula might resemble this:
=(A1 + B1) * $J$1
Absolute reference A cell reference that does not change if you copy the formula elsewhere.
When entering cell references, the letters are not case-sensitive. You can type
a3 and Calc will convert it to the cell reference
A3 .
In this case,
$J$1 is an absolute reference, but
A1 and
B1 are relative. If you copy the formula down one row, the formula changes to this:
=(A2 + B2) * $J$1
Notice that the first two cells changed because when you originally entered them, they were relative cell references. You told Calc, by placing dollar signs in front of the absolute cell reference's row and column references, not to change that reference when you copy the formula elsewhere.
$B5 is a partial absolute cell reference. If you copy a formula with
$B5 inside the computation, the
$B keeps the
B column intact, but the fifth row updates to the row location of the target cell. For example, if you type the formula
=2 * $B5
in cell
A1 and then copy the formula to cell
F6 , cell
F6 holds this formula:
=2 * $B10
You copied the formula to a cell five rows and five columns over in the worksheet. Calc did not update the column name,
B , because you told Calc to keep that column name absolute. (It is always
B no matter where you copy the formula.) Calc added five to the row number, however, because the row number is relative and open to change whenever you copy the formula.
Most of the time, you'll use relative referencing. If you insert or delete rows, columns, or cells, your formulas remain accurate because the cells that they reference change as your worksheet changes.
1. | Enter the Total Formula For this sheet, assume you want a formula for each past year and projected year into the future. You would type the formula to total the first year in cell B17 . One formula that would total this year would be =B4 + B5 + B6 + B7 + B8 + B9 + B10 + B11 + B12 + B13 + B14 + B15 52 About Calc Functions explores Sum() and other Calc functions. Notice that cell A12 does not display all of the month of September's name. The column is not wide enough to display the full month. Calc warns you that this cell's contents aren't fully displayed with a small triangle along the right side of the cell. You can widen cells like this that you find are too narrow by dragging the dividing line between the name of column A and column B to the right to give every month name enough room to display properly.60 Format Cells explains how to format cells the way you want them to look. The labels for Factor and Adjusted Total are right-justified (with the Align Right button); however, they first enter their respective cells left-justified because they contain text. |
5. | Enter the First Adjusted Total To enter the correct adjusted total in cell B19 , you would type =B17 * $B$18. By using absolute addressing in cell B18 (that is, $B$18 ), when you copy it to the remaining years, all the cells you copy to will also use B18 instead of a different cell for the factor. You don't need to leave spaces between operators such as multiplication (*) in formulas. Doing so makes them easier to read and to check for errors, however. |
6. | .Copy the Adjusted Total To copy the adjusted total to the other years, you can use Ctrl+C and then paste with Ctrl+V into each year's adjusted total cell, but it's simpler just to drag the small square in the first cell's lower-right corner (the mouse pointer changes to a plus sign when you point to this square) across through the cells that are to receive the copied formula. When you release your mouse after making such a copy with one or more absolute cell addresses in the range, the absolute address remains the same and the relative addresses inside the cells change. This sounds less obvious than it is. In other words, when you copy the formula =B17 * $B$18 to cell C19 , cell C19 gets this formula: =C17 * $B$18. Cell |
You can make only the row or only the column of a cell address absolute. In the cell reference
M$15 , the column named
M is relative and will change if you copy a cell that contains this reference elsewhere, but the absolute row number,