An array formula in Excel 2013 (and other spreadsheets) is a special formula that operates on a range of values. If a cell range supplies this range (as is often the case), it is referred to as an array range. If this range is supplied by a list of numerical values, they are known as an array constant.
Many spreadsheet tables use an original formula that you copy to adjacent cells by using relative cell references (sometimes referred to as a one-to-many copy). In some cases, you can build the original formula so that Excel performs the desired calculation not only in the active cell, but also in all the other cells to which you would normally copy the formula. You do this by creating an array formula.
Although the array concept may seem foreign at first, you are really quite familiar with arrays because the column-and-row structure of the Excel worksheet grid naturally organizes your data ranges into one-dimensional and two-dimensional arrays. (1-D arrays take up a single row or column, whereas 2-D arrays take up multiple rows and columns.)
The following figure illustrates a couple of two-dimensional arrays with numerical entries of two different sizes. The first array is a 3 x 2 array in the cell range B2:C4. This array is a 3 x 2 array because it occupies three rows and two columns.
The second array is a 2 x 3 array in the cell range F2:H3. This array is a 2 x 3 array because it uses two rows and three columns.
If you were to list the values in the first 3 x 2 array as an array constant in a formula, they would appear as follows:
{1,4;2,5;3,6}
Several things in this list are noteworthy. First, the array constant is enclosed in a pair of braces ({}). Second, columns within each row are separated by commas (,) and rows within the array are separated by semicolons (;). Third, the constants in the array are listed across each row and then down each column and not down each column and across each row.
The second 2 x 3 array expressed as an array constant appears as follows:
{7,8,9;10,11,12}
Note again that you list the values across each row and then down each column, separating the values in different columns with commas and the values in different rows with a semicolon.
The use of array formulas can significantly reduce the amount of formula copying that you have to do in a worksheet by producing multiple results throughout the array range in a single operation.
In addition, array formulas use less computer memory than standard formulas copied in a range. This can be important when creating a large worksheet with many tables because it may mean the difference between fitting all your calculations on one worksheet and having to split your model into several worksheet files.