Page 155 - Excel Timesaving Techniques for Dummies

29_574272 ch25.qxd 10/1/04 10:47 PM Page 140
140
Technique 25: Speeding Up Table Creation with Array Formulas
section). Therefore, don’t use array formulas in
tables that are subject to frequent additions
and deletions. Reserve them for tables whose
structures are fixed.

Editing Array Formulas

When it comes to editing array formulas, you need to
think of the Three Musketeers’ motto of “all for one,
and one for all.” Because the formula was entered as
a unit, Excel does not allow you to edit any individual
cell in that range. If you try to move, delete, insert, or
clear individual cells in a range containing an array
formula, Excel displays an alert dialog box that says
• Figure 25-4: Worksheet after copying the first array You cannot change part of an array.
formula to the cell range AI10:AI13.
If you need to make these kinds of editing changes to
6. Press Ctrl+C, select cell AJ10, and press Enter. an array range, you first need to select all its cells
(that is, all the cells containing the array formula).
Excel copies the array formula in the range Only then can you complete your editing operation.
AI10:AI13 to the array range AJ10:AJ13 (see
Figure 25-5).
To edit the contents of an array formula, you
don’t have to select the entire range that con-
tains it. Just double-click one of its cells, or
select a cell and press F2. However, in order
for you to complete the change you make to
the array formula, you must still press
Ctrl+Shift+Enter.

If you want to be able to manipulate the cells individ-
ually, you need to convert the array formula in the
cells to their individual calculated values. To do this,
follow these steps:

1. Select the array range and copy the cells into
the Clipboard (Edit➪Copy or Ctrl+C).
2. Open the Paste Special dialog box (Edit➪Paste
Special), click the Values option button, and
• Figure 25-5: Worksheet after creating the array formula click OK.
that computes the total monthly wages.
Excel not only replaces the array formula with the
calculated values (constants) but also no longer
Array formulas are great: They save time and
computer memory. The only downside is treats the cell range as an array, enabling you to edit
they’re not easy to edit (see the following its individual cells.