Array formulas are a crucial part of the tool kit that makes Excel versatile. However, these expressions can be daunting for beginners.

While they may appear complicated at first glance, their basics are easy to grasp. Learning arrays is handy as these formulas simplify managing complex Excel data.

What Is an Array Formula in Excel?

An array is any collection of cells in a column, row, or combination gathered together into a group.

In Microsoft Excel, the term array formula refers to a family of formulas that perform one or more operations on such a range of cells, all at once, rather than one cell at a time.

Earlier versions of Excel required users to press Ctrl + Shift + Enter to create an array function, resulting in the name CSE (Ctrl, Shift, Escape) functions, though this is no longer the case for Excel 365.

An example might be a function to get the sum of all sales over $100 on a given day. Or to determine the length (in digits) of five different numbers stored in five cells.

Array formulas can also be the perfect way to pull sample data from large datasets. They can use arguments that include a cell range from a single column, a range of cells from a single row, or cells spanning multiple rows and columns.

Also, conditional statements can be applied when pulling data into an array formula, such as only pulling numbers over or under a specific value or pulling every nth value.

This granular control allows you to ensure that you are pulling an exact subset of your data, filtering out unwanted cell values, or taking a random sample of items for testing purposes.

How to Define and Create an Array in Excel

Array formulas are, at their most basic level, easy to create. For a simple example, we can consider an invoice for a customer parts order with the following line item information: the product SKU (Stock Keeping Unit), the quantity purchased, the cumulative weight of the purchased products, and the price per unit purchased.

To complete the invoice, three columns should be added to the end of the lower table, one for the subtotal of the parts, one for the cost of shipping, and the third for the total price of each line item.

You can use a simple formula to calculate each line item independently. But if the invoice was for many line items, it could quickly get far more complicated. So instead, the "Subtotal" can be calculated with a single formula placed in cell E4:

=B4:B8 * D4:D8

Both numbers we are multiplying are cell ranges rather than single cells. In each row, the formula pulls the appropriate cell values from the arrays and places the result into the correct cell without further input from the user.

If we assume a flat shipping rate of $1.50 per pound, we can calculate our shipping column with a similar formula placed in cell F4:

=C4:C8 * B11

This time we only used an array on the left side of the multiplication operator. The results are still automatically entered, but each is multiplied against a static value this time.

Finally, we can use the same type of formula that we used for the Subtotal to get a total by adding arrays of Subtotals and Shipping Costs in cell G4:

=E4:E8 + F4:F8

Once more, a simple array formula turned what would otherwise be five formulas into one.

In the future, adding tax calculations to this spreadsheet would require only a single formula to be changed rather than changing each line item.

Managing Conditionals in Excel Arrays

The previous example uses basic arithmetic formulas across arrays to produce the results, but simple math will no longer work for more complex situations.

For example, suppose the company creating the invoice in the previous example changed to a different freight carrier. In that case, standard shipping might drop in price, but a fee could be applied for objects over a certain weight.

For the new shipping rates, standard shipping will be $1.00 per pound, and higher-weight shipping will be $1.75 per pound. High-weight shipping applies to anything over seven pounds.

Rather than going back to calculating each shipping rate line by line, an array formula can include an IF conditional statement to determine the appropriate shipping cost for each line item:

=IF(C4:C8 < 7, C4:C8*B11, C4:C8*B12)

With one quick change to a function in one cell and the addition of a new shipping rate in our rates table, we can now see that not only does the entire shipping column calculate based on weight, but the totals are also automatically picking up the new prices.

The invoice is thus future-proofed, ensuring that any future change to the cost calculation will require changing only one function.

Combining multiple or different conditionals can allow us to perform various actions. This logic can be used several ways on the same array of data. For instance,

• An extra IF function can be added to ignore shipping if the subtotal is over a certain amount.
• A tax table could be added to tax different categories of products at different rates.

No matter how complex the invoice is, editing a single cell will be all it takes to recalculate any part of the bill.

Example: Averaging Nth Numbers

In addition to making calculating and updating large swaths of data simple, array formulas can pull a slice of a dataset for testing purposes.

As the following example shows, pulling a slice of data out of a large dataset for validation purposes is easy using array formulas:

In the above example, cell D20 uses a simple function to average every nth result from sensor 1. In this case, we can define nth using the value in cell D19, giving us control over the sample size that determines the average:

=AVERAGE(IF(MOD(ROW(B2:B16)-2,D19)=0,B2:B16,""))

This allows you to quickly and easily segment a large dataset into subsets.

Mastering Array Formulas in Excel Is Essential

These simple examples are a good starting point to grasp the logic behind array formulas. Put some thought into them so you can perform multiple operations quickly today and in the future by tweaking just a few cell references and functions. Array formulas are a lightweight and efficient way to slice and dice data. Master them at the beginning of your Excel learning journey so that you can make advanced statistical calculations confidently.