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.
Search JabSto ::

Custom Search