Microsoft Office Tutorials and References

In Depth Information

Chapter 14: Introducing Ar-

rays

In This Chapter

• Defining arrays and array formulas

• One-dimensional versus two-dimensional arrays

• How to work with array constants

• Techniques for working with array formulas

• Examples of multicell array formulas

• Examples of array formulas that occupy a single cell

One of Excel's most interesting (and most powerful) features is its ability to work with arrays in a formula. When

you understand this concept, you can create elegant formulas that appear to perform magic. This chapter intro-

duces the concept of arrays and is required reading for anyone who wants to become a master of Excel formulas.

Chapter 15 continues with lots of useful examples.

Introducing Array Formulas

If you do any computer programming, you've probably been exposed to the concept of an
array,
which is a col-

lection of items operated on collectively or individually. In Excel, an array can be one-dimensional or two-dimen-

sional. These dimensions correspond to rows and columns. For example, a
one-dimensional array
can be stored

in a range that consists of one row (a horizontal array) or one column (a vertical array). A
two-dimensional array

can be stored in a rectangular range of cells. Excel doesn't support three-dimensional arrays (although its VBA

programming language does).

As you'll see, though, arrays need not be stored in cells. You can also work with arrays that exist only in Excel's

memory. You can then use an
array formula
to manipulate this information and return a result. Excel supports

two types of array formulas:

•
Multicell array formulas:
This type of array formula works with arrays stored in ranges or in memory, and

produces an array as a result. Because a cell can hold only one value, a multicell array formula is entered into

a range of cells.

•
Single-cell array formulas:
This type of array formula works with arrays stored in ranges or in memory, and

produces a result displayed in a single cell.

This section presents two array formula examples: an array formula that occupies multiple cells and another array

formula that occupies only one cell.