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

318 Copying Formulas
Keep in mind when using the sum options on the Totals tab of the Quick Analysis tool’s palette (see “Totals and sums with the Quick Analysis tool” earlier in this chapter for details) that all the cell references in the total and sum formulas that Excel creates are relative references.
If you’re building the formula that requires an absolute or some sort of mixed cell reference (see the following section) on a touchscreen device and using the Touch keyboard with no access to function keys, you need to add the required dollar sign(s) into the formula on the Formula bar by using the dollar sign ($) key on the Touch keyboard. To access the dollar sign key, tap the Numeric key (&123) to switch the Touch keyboard out of the QWERTY letter arrangement.
A mixed bag of references
Some formulas don’t require you to change the entire cell reference from relative to absolute in order to copy them correctly. In some situations,
you need to indicate only that the column letter or the row number remains unchanged in all copies of the original formula. A cell reference that is part relative and part absolute is called a mixed cell reference.
In the A1 notation, a mixed cell reference has a dollar sign just in front of the column letter or row number that should not be adjusted in the copies. For example, $C10 adjusts row 10 in copies down the rows but leaves column
C unchanged in all copies across columns to its right. Another example is C$10, which adjusts column C in copies to columns to the right but leaves row 10 unchanged in all copies down the rows. (For an example of using mixed cell references in a master formula, refer to the information on using the PMT Function in Book III, Chapter 4.)
To change the cell reference that you select in a formula (by clicking the flashing insertion point somewhere in its column letter and row number) from relative to mixed, continue to press F4 until the type of mixed reference appears on the Formula bar. When the Formula bar is active and the insertion point is somewhere in the cell reference (either when building or editing the formula), pressing F4 cycles through each cell-reference possibility in the following order:
✦ The first time you press F4, Excel changes the relative cell reference to absolute (C10 to $C$10).
✦ The second time you press F4, Excel changes the absolute reference to a mixed reference where the column is relative and the row is absolute ($C$10 to C$10).
✦ The third time you select the Reference command, Excel changes the mixed reference where the column is relative and the row is absolute to a mixed reference where the row is relative and the column is absolute (C$10 to $C10).