Excel® 2007 Bible

Chapter 17. Introducing Array Formulas

<feature><title>In This Chapter</title>

• 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

</feature>

One 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 perform 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 18 continues with lots of useful examples.

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 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 (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 ...