a.k.a. ARRAYFORMULA

Glide now has a Template Column which allows you to do a lot of what is shown below with ARRAYFORMULA.

One invaluable yet obscure feature of Google Sheets is the ability to calculate an entire column. You’ve probably written a formula in the first row then copied it all the way down the column, but there’s a better way!

For example, I have a sheet with my friends’ birthdays and I want to create a `Message`

column that uses the `Next Age`

and `Days Away`

columns to compose a message about their upcoming birthday. You can see I’m 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`

, I 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 I have a message for all of my friends! One small gotcha is that the full column is computed, even for rows without birthdays:

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

formula that checks whether our input columns have data. Specifically, I’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 I can use `Message`

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