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)
Step 3 : 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:




When evaluated COLUMN returns an array {2,3,4} for the col_index_num, as you can see below:

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:

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.
For more comprehensive Excel Formulae Course, click here.
Team Excelgoodies – www.Excelgoodies.com
I like the helpful information you provide in your articles. I will bookmark your weblog and check again here regularly. I’m quite certain I will learn many new stuff right here! Best of luck for the next
It’s exhausting to search out educated individuals on this subject, however you sound like you recognize what you’re speaking about! Thanks