Microsoft Office Tutorials and References

In Depth Information

**Chapter 14: Introducing Arrays**

14

Introducing Arrays

In This Chapter

The definition of an array and an array formula

●

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’ll be able to create elegant formulas that appear

to perform magic. This chapter introduces 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.

An
array
is a collection of items operated on collectively or individually. In Excel, an array can be

one-dimensional or two-dimensional. 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. An array formula can occupy multiple cells or reside 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.