Array formulas in Excel

What is an array ??

The term array refers to a collection of data residing in a column or row of Excel or a combination of them. In Excel, these elements may reside in a single row (what is called a one-dimensional horizontal array), a column (one-dimensional vertical array ) or multiple rows and columns (two-dimensional array). In Excel you can not create array formulas or arrays that are three-dimensional.

And an array formula ... ??

"I have enough with the formulas, and now you talk about array formulas.. Help!!"

...

If you feel like this right now wait, they are simpler than the name suggests, and learning them it is for Excel power users.


An array formula is a formula that can perform several calculations on one or more of the elements of an array. Array formulas may return multiple results or a single result. For example, you can place an array formula in a range of cells and used to calculate a column or row subtotals. It can also be placed in a single cell and calculate a single number. An array formula that resides in multiple cells is called multiple cells formula, while one that resides in a single cell called a cell formula.


Let me explain it with several examples.


Example 1


Let's say our friend Moe wants to calculate the daily sales from his customers, and have the following table in the range A2:D12.

Moe just want to multiply the number of units sold by the unit price to calculate the total sales per product per customer.

This could easily be calculated in two steps using the traditional way.


In cell E3 write = C3 * D3, and now the formula is copied and pasted into the range E4: E12.


And if we could do it in one go???


To do this we use an array formula.

1. Select the range E3:E12

2. We write in the formula bar

= C3:C12*D3:D13.

3. We press CTRL + SHIFT + ENTER.


Excel formula includes braces ({}, that indicates that we are using an array formula in Excel) and place an instance of it in each cell of the selected range. That happens very quickly, so what you see Moe in column E is the total sales of each product per customer.

We have just seen that the array formula has saved us a step.

But wait, there's more!


Example 2

And what if Moe would like to know the total amount of sales. I mean, we could do what we did in Example 1 and then add all the sales per product per customer to obtain for total sales of the day. In our example that would $41. 

But that would be 2 or 3 steps (depending on whether or not you used array formula in Example 1)

And if we could do in one step ??

We can if we use array formulas. Are you already liking them?


1. Go to a cell outside the range of the table above, for example cell B15

2. Type =SUM(C3:C12*D3:D13)

3. Press CTRL + SHIFT + ENTER.


And there you have the result in one formula.




You see the incredible potential of array formulas??

Download an example file

To download an example file with all the examples that we are covering here click on the following link:

Array formulas

Subscribe and get all entries via email

You can do it by providing your email into "Join the newsletter" box in the right sidebar.

Or follow me via Social Media

Facebook, Twitter and Google+