Microsoft Office Tutorials and References

In Depth Information

**Copying Formulas with AutoFill**

the Mother Goose Enterprises – 2013 Sales worksheet, you encounter this

situation in creating and copying a formula that calculates what percentage each

monthly total (in the cell range B14:D14) is of the quarterly total in cell E12.

Suppose that you want to enter these formulas in row 14 of the Mother Goose

Enterprises – 2013 Sales worksheet, starting in cell B14. The formula in cell

B14 for calculating the percentage of the January-sales-to-first-quarter-total is

very straightforward:

=B12/E12

This formula divides the January sales total in cell B12 by the quarterly total

in E12 (what could be easier?). Look, however, at what would happen if you

dragged the fill handle one cell to the right to copy this formula to cell C14:

=C12/F12

The adjustment of the first cell reference from B12 to C12 is just what the

doctor ordered. However, the adjustment of the second cell reference from

E12 to F12 is a disaster. Not only do you not calculate what percentage the

February sales in cell C12 are of the first quarter sales in E12, but you also

end up with one of those horrible #DIV/0! error things in cell C14.

To stop Excel from adjusting a cell reference in a formula in any copies you

make, convert the cell reference from relative to absolute. You do this by

pressing the function key F4, after you put Excel in Edit mode (F2). Excel

indicates that you make the cell reference absolute by placing dollar signs

in front of the column letter and row number. For example, in Figure 4-7, cell

B14 contains the correct formula to copy to the cell range C14:D14:

=B12/$E$12

Figure 4-7:

Copying the

formula for

computing

the ratio of

monthly to

quarterly

sales

with an

absolute

cell

reference.