Chapter 3 describes how you can use the Copy and Paste buttons to copy information in an Office document file. So you won't be surprised to learn that you can copy formulas, too. What may surprise you, however, is that Excel is smarter than you might at first think about the way it copies formulas. Specifically, Excel edits the formulas as it copies them so the formulas still make sense in their new location.
To demonstrate this, let's say, for example, that you are creating a worksheet and that you enter the formula =B2+B3+B4 into cell B5.
Clearly, you would want to enter an equivalent formula into cells C4, D4, and E4. However, the formula in cell B6 wouldn't work for cell B13, right? The formula in cell B5 sums the values in the cells B2, B3, and B4. So, what do you do? Do you just enter another formula into cell C5? No. It turns out that you can copy the formula in cell B5 to cell C5:E5 because Excel edits the formula so it works in its new location. For example, if you copy the formula in cell B5 into cell C5, Excel places the formula =C2+C3+C4 into cell C5. If you copy the formula in cell B5 into cell D5, Excel places the formula =D2+D3+D4 into cell D5. And if you copy the formula in cell B5 into cell E5, Excel places the formula =E2+E3+E4 into cell E5. In other words, Excel correctly adjusts the copied formula as it pastes it into its new locations.
NOTE: Copying a formula works in much the same way as copying
a label or value. Just select the cell or range holding the formula or formulas
that you want to copy. Then, while holding down the Ctrl key, drag
the border of the selection to the new location. Or, if you want to copy a
formula across a continuous range, select the cell holding the formula and drag
that cell's selection handle across the range.
The reason that Excel adjusts the cell references used in the formula is because Excel considers your cell references to be relative. In other words, while you might read the formula =B2+B3+B4 as saying "Sum the values in cells B2, B3, and B4," Excel reads it a bit differently. If, the formula appears in cell B5, Excel reads the formula =B2+B3+B4 as saying "Sum the values in the three cells directly above the cell with the formula." Do you see the difference? Excel figures, then, that when you copy the formula in cell B5 to its new location, it's still supposed to sum the three cells directly above the cell with the formula.
How Excel handles this formula-editing business is really pretty simple. If you copy a formula, say, seven rows down, Excel adjusts all the row numbers in your cell references by seven rows. If you copy a formula four columns left, Excel adjusts the column letters in your row numbers by four columns.
NOTE: The fact that Excel automatically edits formulas as you
copy them is usually exactly what you want. You should know, however, that you
can stop Excel from performing this formula editing by writing your cell
references in a slightly different way. If you don't want Excel to adjust the
column letter of a cell reference as the formula is copied into different
columns, you precede the column letter with a dollar sign. For example, in the
following formula, Excel would edit as necessary the row numbers but not the
column reference: =$B2+$B3+$B4.
If you don't want Excel to adjust the row number of a cell reference as the formula is copied into different rows, precede the row number with a dollar sign. For example, in the following formula, Excel would edit as necessary the column letters but not the row numbers: =B$2+B$3+B$4.
Predictably, if you don't want Excel to adjust either the column letters or the row numbers, precede both these elements of the cell reference with dollar signs, like this: =$B$2+$B$3+$B$4. This type of cell reference is called an absolute reference.
TIP: Although you typically type the dollar signs used to
convert a relative cell reference to an absolute cell reference, you can also
use the F4 key. If you are entering or editing a cell reference, repeatedly
pressing the F4 key cycles through the different ways you can write the cell
reference. For example if you have just entered the cell reference B2 or you
were editing a formula and the insertion point rested on the cell reference B2,
pressing F4 repeatedly would rewrite the reference as $B$2, then B$2, then $B2,
and finally B2.
You can also rewrite range addresses so that they aren't edited in the usual way. For example, you could rewrite the formula =SUM(B2:B5) as =SUM($B$2:$B$5). And if you did, Excel wouldn’t adjust the range address as you copy the formula.
A practical warning is in order here, however: If you start rewriting range addresses so some portions of the range address are adjusted while other portions aren't, you'll find it very difficult to monitor and error-check the formula editing that Excel is performing. Remember that a range address uses the cell addresses of the range's opposite corners. So, if you rewrite the formula =SUM(G20:G25) as =SUM($G$20:G25), you've fixed one corner of the range but not the other. In this case, Excel won't adjust the $G$20 corner of the range address as the formula is copied, but it will adjust the G25 corner of the range address. Unfortunately, this partial adjustment of the formula gets very confusing. If you copy your formula down rows or right across columns, for example, your range address actually grows larger and larger. But if you copy your formula up rows or left across columns, your range address first shrinks in size and then begins to grow. If you rewrite the formula =SUM($G20:G25), so Excel won't adjust the column of the first corner but will adjust the row, the formula adjustment gets even more confusing to monitor.
NOTE: It's important to note that Excel has no problem correctly adjusting the copied formulas. The problem is just that this sort of adjustment is difficult for you to monitor and error-check. For this reason, you may want to avoid constructing formulas that use partially fixed, or mixed-range, references. You'll keep your worksheets much simpler and easier to error-check by doing so