Excel Multi-cell array formulas are a single formula which returns multiple values and is entered into multiple cells. Hence ‘multi’ in the name.
Let’s look at an example, say we want to return a list of numbers 1 through 10 in cells A1:A10.
Step 1 – very important; first select cells A1 to A10
Step 2 – type in this formula; =ROW(1:10)
It should look like the image below with the formula in cell A1:
Tip: ROW(A1:A10) or ROW($A$1:$A$10) yields the same results as ROW(1:10).
Step 3 – press CTRL+SHIFT+ENTER to complete the formula.
You should have the numbers 1 through 10 in cells A1:A10 and in the formula bar you’ll see the curly braces around your formula as shown below:
Features of Excel Multi-cell Array Formulas
- If you select any of the cells in the range A1:A10 you’ll see the formula is exactly the same. In effect the range 1:10 is absolute yet I haven’t had to use the $ sign to lock them in.
- If you try to delete or insert cells or rows between A1:A10 you’ll get this error:
- To select all of the cells relating to the array formula select one cell containing the formula and press CTRL+/
- To edit the formula simply click on any of the cells in the array, make your changes and press CTRL+SHIFT+ENTER to re-enter the formula.
- To delete a multi-cell array formula first select the array, in this case cells A1:A10 (remember the keyboard shortcut is CTRL+/), then press DELETE. Alternatively you can delete all of the rows or the column containing the formula.
Multi-cell Array Formula Gotchas
While you can’t insert rows or cells within an array, you can insert rows above and this may alter the results of your formula. For example, if I insert a row above cell A1 my formulas and cell references adjust like so:
This can be catastrophic for your formula, especially if you’re nesting ROW in another formula and the impact is not transparent like the example above.
The solution is to use this formula instead of ROW(1:10):
=ROW(A1:A10)-ROW(A1)+1
Now when I insert a row above A1 my formula adjusts but I still get numbers 1 through 10:
Tip: you can insert numbers 1 through 10 across a row with this multi-cell array formula:
=COLUMN(A1:J1)-COLUMN(A1)+1
Don’t forget to press CTRL+SHIFT+ENTER to complete the formula.
When to use Multi-cell Array Formulas
We could just as easily insert the numbers 1 through 10 in column A with this formula (entered in any cell):
=ROW(A1)
Then copy down as needed. Of course this formula is subject to error if rows are inserted above it, so you’re better off using the ROWS function like this:
=ROWS($A$1:A1) or =ROWS($1:1)
And don’t forget there’s always Auto-fill or Fill Series for a quick list of numbers.
However, the benefit of using a multi-cell array formula is that it prevents rows/cells being inserted within the range without the need for worksheet protection.
Multi-cell Array Formula Examples
So far we’ve looked at a basic example, but often you’ll find more complex multi-cell array formulas that contain nested functions like this VLOOKUP with COLUMN in cells G2:I2:
I’ll explain; I have an Excel Table in cells A1:D6 called Table1:
And in cell F2 I have a data validation list containing the SKU’s:
When I choose an SKU from the Data Validation list a multi-cell VLOOKUP array formula in cells G2:I2 returns the Model#, Supplier and Price for the selected SKU:
The only difference between this multi-cell VLOOKUP array formula and a regular VLOOKUP formula is the COLUMN functions used to return the col_index_num argument for VLOOKUP.
When evaluated COLUMN returns an array {2,3,4} for the col_index_num, as you can see below:
And because I’ve selected 3 cells for my multi-cell array formula it returns the value from 2nd column in Table1 to cell G2, the value from the 3rd column in H2 and the value from the 4th column in I2.
Remember the main benefit of this formula over a regular VLOOKUP is that you can’t insert columns between G and I.
The other benefit is that if an inexperienced user attempts to edit this formula they’re likely to get the error message “you cannot change part of an array”, and that might just be enough to prevent them from messing up your formulas. No promises though. Those users can be tenacious 😉
Multi-cell Array Formula Functions
There are also quite a few functions in Excel that return an array of results. That is they return multiple values and deposit each one in its own cell.
For example the FREQUENCY function does this. The syntax is:
FREQUENCY(data_array, bins_array)
FREQUENCY calculates how often values occur within a range (data_array), for each value in the bins_array. Let’s look at an example:
Column A contains a series of values (our data_array). We want to count how often each number in column A occurs. In column C we have our bins_array, which is simply a list of distinct numbers from column A.
In column E we enter our FREQUENCY formula by first selecting 4 cells (E2:E5, one for each bin), then the formula:
=FREQUENCY(A2:A13,C2:C5)
Press CTRL+SHIFT+ENTER to complete the formula.
The image below shows the FREQUENCY formula has counted 2 occurrences of the number 1 in our data_array, 3 occurrences of number 2 and so on:
Advantages of multi-cell array formulas
- User error more easily avoided since editing them is difficult unless you know how.
- Prevents inserting rows/columns within the array range, but not above or below.
Caution
Array formulas applied to large data sets can be slow and inefficient so use them with caution.
Thanks
Thanks to Mike Girvin and his incredible book CTRL+SHIFT+ENTER Mastering Excel Array Formulas. I learnt endless tips in this book. It’s a must read for any Excel enthusiast.
Thanks to Simlaoui’s comment on last week’s post on the ROW function which inspired me to write about multi-cell array formulas.
Disclosure: if you purchase Mike's book I earn a couple of dollars. That's not why I recommend it though. It's a brilliant book and you'll learn a lot, even if you're already familiar with array formulas.
Rajesh Sinha
Sometimes I’ve been around your articles,, examples are quite useful to realize capacity of an Array formula,,
If possible share with us that how to filter more than one row,, like Advance Filter on a criteria or multiple,, ☺
Catalin Bombea
Hi Rajesh,
You can see this article for more details on advanced filters: Advanced filters
Catalin
Rajesh
Yur example is quite useful,, and professional too.
Philip Treacy
Thanks Rajesh
Gaurav Agarwal
Hi Mynda,
I’m Gaurav from Bangalore, India and I want to thank you from the bottom of my heart.
I cannot explain how much this website and posts have helped me in my career. I just got a new Job Role in my Company because of the increased efficiency in excel. Also, bagged an award recently as the Problem Solver! Thanks again.
Philip Treacy
Thanks Gaurav, that is fantastic to hear. So glad that we can help you out.
Regards
Phil
MF
“The other benefit is that if an inexperienced user attempts to edit this formula they’re likely to get the error message “you cannot change part of an array”, and that might just be enough to prevent them from messing up your formulas. No promises though. Those users can be tenacious”
Can’t agree more! 🙂
Mynda Treacy
🙂 thanks, MF.
Kevin Lehrbass
Thanks for the post Mynda. These multi cell arrays are rare but are sometimes the perfect solution to a complex question.
I use Goto Special / Current Array to select the array area if I need to update it.
I’ve used these arrays with Transpose function hundreds of times over the years.
Cheers
Kevin Lehrbass
Mynda Treacy
Cheers, Kevin. I like the Go To Special tip. It’s a good one if you can’t remember the keyboard shortcut.
Mynda