Last Updated on November 18, 2021 by Jake Sheridan
In this tutorial, you will learn how to use array formula Google Sheets.
Array formulas can get a bad rap for being complicated and difficult to understand, but they’re not as difficult to use as you might imagine and can save a lot of time when the same formula is used more than once, so it’s worth learning how they work.
This tutorial explains the basics of how array formulas work, then provides a simple example of how to use array formulas to add row numbers to a column in Google Sheets.
What is an Array Formula?
An array formula is a type of formula in Google Sheets that returns values in multiple rows and/or columns. Standard, non-array formulas can only ever return values in a single cell, but an array formula can return values in an entire range. For example, in the screenshot below, all the row numbers in column A are generated by a single array formula.
Array formulas only need to be edited once to update an entire range, making spreadsheets quicker and easier to maintain. They also automatically perform the same calculations to new rows or columns that are inserted into the middle of the range the array formula acts on. In the following screenshot, you can see the comparison between an array formula used to populate row numbers, and a regular formula. A new row has been inserted and the array formula automatically performs the same calculation on the new row, while the standard formula would need to be manually added to the new row
Adding Row Numbers Using Array Formulas
Now that you have a general idea what an array formula, here’s a practical example of how to use one to add row numbers to a column:
First, make sure the array formula has enough space to expand. In this case, we want to populate a column with row numbers, so ensure you have a clear column
For this example, we’ll leave space for a header and start the array formula in cell A2. Type the following formula in cell A2: “=ARRAYFORMULA(ROW(A2:A100))”. Hit enter and row numbers will appear in the range A2:A100
To understand the above formula, first consider how the ROW function normally works: It normally takes a cell reference and outputs the corresponding row. If you input a range (for example, ROW(A2:A100)), ROW will still only output a single row number for the first cell in the range. This is where the ARRAYFORMULA function comes in. It turns ROW (or whatever else is inside it) into an arrayformula, allowing the ROW operation to be repeated on every cell in the input range, and outputting all the results to the spreadsheet
Subtract 1 from the output of the ROW function inside ARRAYFORMULA to start the output with the number 1: “=ARRAYFORMULA(ROW(A2:A100)-1)”
Example Spreadsheet: Make a copy of the example spreadsheet
In this tutorial, I covered how to use array formula Google Sheets. Want more? Check out all the Google Sheets Tutorials.