Sheets Tip #1: Calculate entire columns with ARRAYFORMULA

When creating apps with Glide, 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 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:

Try using ARRAYFORMULA to make your Glide apps more powerful!