MARISSA JONES ONLY!!!!
Copy Formulas with Relative Cell References
Learning Outcomes
· Copy and Paste formulas with relative cell references
· Examine Auto Fill and Paste Options
· Use the Fill button
Copying and moving a cell allow you to reuse a formula you’ve already created. Copying cells is usually faster than retyping the formulas in them and helps to prevent typing errors. If the cells you are copying contain relative cell references and you want to maintain the relative referencing, you don’t need to make any changes to the cells before copying them. You want to copy the formula in cell B21, which calculates the 30% increase in quarterly expenses for Quarter 1, to cells C21 through E21. You also want to create formulas to calculate total expenses for each tour country.
Steps
1. 1
Click cell B21 if necessary, then click the Copy button in the Clipboard group on the Home tab
The formula for calculating the 30% expense increase during Quarter 1 is copied to the Clipboard. Notice that the formula =B12*1.3 appears in the formula bar, and a moving border surrounds the active cell.
Quick Tip
To paste only specific components of a copied cell or range, click the Paste list arrow in the Clipboard group, then click Paste Special. You can selectively copy formats, formulas, values, comments, and validation rules; transpose columns and rows; paste a link; or add, subtract, multiply, or divide using the Paste Special dialog box.
2. 2
Click cell C21, then click the Paste button (not the list arrow) in the Clipboard group
The formula from cell B21 is copied into cell C21, where the new result of 45850.688 appears. Notice in the formula bar that the cell references have changed so that cell C12 is referenced instead of B12. This formula contains a relative cell reference, which tells Excel to substitute new cell references within the copied formulas as necessary. This maintains the same relationship between the new cell containing the formula and the cell references within the formula. In this case, Excel adjusted the formula so that cell C12—the cell reference nine rows above C21—replaced cell B12, the cell reference nine rows above B21.
3. 3
Drag the fill handle from cell C21 to cell E21
A formula similar to the one in cell C21 now appears in cells D21 and E21. After you use the fill handle to copy cell contents, the Auto Fill Options button appears, as shown in Figure 2-14 . You can use the Auto Fill Options button to fill the cells with only specific elements of the copied cell if you wish.
Figure 2-14Formula Copied Using the Fill Handle
4. 4
Click cell F4, click the AutoSum button in the Editing group, then click the Enter button on the formula bar
5. 5
Click in the Clipboard group, select the range F5:F6, then click
See Figure 2-15 . After you click the Paste button, the Paste Options button appears.
Figure 2-15Formulas Pasted in the Range F5:F6
6. 6
Click the Paste Options button adjacent to the selected range
You can use the Paste options list to paste only specific elements of the copied selection if you wish. The formula for calculating total expenses for tours in Britain appears in the formula bar. You would like totals to appear in cells F7:F11. The Fill button in the Editing group can be used to copy the formula into the remaining cells.
7. 7
Press [Esc] to close the Paste Options list, then select the range F6:F11
8. 8
Click the Fill button in the Editing group, then click Down
The formulas containing relative references are copied to each cell. Compare your worksheet to Figure 2-16 .
Figure 2-16Formula Copied Using Fill Down
9. 9
Using Paste Preview
You can selectively copy formulas, values, or other choices using the Paste list arrow, and you can see how the pasted contents will look using the Paste Preview feature. When you click the Paste list arrow, a gallery of paste option icons opens. When you point to an icon, a preview of how the content will be pasted using that option is shown in the worksheet. Options include pasting values only, pasting values with number formatting, pasting formulas only, pasting formatting only, pasting transposed data so that column data appears in rows and row data appears in columns, and pasting with no borders (to remove any borders around pasted cells).