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 apart
• transpose: 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:

1. Operating in an array formula, it operates on the items one by one: Fruit, Vegetables, etc.
2. It takes each item and appends the splitter string to it, so each one becomes e.g. Fruit,  Favourite 1,  Favourite 2,  Favourite 3,
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_rangeacross the top.

=arrayformula(vertical_range&"'s "&split(concatenate(horizontal_range&","),","))