Page 149 - Excel Timesaving Techniques for Dummies

28_574272 ch24.qxd 10/1/04 10:47 PM Page 134
134
Technique 24: Efficient Formula Copying
paste the formula into the entire range (this takes all
of three seconds flat). And, of course, whenever copy-
ing a formula from one worksheet to another or one
workbook to another, I always use cut and paste
because this is the only way you can do it!

Making one-dimensional
copies down or across
By far the most common formula copying that you • Figure 24-5: Copying a SUM formula down a blank
do is one dimensional, either down a single column column by using the occupied column to
or across a single row. Formulas that subtotal the the left.
columns and rows of values in a simple rectangular
table or schedule use this type of one-dimensional To see how this works, follow along with my steps:
copying.
1. Position the cell pointer in the cell that con-
To copy in one dimension, position the cell pointer tains the original formula to be copied (E4) and
in the cell with the original formula and then do then copy it to the Windows Clipboard (Ctrl+C).
either of the following:
2. Press the ← key to move the cell pointer one
column to the left (D4) and then press Ctrl+↓.
Drag the Fill handle down the column or across
the row. Excel moves the cell pointer to the last occupied
cell in this column (D8 in this example).
Copy the formula to the Clipboard and then
select the range of blank cells into which you 3. Press the → key to move the cell pointer one
want to paste it. (The pasting itself is the easy column to the right to the last cell where the
part; simply press the Enter key, and your past- formula is to be copied (E8).
ing is complete.)
4. Hold down the Shift key and press Ctrl+↑ to
extend the range up to the cell with the original
When copying a formula down lots and lots of rows
formula and then press the Enter key.
or across bunches and bunches of columns, I want
to share a trick with you that can make this entire
When you press Enter, Excel copies the formula to
procedure a whole lot easier. In order for this little
all the cells that you selected with this little sleight
trick to work, however, you need to use cut and
of hand. To apply this technique to copying a for-
paste (AutoFill is out), and the row immediately
mula across the columns of a single row, copy the
above the one with the formula or the column to the
formula to the Clipboard and then press ↑ and
immediate left must be full of data entries all the way
Ctrl+→ to reach the end of the occupied row. Then
down the rows or across the columns into which the
press ↓ and Shift+← to extend the range back to the
copies must be made.
original formula cell whereupon you press Enter.
Figure 24-5 illustrates this situation. Here, I want to
copy the SUM formula in cell E4 down to the bottom This trick may not seem like much in this
of the sales table to range E5:E8. In order to do so, I example, but when you have to copy a formula
down 400 rows instead of 4, it really pays off.
use the occupied column to the immediate left (D in
And besides, with a little practice, you can make
this example) to quickly extend the range into which
hundreds of copies in seconds. Just remember
the formula is then pasted.