Use of formulas in spreadsheets allow to quickly perform calculations and get a total of multiple rows, cells, or columns in a spreadsheet. Spreadsheets support multiple functions like addition, subtraction, multiplication, average etc. Formulas can be applied to a cell or group of cells, single row or column or multiple rows and columns. Formulas can be of several types which can perform operations on single or multiple values.

In this article we will learn more about an Array formula in Google Sheets, which is a specific type of formula that performs an operation on multiple values rather than on a single value, we will learn how to apply array formulas, its use and functionality.  

Array formula in Google sheets

An Array formula is a specific type of formula which can perform multiple calculations on one or more items in an array. Array can be a row or column of values, or a combination of rows and columns of values. We can use array formulas to perform complex tasks such as quick creation of sample datasets. The final output of an array formula will be either one item or an array of items based on what formula was applied. For e.g., below formula is an array formula which returns the sum of all characters in a range: – 

 {=SUM (LEN (range))} 

The structure of an Array Formula has 2 parameters

• A range 
• A mathematical expression using ranges of the same size
• A function that returns a result greater than one cell 

An array is a collection of one or more items, a normal formula results in a single value, array formula outputs a range of cells. Let’s look at one example to understand how the array formula works in Google Spreadsheets. 

To calculate the price of all products one way is to apply a sum formula in Column E and multiple values in Column C to Column D and arrive at the final result. Other way is to use an array formula and get straight to answer like this 

=ArrayFormula (SUM (C2:C5 * D2:D5))

When we use the multiplication (*) operator in a spreadsheet we give it two numbers or two cells to multiply together. Here in this array formula, we are giving two ranges or two arrays of data as input 

= C2:C5 * D2:D5 

In normal circumstances if above formula is put it will give an error as #VALUE error however, we need to tell Google spreadsheet that this will be an ArrayFormula and this can be achieved in two ways. 

Either we type word ArrayFormula and add and opening / closing brace to wrap formula or just hitting Ctrl+Shift+Enter (Cmd + Shift+Enter on a MAC system) and Google sheets will add ArrayFormula wrapper 

For each row Google will do the calculation and present the result in output as below.

Quick bits

Array formula only works if the size of two arrays match as in above case where each row has four numbers 

Array formula with IF function used below in a non-array function being used with array it works when we designed IF formula as array formula. 

A standard IF statement to check if value in column A is over INR 10000 or not:

=IF (A2>10000, “Yes’, “No) 

This formula can be copied into each row to get output, we can change this into a single Array formula at the top of the column and run the IF statement across multiple rows in which we have values. 

Single Array formula will produce an output array which fills row 2 to row 10 with Yes and No. 

Features of Google Sheets Array Formula

• Even for a huge dataset it is a single formula 
• It is expandable in nature and a change in one place will expand down in the entire data range
• It is dynamic so when a new row is introduced in dataset the formula will automatically be applied to 
• It is consistent so if we click on one cell within a multi cell array formula we see same formula 
• We cannot overwrite part of a multi cell array formula 
• They sometime use less memory
• Eliminate the need for intermediate formulas
• We cannot create an array that uses a whole column or multiple columns of cells

Limitations of Array Formulas

• Error prone if we forgot to use Ctrl + Shift+Enter for array formulas 
• Debugging an array formula is difficult
• Array formulas are complex
• Using complex array formulas sometimes slow down the spreadsheet recalculation time

Continue Reading:

How do I recover my Google Account?

Google Slides vs PowerPoint: Which is better?