Page 150 - Excel Timesaving Techniques for Dummies

28_574272 ch24.qxd 10/1/04 10:47 PM Page 135
135
When It’s Copy Time
that you shouldn’t press Shift until you’re ready 1. Position the cell pointer in the cell containing
to extend the range in the blank column or the formula to be copied (B7).
row back to the cell with the original formula.
2. Copy the formula to the Clipboard (Ctrl+C).
Making two-dimensional copies 3. Select the cell range into which to paste the
copied formula (B7:G16).
both down and across
4. Press the Enter key.
Two-dimensional formula copies that go down rows
and then across columns (or vice versa) are not That’s all there is to it. Figure 24-7 shows the same
nearly as common as one-dimensional copies down table after pressing the Enter key. Excel copies the
the rows of a single column or the columns of a sin- master formula in one operation, filling the table
gle row. However, when the chance to make a two- with the monthly mortgage payments based on vary-
dimensional copy comes along, it’s the best because ing principal amounts and annual interest rates.
you virtually create an entire table of data in a single
operation (and you can’t beat that for efficiency).
Figure 24-6 shows you a situation that calls for copy-
ing a formula in two dimensions: down rows 7
through 16 in column B and across columns C, D, E,
F, and G in all these rows. Cell B7 contains the mas-
ter formula that needs to be copied throughout this
range. This formula uses Excel’s PMT function to
determine a monthly loan payment based on a group
of principal amounts in the cell range A7:A16 and
annual interest rates in the cell range B6:G6.

• Figure 24-7: Mortgage table after copying the master
formula with the PMT function throughout.

Please note that the key to the successful copying of
the master formula throughout this table lies in the
proper blend of absolute and mixed cell references
in it. The PMT function requires three arguments:
rate (interest rate per annum), nper (number of peri-
ods), and pv (present value, also known as the prin-
cipal). For the master formula, cell B6 contains the
rate argument, B4 the nper argument, and A7 the pv
argument. For purposes of copying the formula
• Figure 24-6: Getting ready to copy the formula with the
down the rows and across the columns of the table,
PMT function to the entire data table.
the following combination of absolute and mixed cell
references has to be used:
To populate this formula throughout the table, I
follow these four simple steps: =PMT(B$6/12,$B$4*12,$A7)