How to Use the ARRAYFORMULA Function in Google Sheets
It’s slow and frustrating to insert and apply a function to every single cell or data range. To hasten this process, you can use the ARRAYFORMULA function in Google Sheets. This function outputs values from cell ranges into multiple columns and rows.
Unlike most formulas in Google Sheets, this formula can be expanded, which means that when new data is added, the formula automatically updates itself. This article discusses what the ARRAYFORMULA is, its syntax, and how you can use it in Google Sheets. Read on to master it quickly.
What Is an ARRAYFORMULA?
ARRAYFORMULA is a handy Google Sheets function that outputs a cell range instead of single values, and it’s possible to use it with non-array formulas to turn them into array compatible ones.
Although the definition can be confusing to someone unaware of the formula and its usage, the examples we have further down in the guide will make it clear. But, to simplify its use, it allows you to use one formula across several columns or rows.
It can use either one cell range or multiple ones of the same size. You can use the handyCtrl+ Shift + Enterkeyboard shortcut to automatically inputARRAYFORMULA(before the formula is already in the cell.
Why Is ARRAYFORMULA Useful?
Using ARRAYFORMULA in your spreadsheets has a lot of advantages. Firstly, you’re able to use this formula with a massive data set, meaning you won’t have to repeat formulas into several columns. Not only will this save you time, but Google Sheets will run large spreadsheets a lot smoother as fewer formulas need to execute simultaneously.
This formula is expandable, which means that a change in one place will expand the whole data range. Not only that, the formula is dynamic as well. This means that you won’t have to manually edit the formulas whenever you make an edit to the sheet.
ARRAYFORMULA vs. Autofill
Google Sheets does offer a suggested autofill featurethat allows you to apply the used function in the rest of the rows. However, using that means you won’t get any of the added benefits of the ARRAYFORMULA.
Additionally, when you update a cell, there is always a risk that autofill won’t be able to update the data accurately. This can cause a lot of cells to be out of sync, which results in discrepancies in the data.
The Syntax for ARRAYFORMULA
The ARRAYFORMULA uses one argument, which can consist of other formulas or cell ranges. The argument is required for the formula to work. Here is the syntax for this formula:
Here is the argument used in this formula:
This argument can be:
You can also add this formula to existing non-array functions in spreadsheets too. You can do this by using theCtrl+ Shift+ Entershortcut.
Using ARRAYFORMULA With Other Sheets Functions
Just looking at the syntax for this formula is not enough. Since ARRAYFOMULA essentially needs a second function inside it to work, we’ll need to look at some examples. Here are a few ways you can use this function in Google Sheets.
1. Simple ARRAYFORMULA
In this example, we have a spreadsheet with names in it. Let’s say we want to combine the first and last names into a new cell containing the full name. To do this, we can use this formula:
As you can see, this formula works on a per-cell basis. This means that the cell address needs to be input to execute the formula. When using ARRAYFORMULA, this can be done automatically without the need to execute the formula multiple times.
Here are the steps you need to follow to use ARRAYFORMULA to combine cells in Sheets:
2. ARRAYFORMULA With IF Functions
In this example, we have sample data of a few shop items and their inventory levels. We use the IF formula to check whether a sufficient amount of the item is present in the stock.
Suppose we were to simply autofill theIF formulain every cell individually. In that case, it could quickly add up to a large number of instances of the same formula being executed, which can slow down your Google Sheets. For this reason, it’s better to use ARRAYFORMULA.
Here is how you’d combine ARRAYFORMULA with an existing IF function in Google Sheets:
The formula will execute it in all the selected cells in just one instance. This would work with other IF statements likeSUMIF in Google Sheetstoo.
Wrapping Up ARRAYFORMULA in Google Sheets
The ARRAYFORMULA function is one of the most powerful functions in Sheets. It can perform many functions at once and can be combined with a wide variety of other functions. Although it can seem confusing at first, you will start using it everywhere in your spreadsheets once you get the hang of it.
Practicing the ARRAYFORMULA will also help you understand other array-compatible functions in Google Sheets and Excel. Keep at it, and you’ll be a spreadsheet pro in no time.
Want to get the most out of Google Sheets? Here’s how to use Google Sheets like a pro to get things done quickly and easily.
I found my TV was always listening—so I shut it down.
Your iPhone forgets what you copy, but this shortcut makes it remember everything.
Make sure you don’t miss these movies and shows before Netflix removes them.
So much time invested, and for what?
Free AI tools are legitimately powerful; you just need to know how to stack them.
