Today I had to create a dynamic array of fields in Google Sheets. I've had to do this a few times, and figure out how to do it each time (as I do it infrequently).
Then I decided I didn't even need to do this... but as I spent far too long figuring out how to do this, I thought I'd share it!
You create a dynamic array when you
- Have a list of things that you want to automatically increase in size
- Have a table you want to automatically populate
The reason I wanted to do this was I often have a list of things, e.g. web pages I want to analyse. I want to analyse these things using a standard process that duplicates every time I add something to the list.
Whenever I add something to the list, I don't want to remember all the places I have to add rows and copy-paste. I'd much rather have a dynamic array.
In brief, you create a dynamic array in Google Sheets by using split
, transpose
, and concatenate
, all in the context of an arrayformula
.
Here's why you use these three:
concatenate
: Use this to connect together series of strings, using commas for example.split
: Now you split these strings aparttranspose
: To change the shape into something more convenient.
The arrayformula
iterates through the range.
Example 1: A Dynamic list with sub-lists
I had a list of websites and I wanted to have a sub-list for each one.
And I wanted the whole thing to build dynamically!
Let's say I have this initial list of things:

And I want to build this second list:

I'm going to use this structure to create my dynamic array:
=arrayformula(transpose(split(concatenate(range_of_items&splitter),",")))
In this case my splitter is going to be this string: , Â Favourite 1, Â Favourite 2, Â Favourite 3,
.
There's an initial comma, to separate the splitter from the list of items. Then there are spaces, and then a comma after each one.
The formula does this:
- Operating in an array formula, it operates on the items one by one: Fruit, Vegetables, etc.
- It takes each item and appends the splitter string to it, so each one becomes e.g.
Fruit, Â Favourite 1, Â Favourite 2, Â Favourite 3,
- It then uses
split
to divide that into four separate columns.
Once those are a bunch of columns, it uses transpose
to split it all into a vertical range.
The next stage is to dynamically create the splitter.
This is another concatenate
inside the arrayformula
.
concatenate(" "&range_of_split_values&",")
Again, this puts two spaces in front of each one (for aesthetics), and sticks a comma after the end.
My final formula looks like this:
=arrayformula(transpose(split(concatenate(range_of_items&","&concatenate(" "&range_of_split_values&",")),",")))
Example 2: Create a dynamically generated table
The second reason you might want to do this is to create a dynamically generated table.
These work a bit like a pivot table but function entirely through formulae. So it's easier to do calculations on the final result.
You might have various reasons for doing this yourself — I'll leave it up to you.
It's similar to above, but you use the formulae for split
and concatenate
a bit differently, and you don't have to use transpose
.
Here's the general formula to create a dynamic table in Google Sheets with vertical_range
along the left and the horizontal_range
across the top.
=arrayformula(vertical_range&"'s "&split(concatenate(horizontal_range&","),","))
