ARRAYFORMULAformula 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.
- How to use
- Examples of using
ARRAYFORMULAformula not working?
- Similar formulas to
ARRAYFORMULA formula with the syntax shown below, it has 1 required parameter:
- array_formula (required):
The formula or function to apply to the entire range of data.
ExamplesHere are a few example use cases that explain how to use the
ARRAYFORMULAformula in Google Sheets.
Calculating a running total
ARRAYFORMULA with a cumulative sum formula, a running total can be quickly calculated for a large range of data.
Converting data types
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
ARRAYFORMULA with a formula that splits text into multiple columns, a large range of data can be quickly and easily organized.
ARRAYFORMULAnot working? Here are some common mistakes people make when using the
ARRAYFORMULAGoogle 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.
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.
Make sure to use the correct syntax for ARRAYFORMULA, including the parentheses and the argument.
The following functions are similar to
ARRAYFORMULA or are often used with it in a formula:
SUMfunction in Google Sheets adds up a range of numbers. This function is most commonly used to sum the values in a range of cells.
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.
IFformula 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.
VLOOKUPfunction 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.
You can learn more about the
ARRAYFORMULA Google Sheets function on Google Support.