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,
transpose, all in the context of an
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.
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:
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
splitto 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
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:
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:
- 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!
- 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
concatenate a bit differently, and you don’t have to use
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.
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.