Excel Array formulas are one of the most powerful yet confusing functions of Excel.

That is one big reason why you’d often find Excel users resorting to time-worn, lengthy methods for performing even the simplest of Excel operations.

Mastering Excel array formulas can help you save big on your time and energy.

38% of all Office Workers’ time is spent using Excel – optimising this time is important in today’s world! For more Excel stats go here!

They are smartly designed and are very fruitful when it comes to complex operations.

Continue reading the article that follows to learn all about single-cell and multi-cell array formulas in Excel.

Excel course offerings by Acuity Training can be accessed here.

 

What is an array in Excel?

In literal terms, an array is defined as the ‘arrangement of a group of items’. Specifically in Excel, an array is a collection of items populated together as a column or a row.

We all have created tons of arrays in Excel by populating individual cells. However, an array can also be created through a multi-cell array formula and you’ll be amazed to know how.

For instance, we want to create an array in the shape of a row that consists of four names; Tim, Brandon, Jonathon, and Taylor.

To do so, select the four cells where you want these names populated and write up a formula like the one below.

{“ Tim”, “Brandon”, “Jonathon”, “Taylor”}

Now! Wait. Don’t simply press enter. But bring three of your fingers to action to press Ctrl + Shift + Enter together. Take a look at the results.

 

Excel creates a row

 

There it is! You have just created an array in Excel. This is a horizontal array constant as it spreads across the columns. You may create a similar vertical array constant by selecting 4 rows instead of 4 columns before the formula is punched in.

Attention!

The formula to be typed must be enclosed in curly brackets and not simple round brackets. Also, note how upon pressing Ctrl + Shift + Enter, Excel adds another pair of curly brackets to the formula.

The Excel array tools also enable the creation of Random arrays through the RANDARRAY function.

 

What is an array formula in Excel?

Simply put, an array formula in Excel is the one that performs multiple calculations in one go. It evaluates individual operations within the formula and processes several values together to bring about the final result.

Not only that, but multiple-cell array formulas can also produce several values at a time. That means array formulas can not only process several values but can also produce a range of values.

Within Excel, array formulas are created through the concurrent operation of the following three keys:

Ctrl + Shift + Enter

Excel denotes an array formula through flower or curly brackets added to the beginning and end of the formula. These brackets are added as soon as you press Ctrl + Shift + Enter and can be seen through the formula bar (press F2).

Learn more about other Excel array tools such as the FILTER function here.

 

Did you know?

Array formulas that work under the operation of Ctrl + Shift + Enter are also known as CSE formulas (abbreviated for Ctrl + Shift + Enter).

 

Points to Note:

For array formulas, there are a few points that you must note:

    • These curly brackets only come through the concurrent operation of Ctrl + Shift + Enter. Manually adding them will not work. In fact, doing so would render the formula invalid as Excel would treat it as a text value.
    • Editing the formula would turn it back into a regular formula unless you again hit Ctrl + Shift + Enter after you’re done editing

 

When should you use Array Formulas in Excel?

Array Formulas in Excel are the most useful when you have lengthy datasets to your plate. For example, take a look at the image below.

 

Quantity and sale price of different products for different customer orders

 

The above image represents the data of different customer orders with the quantity and sale price for products ordered by each customer. How do you calculate the sales for March 202X?

Simple math, multiply the quantity and sales price for each product. Continue this step for each product ordered by each customer. Add up these sub-totals to reach the final value for each customer order. Then add the total of each customer order.

Not to undercount, these are no less than 4 steps to be performed. Also, the complexity keeps on adding as the number of products increases or as the data grows in size.

The old basic way of finding the total sales for March 202X is as follows.

 

Adding a column of sub-totals for each customer order

 

In the above image, we have included a new column for sub-totals where we have first found the total bill for each customer order. And using the ‘Drag and Fill’ button, the same is calculated for other cells in the column.

 

Adding up the sub-totals for each customer order

 

Next, we have again constructed a new SUM formula for computing the total of all the sales made to each customer.

The above lengthy process can be simplified through one single array formula. Compose the formula as given below.

=SUM(B4:B10*C4:C10)*(D4:D10*E4:E10)

  • (B4:B10*C4:C10) is used to compute the total sales of Product A.
  • (D4:D10*E4:E10) is used to compute the total sales of Product B.
  • The SUM function is used to add up both the above sales.

After the above formula is composed, don’t just hit enter, but use:

Ctrl + Shift + Enter

This converts the above formula into a single array formula. And here are the results.

 

Excel computes the total sales for March 202X

 

Viola! No additional sub-total columns are required. It takes only a single formula for Excel to compute the total sales for March 202X in one go.

Pro Tip: Note how excel has added curly brackets to the start and end of the formula to denote a single array formula.

 

Power Points of the Array Formula:

    • The modern-day array formula saves users’ time as you can cut down on unnecessary keystrokes for sub-totals.
    • Array Formulas come up as the handiest tool to replace tens of formulas with a single formula.
    • With single array formulas, Excel stores the intermediate results in its memory and so, you don’t need additional columns that make your data clumsy.

Another powerful tool from the Excel Array tools Library is the UNIQUE function. Learn about it here.

 

