Excel 2019 Bible

399

C H A P T E R

18

Understanding and Using Array

Formulas

IN THIS CHAPTER

Defining arrays and array formulas

Comparing one-dimensional and two-dimensional arrays

Naming array constants

Working with array formulas

Looking at examples of multicell array formulas

Looking at examples of single-cell array formulas

O

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 perform spreadsheet magic.

This chapter introduces the concept of arrays, and it is required reading for anyone who wants to

become a master of Excel formulas.

Most of the examples in this chapter are available on this book’s website at http://www.wiley.com/go/

excel2019bible. The filename is Array Formulas.xlsx.

Understanding 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 (but its VBA programming language does).

As you’ll see, arrays don’t have to be stored in cells. You can also work with arrays that exist only

in Excel’s memory. Then you can use an array formula to manipulate this information and return a

result. Excel supports two types of array formulas:

Excel® 2019 Bible, First Edition. Michael Alexander, Dick Kusleika and John Walkenbach.
© 2019 John Wiley & Sons, Inc. Published 2019 by John Wiley & Sons, Inc.