Excel array formulas, functions and constants - examples and guidelines

Simple example of Excel array formula

Suppose you have some items in column B, their prices in column C, and you want to calculate the grand total of all sales.

Of course, nothing prevents you from calculating subtotals in each row first with something as simple as =B2*C2 and then sum those values:

However, an array formula can spare you those extra key strokes since it gets Excel to store intermediate results in memory rather than in an additional column. So, all it takes is a single array formula and 2 quick steps:

1. Select an empty cell and enter the following formula in it:

   =SUM(B2:B6*C2:C6)

2. Press the keyboard shortcut CTRL + SHIFT + ENTER to complete the array formula.

   Once you do this, Microsoft Excel surrounds the formula with {curly braces}, which is a visual indication of an array formula.

   What the formula does is multiply the values in each individual row of the specified array (cells B2 through C6), add the sub-totals together, and output the grand total:
   

This simple example shows how powerful an array formula can be. When working with hundreds and thousands of rows of data, just think how much time you can save by entering one array formula in a single cell.

Why use array formulas in Excel?

Excel array formulas are the handiest tool to perform sophisticated calculations and do complex tasks. A single array formula can replace literally hundreds of usual formulas. Array formulas are very good for tasks such as:

• Sum numbers that meet certain conditions, for example sum N largest or smallest values in a range.
• Sum every other row, or every N^th row or column, as demonstrated in this example.
• Count the number of all or certain characters in a specified range. Here is an array formula that counts all chars, and another one that counts any given characters.

Example 1. A single-cell array formula

Suppose you have two columns listing the number of items sold in 2 different months, say columns B and C, and you want to find the maximum sales increase.

Normally, you would add an additional column, say column D, that calculates the sales change for each product using a formula like =C2-B2, and then find the maximum value in that additional column =MAX(D:D).

An array formula does not need an additional column since it perfectly stores intermediate results in memory. So, you just enter the following formula and press Ctrl + Shift + Enter:

=MAX(C2:C6-B2:B6)

Example 2. A multi-cell array formula in Excel

In the previous SUM example, suppose you have to pay 10% tax from each sale and you want to calculate the tax amount for each product with one formula.

Select the range of empty cells, say D2:D6, and enter the following formula in the formula bar:

=B2:B6 * C2:C6 * 0.1

Once you press Ctrl + Shift + Enter, Excel will place an instance of your array formula in each cell of the selected range, and you will get the following result:

Example 3. Using an Excel array function to return a multi-cell array

As already mentioned, Microsoft Excel provides a few so called "array functions" that are specially designed to work with multi-cell arrays. TRANSPOSE is one of such functions and we are going to utilize it to transpose the above table, i.e. convert rows to columns.

1. Select an empty range of cells where you want to output the transposed table. Since we are converting rows to columns, be sure to select the same number of rows and columns as your source table has columns and rows, respectively. In this example, we are selecting 6 columns and 4 rows.
2. Press F2 to enter the edit mode.
3. Enter the formula and press Ctrl + Shift + Enter.

In our example, the formula is:

=TRANSPOSE($A$1:$D$6)

The result is going to look similar to this:

This is how you use TRANSPOSE as a CSE array formula in Excel 2019 and earlier. In Dynamic Array Excel, this also works as a regular formula. To learn other ways to transpose in Excel, please check out this tutorial: How to switch columns and rows in Excel.

How to work with multi-cell array formulas

When working with multi-cell array formulas in Excel, be sure to follow these rules to get the correct results:

1. Select the range of cells where you want to output the results before entering the formula.
2. To delete a multi-cell array formula, either select all the cells containing it and press DELETE, or select the entire formula in the formula bar, press DELETE, and then press Ctrl + Shift + Enter.
3. You cannot edit or move the contents of an individual cell in an array formula, nor can you insert new cells into or delete existing cells from a multi-cell array formula. Whenever you try doing this, Microsoft Excel will throw the warning "You cannot change part of an array".
4. To shrink an array formula, i.e. to apply it to fewer cells, you need to delete the existing formula first and then enter a new one.
5. To expand an array formula, i.e. apply it to more cells, select all cells containing the current formula plus empty cells where you want to have it, press F2 to switch to the edit mode, adjust the references in the formula and press Ctrl + Shift + Enter to update it.
6. You cannot use multi-cell array formulas in Excel tables.
7. You should enter a multi-cell array formula in a range of cells of the same size as the resulting array returned by the formula. If your Excel array formula produces an array larger than the selected range, the excess values won't appear on the worksheet. If an array returned by the formula is smaller than the selected range, #N/A errors will appear in extra cells.

