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.

Sometimes you want the same value in every row. This is easy to do with the Single Value Column.

However, computed columns like the single value column can't be used as a Row Owner column. So you can't use the single value column to put an email in every row and then make that column a Row Owner column.

Instead, we can use ARRAYFORMULA to do this, in combination with IF and ISBLANK. Here's a look at the full formula and its effect.

`=ARRAYFORMULA(if(ISBLANK(A2:A), "","marie@glideapps.com"))`

Let's break this down. The first part is our IF & ISBLANK formula 👇

`=if(ISBLANK(A2), "","marie@glideapps.com")`

This function is saying; 'If the A column to the left is blank, then don't add value to this column, but if it's not blank then add the value 'marie@glideapps.com'.

This is great, but as we can see - it's not applying to the rows below. To do this, we can turn our cell reference (currently A2) into an Array reference and then encase the formula in the =ARRAYFORMULA().

We can now see that the array reference, combined with =ARRAYFORMULA() is copying down every time we have a new row.

This is particularly useful when you want the same value to be applied to every row in a table that often has new rows added – like a form submissions table.

With this technique, you can add an email to every new item and then protect all those items by making that column a Row Owner's column.