Google Sheets | ARRAYFORMULA | Function | How to Use | Example | Spreadsheet | Tutorial

The Google Sheets ARRAYFORMULA function's result automatically expands to multiple rows and or columns. This function also assists to use an array as an argument in a function that does not support the same. Some examples of such functions are IF and LEN.

-------------------------------------
How to Filter Data in a Pivot Table in Google Sheets?

Filter data in a pivot table to display only specific data:

-------------------------------------
How to Create a Pivot Table in Google Sheets?

Create a pivot table for calculation and in-depth data analysis:

-------------------------------------
Which one to Use? A Pivot Table, or COUNTBLANK Function, to Count Blank Cells in Google Sheets:

Gives steps to use a pivot table or COUNTBLANK to count blank cells:

-------------------------------------
How to Use a Pivot Table to Sum by Rows or Columns without using Formula in Google Sheets?

Sum numbers by rows and or columns easily, using a pivot table:

-------------------------------------
How to Create a Pivot Table to Get Maximum Value without Using Formula in Google Sheets?

A pivot table assists to get the maximum value in a data range without using a formula:

-------------------------------------
How to Use Quick Analysis in Excel?

Use the quick analysis tool to conduct a faster data analysis:

-------------------------------------
How to Create Reflection Effect in Word?

Create text reflection effect in Word:

-------------------------------------
How to Create Index in Word?

Create a document index in Word:

-------------------------------------
How to Create a Table of Contents in Word?

Steps to create a table of contents (TOC) in Word:

-------------------------------------
How to Set Margins in Word?

Set top, bottom, left, and right margins in Word:

-------------------------------------

Syntax of Array Function Formula

=ARRAYFORMULA(array_formula)

Start the formula with an equal-to symbol

ARRAYFORMULA is the name of the function.

array_formula is the argument. It can be a range or an expression with one or more ranges. In case of the latter, the ranges should be of the same size. The argument can also be a function whose result fills more than one cell.

Example of Array Function Formula

Example 1

Using an Expression in ARRAYFORMULA

=ARRAYFORMULA(A2:A6+B2:B6)

The above formula adds each value in the range A2 to A6 with each of its
corresponding value in the range B2 to B6 and displays the first result of the addition in the cell with the formula and automatically expands the results to four cells below the cell with the formula.

Example 2

Using a Function in ARRAYFORMULA

=ARRAYFORMULA(IF(H2:H132="JR347", "Check Inventory", "---" ))

Because the IF function does not support the use of an array in the logical expression, IF can be nested inside ARRAYFORMULA and an array can be used. IF checks whether any of the cell in the range H2 to H132 has the text JR347, and if a cell does, then its corresponding cell will be assigned the text Check Inventory. Otherwise, cells without the text JR347 will be assigned three dashes.

Review this video tutorial, which gives the steps to use the Google Sheets ARRAYFUNCTION with examples.