355
CHAPTER
Introducing Array
Formulas
IN THIS CHAPTER
The definition of an array and
an array formulaOne-dimensional versus two-
dimensional arraysHow to work with array
constantsTechniques for working with
array formulasExamples of multicell array
formulasExamples of array formulas that
occupy a single cellO
ne of Excel’s most interesting (and most powerful) features is its
ability to work with arrays in formulas. When you understand this
concept, you’ll be able to create elegant formulas that appear to per-
form spreadsheet magic.
This chapter introduces the concept of arrays and is required reading for
anyone who wants to become a master of Excel formulas. Chapter 17 contin-
ues with lots of useful examples.
On the CD
Most of the examples in this chapter are available on the companion CD-ROM.
The filename is array examples.xlsx.Understanding Array Formulas .........................................................................................
If you do any computer programming, you’ve probably been exposed to the
concept of an array. An array is simply a collection of items operated on col-
lectively or individually. In Excel, an array can be one dimensional or two
dimensional. These dimensions correspond to rows and columns. For exam-
ple, 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 (but its VBA programming language does).
As you’ll see, 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.