4

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 '13 at 17:35
9

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.

  • Magic! That worked! – emmby Oct 16 '13 at 1:32
1

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.

  • To do the calculations only for non empty rows, instead of IF() it's better to use FILTER(). – Rubén Feb 9 '16 at 6:14
  • Hi Ruben, I haven't used that before. Can you explain how you would accomplish the same as I have listed above using that function? – americanninja Feb 9 '16 at 13:26
  • See webapps.stackexchange.com/a/79958/88163 (it's one example of many in Web Applications) – Rubén Feb 9 '16 at 15:12
0

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, privacy policy and cookie policy

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