The term “array” comes from programming, but you’ll hear it come up often in the context of more advanced Excel formulas. What does it really mean? An array is a structure or container that holds a collection of items. For example, this array contains 3 items, the numbers 10, 20, and 30: {10,20,30} And this array contains three text strings: {“red”; “blue”; “green”} Note that arrays in Excel are always enclosed in curly braces. The reason arrays come up so often in Excel formulas is that arrays can be mapped directly to ranges. Arrays can be vertical, horizontal, or two-dimensional. Vertical arrays have items separated with semicolons. Horizontal arrays use commas. Two-dimensional arrays use both commas and semicolons. In this worksheet, we can represent the values in the range B6:B10 in an array like this: {“a”;“b”;“c”;“d”;“e”} Notice there are 5 cells in the range, and 5 items in the array. The range D8:F8 can be represented in an array like this: {10,20,30} the range H5:I7 corresponds to a 2D array like this: {“Bronze”,100;“Silver”,300;“Gold”,500} You can inspect arrays in a formula by using the F9 key. For example, if I start a formula with: =H5:I7 I can then use the F9 key to see the array that corresponds to this range. In fact, if I copy this array to the clipboard, then start another formula and then paste, you can see that Excel understands how to map the items in the array to individual cells. If I change a value manually, the corresponding cell updates. Arrays like this - where all values are hardcoded - are called “array constants”. Array constants are useful in many formulas, and we’ll see more examples later on in the course.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