How to enter an Array Formula in Excel – Simple Example

Entering an array formula in Excel is all about three concurrent keystrokes.

Ctrl + Shift + Enter

Let’s put that together through an example.

The image below represents the sale details for different products. These details include the quantity, sale price, and discount offered per product. We want to find the net sales (net of discount) for the month.

 

Sale, quantity, and discount data for different products

 

Can this be done in one go without us having to introduce multiple new columns for sub-totals and discount values? The array formula can help. With array formulas, all the process is encapsulated within a single step by composing the right formula and feeding it through the three magic keys mentioned above.

Simply activate the cell against the total sales and set up the formula as follows.

=SUM(B3:B9*C3:C9*(100%-D3:D9))

 

Breakup of the formula:

    • The cell range B3:B9 consists of the quantity of each product ordered during the period under calculation.
    • This range is multiplied by the cell range C3:C9 which consists of the sale price of each product.
    • Another multiplication factor is added in the parenthesis for the computation of the discount. To find net sales, we must reduce the sales of each product (quantity * sale price) by the discount per product.
    • The cell range G3:G9 consists of the discount rate per product. (100%-D3:D9) is to reduce the discount from the sale.

The formula is all constructed. However, do not hit only ‘Enter’ but ‘Ctrl + Shift + Enter’.

Doing so, here are the results shown in Excel.

 

Excel computes the total net sales

 

Do not forget to take note of the curly brackets added by Excel to the opening and closing of the formula – this indicates that the enclosed formula is a single array formula and not a regular formula.

 

Multi-Cell Array Formulas

You must have enjoyed learning about the single-cell array formula in Excel. But what comes next is going to take you by surprise.

 

What is a Multi-cell array formula and how does it work?

A multi-cell array formula is where the formula and results of the array formula are spilled across a range of selected cells. The formula is composed the same way as that of a single-cell array formula.

However, to have the results spread across a range of cells, all of those cells must be selected before the formula is composed and the CSE keys are used.

 

How is a single-cell array formula different from a multi-cell array formula?

Excel array formulas are designed to produce two kinds of results.

    • An array formula that covers a range of cells and the result spills into multiple cells is known as the multi-cell array formula.
    • An array formula where the formula and result are confined to a single cell is known as the single-cell array formula.

As seen above, with the single-cell array formula, we were able to perform operations with several calculations in one go.

However, if you want these calculations to be performed individually with results populated across a row/column, the multi-cell array formula is what you need.

 

Examples:

The below example must help you learn the primary difference between single-cell and multi-cell array formulas that is attributable to the selection of cell range.

Continuing with the example above, let’s reiterate the source data for finding net sales.

 

Data for quantity, sale price, and discount per product

 

There are two ways how you can find the net sales in the above example, each of which is discussed below.

 

      1. Single-cell array formula:

A single-cell array formula is only to be used if you want the total net sales for all the products added up. To do so, activate a cell, and write in the following formula:

=SUM(B3:B9*C3:C9*(100%-D3:D9))

Hit Ctrl + Shift + Enter to have the single-cell results as shown below.

 

Excel computes the total net sales under one cell

 

$867.7 are the total sales net of discount for the period under calculation.

 

2. Multi-cell array formula:

A multi-cell array formula is to be used when you want a range of results i.e. net sales against each product. With a multi-cell array formula, you can find the net sales for each product only by composing a single formula.

However, what differentiates it from the single-cell array formula is the selection of cell range. To enter the following formula, select cells E3:E9 where the net sales against each product are to be populated.

=(B3:B9*C3:C9*(100%-D3:D9))

Desired range selected and formula entered

 

Press Ctrl + Shift + Enter to yield the results shown below:

 

Excel computes the net sales against each product

 

The multi-cell array formula helps you find the range of net sales of each product in contradiction to the total sales computed by the single-cell array formula.

The SORT and SORTBY functions from the Excel array tool library can be used to sort data. Learn all about them here.

 

Do’s and Dont’s for the Multi-cell array formula:

The very handy multi-cell array formula comes with the following technicalities that you must take note of:

  • Do not forget to select the range of cells where you want the results populated before the formula is entered.
  • Do not attempt to edit, move, delete or add to the contents of cells within an array, as is normally done for regular cells. If you select any cell from an array and edit it right away, the entire worksheet would hang up with the error “You cannot change a part of an array“. Even if you try closing the workbook, Excel won’t let you quit. If encountered, to combat the above issue, write back the multi-cell array formula as originally set up and press ‘Ctrl + Shift + Enter’.
  • Multi-cell array formulas are not compatible with Excel tables.
  • To shrink down the size of an array, do not make edits to the array formula. The existing array must be deleted, and a new array is to be formed.
  • To delete an array formula, you may either select all the cells to which the array extends and delete the same. Or, you may select the array formula in the formula bar, delete it and press ‘Ctrl + Shift + Enter’.

 

Conclusion

Excel array functions make genius functions of the modern-day Excel. However, unfortunate to say, this function is also one of the most intimidating functions for many Excel users. The reason for this lies in lack of practice more than in the complexity of the function.

Go ahead practicing array formulas in light of the examples and tips above to master it in no time. Interested in knowing more Excel Statistics? Hop here.