What Is Array Formula In Google Sheets?
The Array Formula in Google Sheets, ARRAYFORMULA, is an inbuilt Google function. It enables us to execute one formula in all the cells of the specified data range.
Users can use the ARRAYFORMULA Google Sheets function to perform the same calculation across a massive range in one go. Also, the function is dynamic, as it automatically applies to the new rows added within the given target range.
For instance, the dataset below contains the monthly income and expense data of an employee.
We must compute the employee’s monthly savings in column D cells using the respective income and expense data.
Then, we can use the ARRAYFORMULA() in the first target cell in column D to implement the formula to determine the savings data for the months in all the target cells in one go. This method is in line with the definition of Array Formula in Google Sheets explained earlier.
We must find the difference between the monthly income and the corresponding monthly expense to secure the required monthly savings for the specific month. So, we must subtract the column C values from the respective column B values.
Thus, we shall supply B2:B7-C2:C7 as the cell D2 Array Formula in Google Sheets argument.
So, the function finds the difference between the columns B and C cells in each row and the Array Formula in Google Sheets returns the result in the corresponding column D cell.
Key Takeaways
- The Array Formula in Google Sheets, ARRAYFORMULA, helps us implement one formula in the specified data range in one go.
- The ARRAYFORMULA function in Google Sheets can take an expression or a formula created using inbuilt functions and which returns multiple values as the mandatory argument value.
- The ARRAYFORMULA function in Google Sheets is useful for applying formulas with enhanced flexibility and expandability. Any source data changes or rows of new data inserted into the source dataset will be reflected in the array formula output.
- We can apply the ARRAYFORMULA in Google Sheets with other inbuilt functions, such as IF, VLOOKUP, and FILTER, to yield practical results.
Syntax
The ARRAYFORMULA() syntax for using Array Formula in Google Sheets is the following:
Where the Array Formula In Google Sheets argument definition is as follows:
- array_formula: It is a range containing a mathematical formula utilizing one or multiple ranges of identical size. Otherwise, it is a function returning an output greater than a single cell.
Furthermore, we must supply the argument when using Array Formula in Google Sheets, as it is compulsory.
How To Use Array Formula In Google Sheets?
We can utilize the ARRAYFORMULA function in Google Sheets using the following methods:
- Access the function from the ribbon.
- Enter the function into the sheet manually.
Method #1 – Access The Function From The Ribbon
Click on a cell to show the outcome – The Insert tab – The Function option right arrow – The Google function group right arrow – The ARRAYFORMULA function.
The specific function gets inserted in the selected cell. Next, provide the function argument within the brackets.
Moreover, if required, we can select the ‘?’ icon next to the function to check the syntax of the function Array Formula in Google Sheets explained previously.
Next, selecting the down arrow in the window will show the function’s explanation.
So, after providing the argument, press Enter to view the output, which the Array Formula in Google Sheets returns as the target outcome in the specific range.
Method #2 – Enter The Function Into The Sheet Manually
- Pick the cell where we desire to showcase the output.
- Type =ARRAYFORMULA( in the cell. [ Alternatively, type =A or =AR and choose the function ARRAYFORMULA from the listed options to select the function.]
- Provide the applicable argument value and close the brackets.
- Press Enter to acquire the ARRAYFORMULA() output in the specific data range.
Examples
The examples below will help us use the ARRAYFORMULA function in Google Sheets effectively.
Example #1
The given dataset contains a store’s order data.
We must update the total order costs of the listed products in column E by multiplying the units of the products ordered and their corresponding costs per unit.
Step 1: Select cell E2 and enter the ARRAYFORMULA().
=ARRAYFORMULA(
[Alternatively, we can choose the specific cell and select Insert – Function – Google – ARRAYFORMULA.
We can now view the specific function in the chosen cell, as depicted above.]
Step 2: Provide the ARRAYFORMULA() argument.
=ARRAYFORMULA(C2:C7*D2:D7)
Next, press Enter to view the range of values the ARRAYFORMULA() returns in the required data range E2:E7.
We must compute the product of the units of a product ordered and the product’s cost per unit to determine its total order cost. So, we must multiply the column C values with the respective column D values.
Thus, we provide C2:C7*D2:D7 as the input to the ARRAYFORMULA() in cell E2.
So, the function determines the product of the columns C and D cell values in each row of the data range, and the ARRAYFORMULA() returns the result in the corresponding column E cell.
Example #2 – IF with ARRAYFORMULA
We have a list of employees and their tasks completed data for July 2024.
An employee’s monthly target for completed tasks is 50 or above, and we must update the target achieved status in column C as Yes or No.
Step 1: Choose cell C2 and enter the ARRAYFORMULA().
=ARRAYFORMULA(
Next, nest the IF() within the ARRAYFORMULA().
=ARRAYFORMULA(IF(
After that, enter the IF() arguments.
=ARRAYFORMULA(IF(B2:B7>=50,”Yes”,”No”
Step 2: Close the IF() bracket.
=ARRAYFORMULA(IF(B2:B7>=50,”Yes”,”No”)
Next, close the ARRAYFORMULA() bracket.
=ARRAYFORMULA(IF(B2:B7>=50,”Yes”,”No”))
Step 3: Press Enter to view the IF() execute as an array formula and return the output in the range C2:C7.
The IF() condition checks if the number of tasks completed by an employee in column B cell is 50 or more. If the condition holds, the function returns the True value, Yes. Otherwise, the function output is the False value, No.
Furthermore, as we nest the IF() within the ARRAYFORMULA(), the IF() checks the condition in all the cells in the range B2:B7. Accordingly, it then returns the respective return values in the corresponding column C target cells in one go.
Example #3 – VLOOKUP with ARRAYFORMULA
The dataset below shows the revenues generated by sales executives at a firm from Jan-Mar.
The task is to update the revenue generated by the employee, cited in cell F2, in the three months, based on the source dataset. Consider cells G2:I2 as the target cells.
Step 1: Select cell G2 and enter the ARRAYFORMULA().
=ARRAYFORMULA(
Next, nest the VLOOKUP() within the ARRAYFORMULA().
=ARRAYFORMULA(VLOOKUP(
Next, enter the VLOOKUP() argument values.
=ARRAYFORMULA(VLOOKUP(F2,$A$2:$D$11,{2,3,4},0
Step 2: Close the VLOOKUP() bracket.
=ARRAYFORMULA(VLOOKUP(F2,$A$2:$D$11,{2,3,4},0)
Next, close the ARRAYFORMULA() bracket.
=ARRAYFORMULA(VLOOKUP(F2,$A$2:$D$11,{2,3,4},0))
Step 3: Press Enter to implement the VLOOKUP() as an array formula and achieve the outcome in the range G2:I2.
The VLOOKUP() must return multiple values. So, we will supply multiple column numbers within curly braces as the index argument value, as mentioned below:
{2,3,4}
It builds an array to return multiple column values, which are the columns 2, 3, and 4 values of the search range. Also, we use curly braces to secure an array within the VLOOKUP function.
Next, the ARRAYFORMULA() executes the VLOOKUP() as an array formula.
The VLOOKUP() looks for the cell F2 value, Jonathan Thompson, in the leftmost column of the range A2:D11. It locates a perfect match for the search key in cell A6, which is in row 5 of the search range. Next, the index in the VLOOKUP() is an array of column indices 2, 3, and 4. So, the function returns the values in row 5 of columns B, C, and D in the range A2:D11, which are the cells B6, C6, and D6 values, $5,900, $6,200, and $4,100.
Example 4 – FILTER with ARRAYFORMULA
The primary dataset lists a set of stationery items and their units sold data.
We need to list the stationery items in column D whose units sold counts are over 100. Also, we must determine the revenues for such items and display them in column E, with the cost per unit being $20.
Step 1: Select cell D2 and enter the FILTER().
=FILTER(A2:A7,B2:B7>100)
Next, press Enter to implement the FILTER().
The FILTER() accepts two inputs.
The first argument is the range A2:A7, from where the function must return the output based on the condition B2:B7>100, cited as the second argument value. The condition is to check if the units of the stationery items sold, cited in the range B2:B7, are over 100.
The condition holds in cells B3, B4, and B6. So, the function returns an array of values in the same rows of column A, which are the cells A3, A4, and A6 values, A4 Sheets, Folder, and Pen. We can view the output in the column D cells.
Step 2: Select cell E2 and enter the ARRAYFORMULA().
=ARRAYFORMULA(
Next, nest the FILTER() within the ARRAYFORMULA().
=ARRAYFORMULA(FILTER(
After that, supply the FILTER() inputs, close the bracket, and multiply the function output with the value of 20.
=ARRAYFORMULA(FILTER(B2:B7,B2:B7>100)*20
Step 3: Close the FILTER() and ARRAYFORMULA() brackets.
=ARRAYFORMULA(FILTER(B2:B7,B2:B7>100)*20)
The FILTER()‘s first argument is the range B2:B7, from where the function must return the output based on the condition B2:B7>100, provided as the second argument value. The condition is to check if the units of the stationery items sold, specified in the range B2:B7, are over 100.
The condition is true in cells B3, B4, and B6. So, the function returns an array of values, which are the values in the same cells B3, B4, and B6, 200, 150, and 250.
Next, the ARRAYFORMULA() multiplies $20 with the array of values we obtain by applying the FILTER(). Thus, we achieve an array of values in the range E2:E4, $4,000, $3,000, and $5000, which are the required revenues based on the units of stationery items sold over 100.
Please note that the FILTER() output will be an array. However, when we multiply the value of 20 with the function output, we will secure the output only in cell E2 (revenue of A4 Sheets) instead of an array of values. Thus, we nest the FILTER() within the ARRAYFORMULA() when applying it in the column E cells.
Important Things To Note
- Most of the array formulas automatically expand into neighboring cells, precluding the obvious use of the Array Formula in Google Sheets, ARRAYFORMULA.
- When we press Ctrl + Shift + Enter while implementing a formula in Google Sheets, the action will automatically append ARRAYFORMULA( at the start of the formula.
- We cannot export array formulas in Google Sheets.
Frequently Asked Questions (FAQs)
We can use SUMIF with ARRAYFORMULA in Google Sheets using the following method, described with the help of an example.
The primary dataset shows a set of fruits, their grades and stocks.
The aim is to evaluate the total stock of fruits based on their grades and showcase the results in column F cells.
Step 1: Choose cell F2 and enter the ARRAYFORMULA(), nesting the SUMIF().
=ARRAYFORMULA(SUMIF(B2:B12,E2:E4,C2:C12))
Step 2: Press Enter to view the array of values the formula returns in the range F2:F4.
The SUMIF() checks for the grades, cited in the range E2:E4, in the range B2:B12. Once it finds the matches for the respective grades in the range B2:B12, it adds the stock values in the corresponding rows of the range C2:C12.
For instance, the function finds the match for grade A in cells B2, B4, B8, and B9. So, it sums the cell values C2, C4, C8, and C9 to return 361 as the total stock units of grade A fruits.
Furthermore, since we nest the SUMIF() within the ARRAYFORMULA(), the function executes as an array formula. So, it returns the total stock units of grades A, B, and C fruits in one go as an array of values in the range F2:F4.
We can combine columns using Array Formula in Google Sheets using the following formula syntax:
=ARRAYFORMULA(data_range_col1&” “&data_range_col2)
The data_range_col1 and data_range_col2 are the two ranges holding the values of the two columns we aim to combine.
Likewise, we can combine more columns by adding the respective ranges in the above formula, separated by ‘&” “&’. Also, we can use other characters instead of the space character according to our requirements.
We can combine columns using Array Formula in Google Sheets using the following formula syntax:
=ARRAYFORMULA(data_range_col1&” “&data_range_col2)
The data_range_col1 and data_range_col2 are the two ranges holding the values of the two columns we aim to combine.
Likewise, we can combine more columns by adding the respective ranges in the above formula, separated by ‘&” “&’. Also, we can use other characters instead of the space character according to our requirements.
Download Template
This article must be helpful to understand the Array Formula In Google Sheets, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is Array Formula In Google Sheets. We learn its syntax and how to use it in Google Sheets with detailed examples. You can learn more from the following articles –
Leave a Reply