Friday, 28 September 2018

How to Use Array Formula in Google Sheet

An array is a table containing values. An array formula used to perform multiple calculations on “n” number of data or items in an array. Google sheet array formula also performing multiple calculations in rows and columns. 

Usage of array formula helps us to ensure accuracy and safety of data in our operations. Hence it reduces the risk of error or overwrites in the google sheet. 

So now let's know how this function works in Google sheet.



Here the 'array_formula' may be 
  • a range
  • a mathematical expression using one cell range or multiple ranges of the same size, or 
  • a function that returns a result greater than one cell

Google sheet Array with a Simple Example

Created an array below shows five products, its cost, price and quantity

To find the profit of the product(Price-Cost* Qty), What we will do?

Normally we will enter the equation in the array as shown below. But this equation is applied to only one cell in the array(profit). If we want to find the profit for all the products, we need to just drag the equation to down to all the cells corresponding to the products.

Array Formula in Google Sheet

Array Formula in Google sheet

Refer the picture below, here you can see how to use the array formula in google sheet. 

Array Formula in Google Sheet

In "profit" column, we have to enter the formula as  


then press the Enter Key. 

When we complete this process, all the rows in the "Profit " column will be filled with the amount of profit as shown below.

Array Formula in Google Sheet

If you want to get updated the "profit" column whenever you enter new product details in the sheet, you just alter the formula from the first cell in the "profit" column as shown below.


But in the above table, we can see "Zero" in the "profit" column as we did not enter the corresponding the product data. To avoid showing the zero in the "Profit" column, we have to change the formula with 'IF function' as below.


This formula will help the Google sheet to clear the "zero" in the cells where we applied our formula as shown below and the profit will be calculated whenever you enter the new product details in the sheet.

Selection of Unlimited Rows

Array formula in Google sheet

You may notice that the formula contained C4:C, D4:D,E4:E. All these are representing columns of  unlimited rows. Once you enter the C4:C, the google sheet selects the entire rows in the column 'C' from the fourth cell onwards(C4). Please refer the table to the right

Array Formula with Ctrl+Shift+Enter

Once you enter the formula in a single cell as given below, then press Ctrl+Shift+Enter keys. It will automatically create an array formula 

This is a very simple example used to explain the basic concept of the array formula in the google sheet. If you want to know more about array formula, please click here

Please comment and share it with social media and your friends.

Qatar Airways