Microsoft Office Tutorials and References
In Depth Information
Copying Formulas with AutoFill
Look at the worksheet after this formula is copied to the range C14:D14
with the fill handle and cell C14 is selected (see Figure 4-8). Notice that the
Formula bar shows that this cell contains the following formula:
Because E12 was changed to $E$12 in the original formula, all the copies have
this same absolute (non-changing) reference.
If you goof up and copy a formula where one or more of the cell references
should have been absolute but you left them all relative, edit the original
formula as follows:
1. Double-click the cell with the formula or press F2 to edit it.
2. Position the insertion point somewhere on the reference you want to
convert to absolute.
3. Press F4.
4. When you finish editing, click the Enter button on the Formula bar
and then copy the formula to the messed-up cell range with the fill
Be sure to press F4 only once to change a cell reference to completely
absolute as I describe earlier. If you press the F4 function key a second time, you
end up with a so-called mixed reference, where only the row part is absolute
and the column part is relative (as in E$12). If you then press F4 again, Excel
comes up with another type of mixed reference, where the column part is
absolute and the row part is relative (as in $E12). If you go on and press F4