a.k.a. ARRAYFORMULA

You’ve probably written a formula in the first row of your sheet and then copied it all the way down the column. However, this can cause issues:

When new rows get added – the formula doesn't get copied down.

Glide can see this formula and can count it as an existing – even if you don't see data in your sheet. This results in new records that are added in your Glide app appearing at the bottom of your sheet

Well `=ARRAYFORMULA`

can help with that.

The `=ARRAYFORMULA`

can be used for lots of things but one of the simplest is copying.

Let's say I have a column in my sheet that I want to duplicate and keep updated. I can use `=ARRAYFORMULA()`

to do this. In the image below we write `=ARRAYFORMULA(A2:A). `

The `A2:A`

is what we call an open ended reference - meaning that it starts at A2 and goes all the way to the end of A.

Instead of using `=A2`

in column 4 and then copying the formula down, we have wrapped the `=A2`

formula in an `=ARRAYFORMULA`

which allows us to apply our formula to an array of rows.

You can also use the ARRAYFORMULA to concatenate (join) text into greater structures.

For example, here is a sheet with friends’ birthdays. We want to create a `Message`

column that uses the `Next Age`

and `Days Away`

columns to compose a message about their upcoming birthday. We're using the formula `="Turns " & D2 & " in " & E2 & " days”`

to calculate the message to display in cell `F2`

By wrapping this formula with `ARRAYFORMULA`

and changing cell references `D2`

and `E2`

to *open-ended ranges* `D2:D`

and `E2:E`

, we can calculate the message for all rows.

`="Turns " & D2 & " in " & E2 & " days”`

becomes

`=ARRAYFORMULA("Turns " & D2:D & " in " & E2:E & " days”)`

You can see that the formula is applied for every row, so now we have a message for all of our friends! One small gotcha is that the full column is computed, even for rows without birthdays:

We can avoid generating messages for empty rows with an `IF`

formula that checks whether our input columns have data. Specifically, we’ll require `Next Age`

(column `D`

) to be non-empty to calculate a message.

`=ARRAYFORMULA("Turns " & D2:D & " in " & E2:E & " days”)`

becomes

`=ARRAYFORMULA(IF(LEN(D2:D) = 0, "", "Turns " & D2:D & " in " & E2:E & " days”))`

Now we can use the `Message`

column as the list item detail in a Birthdays Glide app to see how old our friends are, and how close their next birthday is at a glance:

Glide also has a **Template Column** that can achieve this in the data editor.

You can also encase your functions with the `=ARRAYFORMULA`

to copy the formula down for new rows.

Below we have a list of scores and their grade. We have a formula that outputs a "Pass" if the score is greater or equal to 10 and a "Fail" if it's less → `=if(A2>=10,"Pass","Fail").`

Instead of copying this formula down, we can encase it in the `=ARRAYFORMULA`

and use open-ended references to automatically copy it down to all rows.

However, when we do this we'll get "Fail" for all empty rows. If we add an `=IF()`

statement in our function we can make our function not populate rows that are empty.

`=ARRAYFORMULA(if(D2:D>=10,"Pass",if(D2:D="","","Fail")))`

Now the formula will only populate when new rows are added and the column with the score in it is filled.