You’re working in a Google spreadsheet where a formula needs to be copied to the last row of the sheet. Additionally, you want the formula to be added automatically when a new row is added to the Google sheet.
There are several ways to solve this problem.
Using the Copy Handle in Google Sheets
The simplest method to copy formulas is by using the copy handle in Google Sheets. Write your formula in the first row of your spreadsheet, then hover your mouse over the bottom right corner of the cell containing the formula.
The cursor transforms into a copy handle (a black plus symbol) that you can drag down to the last row of the sheet. The copy handle not only copies the formulas to all adjacent cells but also copies the visual formatting.
If you need to copy formulas to multiple cells without any formatting, select the cell with the formatting, press Ctrl+C to copy it to the clipboard. Then, select the range where the formula needs to be applied, right-click, choose “Paste Special,” and select “Paste Formula Only.”
Applying a Formula to an Entire Column in Google Sheets
If you have hundreds of rows in a Google spreadsheet and want to apply the same formula to all rows in a specific column, there is a more efficient solution than copy-pasting: matrix formulas.
Select the first cell in the column and enter the formula as before. However, instead of specifying a single cell as a parameter, we will specify the entire column using the notation B2:B (from cell B2 to the last row in column B).
Then, press Ctrl+Shift+Enter, or Cmd+Shift+Enter on Mac, and Google Sheets will automatically wrap your formula with the ARRAYFORMULA function.
This way, we can apply the formula to the entire column of the spreadsheet using just one cell. Matrix formulas are more efficient as they process a group of rows in one go. They are also easier to maintain since you only need to modify one cell to change the formula.
A problem you may have noticed with the previous formulas is that they apply to every row in the column, whereas you only want to add formulas to rows with data and skip empty rows.
This can be solved by adding an IF condition to our ARRAYFORMULA so that it doesn’t apply the formula to empty rows.
Google Sheets provides two functions to test whether a cell is empty or not.
- ISBLANK(A1) – Returns TRUE if the referenced cell is empty.
- LEN(A1) <> 0 – Returns TRUE if the referenced cell is not empty, otherwise FALSE.
Our modified matrix formulas would be:
=ARRAYFORMULA(IF(ISBLANK(A1:A), "", [your formula here]))There are several other ways to test whether a cell is empty or not.
Using Matrix Formulas in Column Headers
In our previous examples, the text of the column headers (like “Tax”, “Total Amount”) was pre-filled, and the formulas were only added to the first row of the data set.
We can enhance our formulas to be applied to the column header itself. If the current row index is 1, calculated using the ROW() function, the formula displays the column header, otherwise, it performs the calculation using the formula.
Auto-Filling Formulas in Google Forms Submissions
ARRAYFORMULA functions are particularly useful for Google Forms when form responses are saved in a Google spreadsheet. You can’t perform calculations directly in Google Forms, but they can be done in the spreadsheet that collects the responses.
You can create new columns in the Google spreadsheet and apply the ARRAYFORMULA function to the first row of the added columns.
When a new form submission is received, a new row is added to the Google sheet, and the formulas are cloned and automatically applied to the new rows without having to copy-paste anything.
Using VLOOKUP in ARRAYFORMULA
You can combine the ARRAYFORMULA function with VLOOKUP to quickly search an entire column.
Let’s say you have a “Fruits” sheet that lists fruit names in column A and their corresponding prices in column B. The second “Orders” sheet contains fruit names in column A, quantity in column B, and you need to calculate the order amount in column C.
Simply put, if the current cell’s row is 1, display the column header as plain text. If the row is greater than 1 and the column A of the current row is not empty, perform a VLOOKUP search to retrieve the item price from the “Fruits” sheet. Then, multiply that price by the quantity in cell B and display the value in cell C.
If your VLOOKUP range is in another Google spreadsheet, use the IMPORTRANGE() function with the ID of the other Google sheet.
Please note that you may need to use semicolons in spreadsheet formulas instead of commas for certain locales.
That’s all for today! I hope these tips will help you copy your formulas in Google Sheets. To learn more about spreadsheets and other tips, visit Crawlan.com.
See you soon for more tips!
