How to Use the ARRAYFORMULA Function to Copy Formulas in Google Sheets

In this article, we will explore how to use the ARRAYFORMULA function in Google Sheets to quickly apply a formula to an entire column in the spreadsheet. The formula will also automatically be added to new rows.

Working within Google Sheets, you may find yourself in a situation where a formula needs to be copied down to the last row of the sheet. Additionally, you want the formula to be automatically added when a new row is inserted into Google Sheets. There are several straightforward ways to address this issue.

Copying Formulas Down in Google Sheets

The easiest way to copy a formula is by using the fill handle in Google Sheets. Write your formula in the top row of the spreadsheet, then hover your mouse over the bottom-right corner of the formula cell.

The cursor transforms into a fill handle (a black plus sign icon) that you can drag down to the last row of the sheet. The fill handle will not only copy the formula to all adjacent cells but also replicate the visual formatting.

If you need to copy a formula between cells without copying any formatting, select the cell containing the formatting, press Ctrl + C to copy it to the clipboard. Then, choose the range where you want to apply the formula, right-click, select Paste Special, and choose Paste Formula.

Applying Formulas to an Entire Column in Google Sheets

When dealing with hundreds of rows in Google Sheets and the need to apply the same formula to all rows of a specific column arises, a more efficient solution than manual copy-pasting is to utilize Array Formulas.

Select the first cell in the column and input the formula as usual. However, instead of specifying a single cell as a parameter, we will reference the entire column using the notation B2:B (starting from cell B2 and going down to the last row of column B).

Then, press Ctrl + Shift + Enter or Cmd + Shift + Enter on a Mac, and Google Sheets will automatically wrap your formula with the ARRAYFORMULA function.

Thus, we can apply the formula to the entire column of the spreadsheet with just one cell. Array Formulas are more efficient as they handle a range of rows at once and are easier to maintain since you only need to modify a single cell to edit the formula.

One issue you might notice with these formulas is that they apply to every row in the column, including the ones you want to skip, such as empty rows.

This can be addressed by adding an IF statement to our ARRAYFORMULA to skip the formula for any empty rows.

Google Spreadsheet provides two functions to check if a cell is empty:

  • ISBLANK(A1) – Returns TRUE if the referenced cell is empty.
  • LEN(A1) <> 0 – Returns TRUE if the referenced cell is not empty; otherwise, returns FALSE.

So, the modified Array Formulas will look something like this if you use ISBLANK (Cell reference):

There are some other interesting ways to check if a cell is empty:

=ArrayFormula(IF(ISBLANK(B2:B), "", ROUND(B2:B*18%, 2)))

=ArrayFormula(IF(LEN(B2:B)<>0, ROUND(B2:B*18%, 2), ""))

=ArrayFormula(IF(B2:B="", "", ROUND(B2:B*18%, 2)))

Using Array Formulas within Column Headers

In the above examples, the text in the column headers (such as Tax, Total Amount) was pre-filled, and formulas were only added to the first row of the dataset.

We can further enhance our formulas to be applicable to the column headers themselves. If the current row index is 1, calculated using the ROW() function, the formula will output the respective column header instead of performing the computation.

=ArrayFormula(IF(ROW(B:B)=1, "Tax", IF(ISBLANK(B:B), "", ROUND(B:B*18%, 2))))

This revised formula ensures that the computation is skipped for the header row, and instead, the corresponding column header is displayed.

Automatically Populate Formulas in Google Form Responses

ARRAYFORMULA functions prove particularly useful when managing Google Form responses stored in Google Sheets. Direct calculations cannot be performed within Google Forms, but they can be executed within the spreadsheet collecting the responses.

You can create new columns inside Google Sheets and apply ARRAYFORMULA to the first row of the added columns. Upon receiving a new form submission, a new row is appended to Google Sheets, and the formulas are automatically copied and applied to the new rows, eliminating the need for manual copying and pasting of content.

Using VLOOKUP Inside ARRAYFORMULA

You can combine ARRAYFORMULA with VLOOKUP to quickly perform lookups across an entire column.

Suppose you have a sheet “Fruits” listing fruit names in column A and corresponding prices in column B. The second table “Orders” has fruit names in column A, quantities in column B, and you need to calculate the order total in column C.

=ArrayFormula(IF(ROW(A:A)=1, "Total", IF(NOT(ISBLANK(A:A)), VLOOKUP(A:A, Fruits!A2:B6, 2, FALSE) * B:B, "")))

This formula means if the current cell’s row is 1, output the pure text column header. If the row is greater than 1 and column A of the current row is not blank, perform the VLOOKUP function to fetch the item’s price from the “Fruits” sheet. Then multiply that price by the quantity in column B and output the result in column C. If your VLOOKUP range is in another Google Sheets spreadsheet, use the IMPORTRANGE() function with the ID of the different Google Sheets.

I hope through this article, you have learned how to use ARRAYFORMULA in Google Sheets and apply it to copy formulas in both Google Sheets and Google Forms.

Leave a Comment

Your email address will not be published. Required fields are marked *

Shopping Basket
Scroll to Top