MARISSA JONES ONLY!!!!
jermaineb1979
EX2CopyFormulaswAbsoluteCellRef7.docxCopy Formulas with Absolute Cell References
Learning Outcomes
· Create an absolute cell reference
· Use the fill handle to copy absolute cell references
When copying cells, you might want one or more cell references in a formula to remain unchanged. In such an instance, you need to apply an absolute cell reference before copying the formula to preserve the specific cell address when the formula is copied. You create an absolute reference by placing a dollar sign ($) before the column letter and row number of the address (for example, $A$1). You need to do some what-if analysis to see how various percentage increases might affect total expenses. You decide to add a column that calculates a possible increase in the total tour expenses, and then change the percentage to see various potential results.
Steps
1. 1
Click cell G1, type Change, then press [Enter]
2. 2
Type 1.1, then press [Enter]
You store the increase factor that will be used in the what-if analysis in this cell (G2). The value 1.1 can be used to calculate a 10% increase: anything you multiply by 1.1 returns an amount that is 110% of the original amount.
3. 3
Click cell H3, type What if?, then press [Enter]
4. 4
In cell H4, type =, click cell F4, type *, click cell G2, then click the Enter button on the formula bar
The result, 28690.1, appears in cell H4. This value represents the total annual expenses for Australia if there is a 10% increase. You want to perform a what-if analysis for all the tour countries.
Quick Tip
Before you copy or move a formula, always check to see if you need to use an absolute cell reference.
5. 5
Drag the fill handle from cell H4 to cell H11
The resulting values in the range H5:H11 are all zeros, which is not the result you wanted. Because you used relative cell addressing in cell H4, the copied formula adjusted so that the formula in cell H5 is =F5*G3; because there is no value in cell G3, the result is 0, an error. You need to use an absolute reference in the formula to keep the formula from adjusting itself. That way, it will always reference cell G2.
Quick Tip
When changing a cell reference to an absolute reference, make sure the reference is selected or the insertion point is next to it in the cell before pressing [F4].
6. 6
Click cell H4, press [F2] to change to Edit mode, then press [F4]
When you press [F2], the range finder outlines the arguments of the equation in blue and red. The insertion point appears next to the G2 cell reference in cell H4. When you press [F4], dollar signs are inserted in the G2 cell reference, making it an absolute reference. See Figure 2-17 .
Figure 2-17Absolute Reference Created in Formula
7. 7
Click , then drag the fill handle from cell H4 to cell H11
Because the formula correctly contains an absolute cell reference, the correct values for a 10% increase appear in cells H4:H11. You now want to see what a 20% increase in expenses looks like.
8. 8
Click cell G2, type 1.2, then click
The values in the range H4:H11 change to reflect the 20% increase. Compare your worksheet to Figure 2-18 .
Figure 2-18What-if Analysis with Modified Change Factor
9. 9
Using the Fill Handle for Sequential Text or Values
Often, you need to fill cells with sequential text: months of the year, days of the week, years, or text plus a number (Quarter 1, Quarter 2,...). For example, you might want to create a worksheet that calculates data for every month of the year. Using the fill handle, you can quickly and easily create labels for the months of the year just by typing “January” in a cell. Drag the fill handle from the cell containing “January” until you have all the monthly labels you need. You can also easily fill cells with a date sequence by dragging the fill handle on a single cell containing a date. You can fill cells with a number sequence (such as 1, 2, 3,…) by dragging the fill handle on a selection of two or more cells that contain the sequence. To create a number sequence using the value in a single cell, press and hold [Ctrl] as you drag the fill handle of the cell. As you drag the fill handle, Excel automatically extends the existing sequence into the additional cells. (The content of the last filled cell appears in the ScreenTip.) To choose from all the fill series options for the current selection, click the Fill button in the Editing group on the Home tab, then click Series to open the Series dialog box.
