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:

=C12/$E$12

Figure 4-8:

The

worksheet after

copying the

formula

with the

absolute

cell

reference.

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

handle.

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