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 you can 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 handy Ctrl+ Shift + Enter keyboard shortcut to automatically input ARRAYFORMULA( before the formula is already in the cell.
Why Is ARRAYFORMULA Useful?
Using ARRAYFORMULA in your spreadsheets has a lot of advantages. Firstly, you can 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 feature that 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:
formula: This is an expression or a range that uses a single or multiple ranges of the same size
This argument can be:
A cell range A math expression using one range or multiple cell ranges of the same size. A function that returns results that are greater than one cell.
You can also add this formula to existing non-array functions in spreadsheets too. You can do this by using the Ctrl+ Shift+ Enter shortcut.
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:
Click on the cell where you wish to use the ARRAYFORMULA. Type in the initial part of the formula, which is =ARRAYFORMULA(. Now type in an iteration of the formula seen at the beginning of this example. In this case, we use A2:A7 & " " & B2:B7. Note that we used the entire ranges instead of using the individual cells. Add the closing bracket. Finally, press Enter to execute the formula.
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 the IF formula in 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:
Click on the cell range that contains the IF formula. The formula in this case is =IF(B2:B5<25,“order”, “no”). Move your text cursor between the equal to (=) and the “IF” in the formula. Write the initial ARRAYFORMULA statement here, which is ARRAYFORMULA(. Move to the end of the formula and add an extra bracket. Press Enter to execute the formula.
The formula will execute it in all the selected cells in just one instance. This would work with other IF statements like SUMIF in Google Sheets too.
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.
