# Calculating columns

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: