Check Sheet Logo
Check Sheet

ARRAYFORMULA

The ARRAYFORMULA formula in Google Sheets allows for the application of a formula to an entire column or range of data, without needing to manually copy the formula to each cell. It is most commonly used to simplify and automate complex calculations across large data sets.

Usage

Use the ARRAYFORMULA formula with the syntax shown below, it has 1 required parameter:

=ARRAYFORMULA(array_formula)
Parameters:
  1. array_formula (required):
    The formula or function to apply to the entire range of data.

Examples

Here are a few example use cases that explain how to use the ARRAYFORMULA formula in Google Sheets.

Calculating a running total

By using ARRAYFORMULA with a cumulative sum formula, a running total can be quickly calculated for a large range of data.

Converting data types

Using ARRAYFORMULA with a formula that converts data types (such as text to numbers) can quickly transform an entire column or range of data.

Splitting data into multiple columns

By using ARRAYFORMULA with a formula that splits text into multiple columns, a large range of data can be quickly and easily organized.

Common Mistakes

ARRAYFORMULA not working? Here are some common mistakes people make when using the ARRAYFORMULA Google Sheets Formula:

Missing curly braces

Users often forget to enclose the formula in curly braces, which are necessary for ARRAYFORMULA to work.

Using incompatible functions

ARRAYFORMULA does not work with all functions in Google Sheets, make sure to use only compatible functions.

Mismatched ranges

If the ranges in the formula do not match, ARRAYFORMULA will not work as expected.

Not starting the formula in the first row

ARRAYFORMULA must be started in the first row of the column to work correctly.

Incorrect syntax

Make sure to use the correct syntax for ARRAYFORMULA, including the parentheses and the argument.

Related Formulas

The following functions are similar to ARRAYFORMULA or are often used with it in a formula:

  • SUM

    The SUM function in Google Sheets adds up a range of numbers. This function is most commonly used to sum the values in a range of cells.

  • AVERAGE

    The AVERAGE function calculates the average (arithmetic mean) of the values passed to it. It is commonly used to find the average of a range of cells containing numerical data.

  • IF

    The IF formula is a logical function used to test a condition and return one value if the condition is true and another value if the condition is false. It is commonly used to create conditional statements and perform calculations based on certain conditions.

  • VLOOKUP

    The VLOOKUP function is a lookup formula used to search for a value in the first column of a range of cells (the search key) and return a value in the same row from a specified column in that range. This function is most commonly used to look up and retrieve data from a table.

Learn More

You can learn more about the ARRAYFORMULA Google Sheets function on Google Support.