O'Reilly logo

Excel 2013 Bible by John Walkenbach

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Chapter 17: Introducing Array Formulas

In This Chapter

Defining arrays and array formulas

Comparing one-dimensional and two-dimensional arrays

Working with array constants

Working with array formulas

Looking at examples of multicell array formulas

Looking at examples of single-cell array formulas

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.

On the Web

Most of the examples in this chapter are available at this book's website. 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 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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required