Create a function for an entire column in Google Spreadsheets

Question

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"?

Answer 1

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...

= ARRAYFORMULA (B1:B * 2)

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

Answer 2

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.

Answer 3

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