If your formula may return an array with a variable number of elements, enter it in a range equal to or larger than the maximum array returned by the formula and wrap your formula in the IFERROR function, as demonstrated in this example.

1. Horizontal array constant

A horizontal array constant resides in a row. To create a row array constant, type the values separated by commas and enclose then in braces, for example {1,2,3,4}.

To enter a horizontal array in a spreadsheet, select the corresponding number of blank cells in a row, type the formula ={1,2,3,4} in the formula bar, and press Ctrl + Shift + Enter. The result will be similar to this:

As you see in the screenshot, Excel wraps an array constant in another set of braces, exactly like it does when you are entering an array formula.

2. Vertical array constant

A vertical array constant resides in a column. You create it in the same way as a horizontal array with the only difference that you delimit the items with semicolons, for example:

={11; 22; 33; 44}

3. Two-dimensional array constant

To create a two-dimensional array, you separate each row by a semicolon and each column of data by a comma.

={"a", "b", "c"; 1, 2, 3}

Working with Excel array constants

Array constants are one of the cornerstones of an Excel array formula. The following information and tips might help you use them in the most efficient way.

1. Elements of an array constant

   An array constant can contain numbers, text values, Booleans (TRUE and FALSE) and error values, separated by commas or semicolons.

   You can enter a numerical value as an integer, decimal, or in scientific notation. If you use text values, they should be surrounded in double quotes (") like in any Excel formula.

   An array constant cannot include other arrays, cell references, ranges, dates, defined names, formulas, or functions.

2. Naming array constants

   To make an array constant easier to use, give it a name:

   • Switch to the Formulas tab > Defined Names group and click Define Name. Alternatively, press Ctrl + F3 and click New.
   • Type the name in the Name
   • In the Refers to box, enter the items of your array constant surrounded in braces with the preceding equality sign (=). For example:

     ={"Su", "Mo", "Tu", "We", "Th", "Fr", "Sa"}
     

   • Click OK to save your named array and close the window.

   To enter the named array constant in a sheet, select as many cells in a row or column as there are items in your array, type the array's name in the formula bar preceded with the = sign and press Ctrl + Shift + Enter.

   The result should resemble this:
   

3. Preventing errors

   If your array constant does not work correctly, check for the following problems:

   • Delimit the elements of your array constant with the proper character - comma in horizontal array constants and semicolon in vertical ones.
   • Selected a range of cells that exactly matches the number of items in your array constant. If you select more cells, each extra cell will have the #N/A error. If you select fewer cells, only a part of the array will be inserted.

Using array constants in Excel formulas

Now that you are familiar with the concept of array constants, let's see how you can use arrays informulas to solve your practical tasks.

Array formula with the AND operator

In this example, we find the sum of sales where the sales person is Mike AND the product is Apples:

=SUM((A2:A9="Mike") * (B2:B9="Apples") * (C2:C9))

Or

=SUM(IF(((A2:A9="Mike") * (B2:B9="Apples")), (C2:C9)))

Technically, this formula multiplies the elements of the three arrays in the same positions. The first two arrays are represented by TRUE and FALSE values which are the results of comparing A2:A9 to Mike" and B2:B9 to "Apples". The third array contains the sales numbers from the range C2:C9. Like any math operation, multiplication converts TRUE and FALSE to 1 and 0, respectively. And because multiplying by 0 always gives zero, the resulting array has 0 when either or both conditions are not met. If both conditions are met, the corresponding element from the third array gets into the final array (e.g. 1*1*C2 = 10). So, the result of multiplication is this array: {10;0;0;30;0;0;0;0}. Finally, the SUM function adds up the array's elements and return a result of 40.

Excel array formula with the OR operator

The following array formula with the OR operator (+) adds up all sales where the sales person is Mike OR product is Apples:

=SUM(IF(((A2:A9="Mike") + (B2:B9="Apples")), (C2:C9)))

In this formula, you add up the elements of the first two arrays (which are the conditions you want to test), and get TRUE (>0) if at least one condition evaluates to TRUE; FALSE (0) when all the conditions evaluates to FALSE. Then, IF checks if the result of addition is greater than 0, and if it is, SUM adds up a corresponding element of the third array (C2:C9).