Microsoft Office Tutorials and References
In Depth Information
2. Select the range C22:M24 , and then click the Paste button on the Standard toolbar.
Figure 2-5 shows the total expenses and net income values for each month in the Budget
worksheet. Note that Excel has duplicated the two formulas from the first month in each
Copying and pasting a cell range
net income values
3. Press the Esc key to remove the moving border from the selected range.
As you can see, Excel’s ability to adjust cell references when copying and pasting for-
mulas makes it easy to create columns or rows of formulas that share a common structure.
Using Relative and Absolute References
The type of cell reference that you just worked with is called a relative reference. A relative
reference is a cell reference that changes when it is copied and pasted in a new location.
Excel interprets the reference relative to the position of the active cell. For example, when
you copied the formula =SUM(B6:B7) from the source cell, B8, and pasted it in the desti-
nation range, C8:M8, Excel adjusted the cell references in each pasted formula relative to
the new location of the formula itself. The formula in cell C8 became =SUM(C6:C7) , the
formula in cell D8 became =SUM(D6:D7), and so on.
A second type of cell reference is an absolute reference. An absolute reference is a cell
reference that doesn’t change when it is copied. Excel does not adjust the cell reference
because the cell reference points to a fixed, or absolute, location in the worksheet, and it
remains fixed when the copied formula is pasted. In Excel, an absolute reference appears
with a dollar sign ($) before each column and row designation. For example, $B$8 is an
absolute reference, and when it is used in a formula, Excel will always point to the cell
located at the intersection of column B and row 8.
Figure 2-6 provides an example in which an absolute reference is necessary to a for-
mula. In this example, a sales worksheet records the units sold for each region as well as
the overall total. If you want to calculate the percent of units sold for each region, you
divide the units sold for each region by the overall total. If you use only relative references,
copying the formula from the first region to the second will produce an incorrect result,
because Excel shifts the location of the total sales cell down one row. To correct this prob-
lem, you use an absolute cell reference, fixing the location of the total sales cell at cell B8.
In the example, this means changing the formula in cell C4 from =B4/B8 to =B4/$B$8 .