Page 335 - Excel 2013 All-in-One For Dummies

the cell reference, as in R8C11, without placing the row and column numbers in square brackets.
If you realize that you need to convert a relative cell reference to an absolute reference as you’re building the original formula, you can convert the rela- tive reference to absolute by selecting the cell and then pressing F4. To get an idea of how this works, follow along with these steps for creating the cor- rect formula =B7/$K$8 in cell B10:
1. Click cell B10 to make it active.
2. Type = to start the formula; then click cell B8 and type / (the sign for
division).
The Formula bar now reads =B8/.
3. Click K8 to select this cell and add it to the formula. The Formula bar now reads =B8/K8.
4. Press F4 once to change the cell reference from relative (K8) to abso- lute ($K$8).
The Formula bar now reads =B8/$K$8. You’re now ready to enter the for- mula and then make the copies.
5. Click the Enter button on the Formula bar and then drag the Fill handle to cell J10 before you release the mouse button.
Like it or not, you won’t always anticipate the need for an absolute value until after you’ve built the formula and copied it to a range. When this hap- pens, you have to edit the original formula, change the relative reference to absolute, and then make the copies again.
When editing the cell reference in the formula, you can change its reference by positioning the insertion point anywhere in its address and then press- ing F4. You can also do this by inserting dollar signs in front of the column letter(s) and row number when editing the formula, although doing that isn’t nearly as easy as pressing F4.
You can make an exact copy of a formula in another cell without using abso- lute references. To do this, make the cell with the formula that you want to copy the active one, use the I-beam pointer to select the entire formula in the Formula bar by dragging through it, and then click the Copy command button on the Home tab of the Ribbon (or press Ctrl+C). Next, click the Cancel button to deactivate the Formula bar, select the cell where you want the exact copy to appear, and then click the Paste command button on the Home tab (or press Ctrl+V). Excel then pastes an exact duplicate of the origi- nal formula into the active cell without adjusting any of its cell references (even if they are all relative cell references).
Copying Formulas 317
Book III Chapter 1
Building Basic Formulas