How to create Dynamic Arrays in Google Sheets with Concatenate, Split and Transpose

Something I have to often do is to create a dynamic array of fields in Google Sheets.

Each time, I forget a couple of steps, and have to figure out how to do it each time (as I do it infrequently).

As I spend far too long figuring out how to do this, I thought I’d share it with myself as well as with everyone!

What is a dynamic array in Google Sheets?

The term “dynamic array” can mean a number of things.

In this case, by “dynamic array” I mean a set of calculations or functions that automatically expands based on a master list of things to operate on.

Recently for example, I was building a workbook of web pages I was analysing (web pages I own and manage). I wanted to add web pages to a master list and have the analysis self-replicate.

You create a dynamic array when you

• Have a list of things that you want to make something else automatically increase in size
• Have a table you want to automatically populate

For example: you are doing a P&L on ten businesses, and add one more. Your data all comes from one source. So when you add your eleventh business to the list of ten, you want your P&L calcs to automatically expand for the new business without having to tediously copy and paste everywhere.

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.

Nuts and bolts: Using Concatenate, Split, and Transpose

In brief, you create a dynamic array in Google Sheets by using concatenate, split, transpose, all in the context of an arrayformula.

Here’s why you use these three:

• concatenate: Use this to connect together series of cells into one string, separated by 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. Fruit, for example.

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&",")),",")))

Now, if I add another element into the first or second list, the whole array changes. See for example below, I added “Potato” into the second list.

Example 2: Create a dynamically generated table

The second reason you might want to do use a dynamic array is to create a dynamically generated table.

These work a bit like a pivot table but function entirely through formulas.

Two advantagses of dynamically generated tables are:

1. It’s easier to do calculations on the final result. You don’t have to do pivot table formulas, which can get a bit arcane!
2. You don’t have to “update” the pivot table.

You might have various reasons for doing this yourself — I’ll leave it up to you.

Creating a dynamically generated table is 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&","),","))

As with the above, if you add an item to either array, the dynamically generated table expands to accommodate.

If you want to see the workbook with these examples, follow this link here.