Array formulas can really open the possibilities of what is possible within your spreadsheets.
If you’ve never used array formulas before, they may seem pretty intimidating, but once you learn the basics, they are pretty simple to understand.
In this tutorial, I am going to teach you the basics of array formulas and show you how to use the ARRAYFORMULA function in Google Sheets.
Table of Contents
What is an Array Formula?
In Google Sheets, ARRAYFORMULA is a function that allows your returned values to display across multiple rows and/or columns.
This means that an array formula will return a range of cells with its output. You can use array formulas in combination with non-array functions to output ranges of data across multiple rows and columns.
To better understand what I mean by this, let’s take a look at an example.
Let’s look at this example data with different products, their sales numbers, and sale price. I want to calculate the total sales revenue of all the products in the table.
I could do this by first creating a formula to multiply columns B and C together and then pasting the formula down to each row, and then summing together every row, or I could do this entire process in one formula by using ARRAYFORMULA.
First, let me show you how ARRAYFORMULA works by just using it to get the sales revenue across each row in my data with the example below.
You can see that using ARRAYFORMULA, I only entered one function in my data to multiply the entire range of data and then function outputted data across multiple rows.
Now let me show you getting the entire sum of sales revenue in the example with one formula.
You can see in the example above that one simple formula using ARRAYFORMULA did what would take multiple steps using other functions. In the example, I was able to multiply the range of cells of column B by column C and sum them all together.
Hopefully, by now you have a better understanding of what an array formula is and the benefits of using them. Next, I will show you how to use these formulas in your spreadsheet.
How to Use ARRAYFORMULA in Google Sheets
The syntax of ARRAYFORMULA is:
- array_formula – a mathematical expression using one or more cell ranges of the same size
The key takeaway here is that ARRAYFORMULA can be used with almost any mathematical expression in Google Sheets, but the ranges have to be the same size.
In my previous example, I used this formula to multiply my range of data and get the entire sum: =ARRAYFORMULA(SUM(B2:B5*C2:C5))
But if I change my range so that the ranges are no longer the same size, this happens:
You can see in the image above since my ranges are no longer the same size, I now get an error message. This is one of the most important things to remember with array functions.
They work on almost any mathematical expression, but the ranges have to be the same size.
Using ARRAYFORMULA on an Entire Column
If you want to apply a formula to an entire column in Google Sheets, ARRAYFORMULA is one of the best ways to do this.
Here are the steps to do this:
1. Select the cell where you want your formula to calculate and press the equals signs (=) on your keyboard. Type “ARRAYFORMULA” and press Tab on your keyboard to start the formula
2. Put in your first column that you want to use in your formula
3. Put in whatever function or mathematical operator you want to perform
4. Then, put in the second column that you want in your formula
5. Press Enter on your keyboard and your formula will calculate across every column that you have specified in your ranges
You can see that this way a quick way to apply a formula to an entire column, but where the data ended, my formula still calculated and leaves zeroes. If you don’t want this, just put in the cell ranges up until your data ends.
ARRAYFORMULA Keyboard Shortcut
Again, the ARRAYFORMULA function can be used with just any mathematical expression in Google Sheets. There is a keyboard shortcut to automatically wrap your formula in an ARRAYFORMULLA function.
The keyboard shortcut is:
I am using this shortcut in the example above. You can see I start with a formula that is multiplying my range in column B by the range in column C, and by pressing (CTRL+SHIFT+ENTER) on my keyboard it immediately wraps my entire formula in the ARRAYFORMULA function.
ARRAYFORMULA with the IF Function
One of the best features of array formulas is that they can be used with non-array functions. One common non-array function that many spreadsheet users use with the array formula function is the IF function.
Here’s how to use ARRAYFORMULA with the IF Function:
- Consider this example where I want to see if the values in column A are greater than 50. First, you would have to create your IF statement on the entire range of data in column A
- Next, you simply would wrap your entire IF function inside the ARRAYFORMULA function
- One your formula is done, simply hit ENTER on your keyboard to calculate and your IF function should calculate across the entire range of data you specified in step 1
When used properly, an array formula can be a powerful tool for quickly performing multiple formulas across a range of cells. They will also allow you to output data across multiple rows and columns which can be incredibly useful for analyzing data.
We recommend watching the video at the top of the page and trying to replicate all the examples we have used in this tutorial. If you have done all that, you should have a firm understanding of the basics of using these formulas in your spreadsheet.
More Google Sheets Tutorials:
How to Count Unique Values
How to Do Basic Math Operations
How to Do Multiple IF statements
How to Use Array Formula with VLOOKUP
How to Use the OFFSET Function