Page 152 - Excel Timesaving Techniques for Dummies

29_574272 ch25.qxd 10/1/04 10:47 PM Page 137
25 Speeding Up Table
Creation with Array

Formulas
Technique

n Technique 24, I make a big deal about making efficient copies of
Save Time By your spreadsheet formulas. In this technique, I make a big deal about
Iusing array formulas in tables of data so that you don’t have to bother
Using array formulas to
populate a table with making a single formula copy. Instead, you input all the formulas
you need in a particular cell range in a single operation. Not only does
Knowing how to properly this significantly reduce the amount of formula copying you have to do
edit an array in a worksheet, but you also get the added benefit of saving computer
memory — array formulas take less memory to store than copies of a
single formula. (This savings can be significant when you’re dealing with
huge spreadsheets.)

The key to array formulas is the array, a concept that isn’t quite as for-
eign as it may first sound. In Excel, with its row and column grid, you
work with arrays all the time. In fact, you could even go as far as to say
that the Excel worksheet itself is nothing more than a humongous 65536
(row) x 256 (column) array!

A Quick Look at Array Ranges

Array formulas that you build in a spreadsheet use one of two types of
array ranges:
One-dimensional array range that occupies a single row or column
Two-dimensional array range that occupies more than one row and
column

Figure 25-1 illustrates these two types of ranges with four sample array
ranges. Each sample range contains a list of simple numerical values
known as array constants.