I'd like to set a function for an entire column in Google Spreadsheets, and I'd like for that function to get applied whenever I add new rows to the spreadsheet.

I know that I can set a function for a cell in that column, and then drag the cell's handle down through the entire column to autofill the function into the other cells. However, that process is slow for spreadsheets with many rows, and it's error-prone to need to remember to do it every time I add new rows.

Is there a way to just say "the function for column X is Y, please assign the function to any new cells in X"?

  • If using a script is an option, you can key off of the OnEdit event of the worksheet and go from there to verify you're on the correct sheet, in the column you want to monitor, and do whatever calculations you want to the data/formatting. I can post an example if a script is an option.
    – panhandel
    Oct 15, 2013 at 17:35

3 Answers 3


You can use ARRAYFORMULA to do this. Then you reference the column as eg B1:B ( note that there is no row number for the second part).

So if you want every cell in column A to be double of column B, you would put this in cell A1...


You might want to throw in an if statement to check for non numeric values as well.

  • Magic! That worked!
    – emmby
    Oct 16, 2013 at 1:32

To add to Tom Horwood's answer, you will definitely want to add an IF statement otherwise your entire column will have 0's or some invalid result displayed.

So to do this, I thought I would share the formula, here you go:

= ARRAYFORMULA (if(B3:B<>"",ARRAYFORMULA (B3:B * 2),""))

So basically this formula first checks whether or not the cell in column B is empty or not. If not, then it will do the multiplication rule. You can modify this formula to match your needs.


Very simple way via Copy & Paste:

  1. Copy the formula you want to using Ctrl+C
  2. Select the entire column by clicking on the column header
  3. Paste the formula to all cells in that column using Ctrl+V
  4. Replace header title

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.