Google Sheets: automatically apply formula to new rows added at the top

12
votes

I have a simple question for which I have failed to find an answer, simple or otherwise!

My Spreadsheet looks like this (say)

   A       B      C
1  Name  Amount  Tax 
2  Neil    20      2
3  Rose    100     10

Now column C is calculated via a formula B/10. I want to insert a new row ABOVE ROW 2 (not at the end) and I want the formula to apply to that new row in column C.

I cannot find a way to do this automatically. I know how to

  • manually copy the formula (but of course there are many columns I actually want to do this to) by copy and paste or grabbing the handle
  • use arrayformula to extend the formula. The problem I have is that if I use arrayformula like this: arrayformula(B2:B/10) that's great if I add rows at the end. But when I insert a row, becoming the new row 2, what happens is that the formula remains linked with the original cell I entered and changes to arrayformula(B3:B/10) which is logical but not what I want because the new row 2 has no formula associated with it
  • if I use arrayformula(B:B/10) then it applies to all rows including row 1 (the column headers) and gets very confused about position.

There must be a way, so I ask you, the internet for your assistance :-)

2
google-sheets

2 Answers

14
votes

An ARRAYFORMULA actually will work in this situation if you place it in cell C1 as follows:

=ARRAYFORMULA(IF(ROW(B:B)=1, "Tax", IF(ISBLANK(B:B), "", B:B/10)))
  1. If the current row is the first, then make the value of the cell "Tax".
  2. Else if it is blank, then leave it blank.
  3. Otherwise attempt the tax calculation using B:B/10.
3
votes

Here's a way to use Apps Script to insert a new row on top and copy the formula to the new row. It may take some customization depending on how complex your data is, but hopefully you get the idea.

// global 
var ss = SpreadsheetApp.getActive();


function addFirstRow() {
    var firstRow = 2;
    var sh = ss.getActiveSheet();
    var lCol = sh.getLastColumn();
    var range = sh.getRange(firstRow, 3, 1, lCol);
    var formulas = range.getFormulas();
    sh.insertRowsAfter(1, 1);
    newRange = sh.getRange(firstRow, 3, 1, lCol);
    newRange.setFormulas(formulas);
}