Formulas in Google Sheets Disappear When New Rows Are Added - The Solution
The formulas in Google Sheets may get deleted when new rows are added in the sheet or when new responses come in through Google Forms. The fix is simple!
An order form, created in Google Forms, requires customers to provide their full name, the item quantity and whether home delivery is required. The final bill amount is calculated with a simple formula in Google Sheets.
// Item cost is $99 per unit. Delivery cost is $19. =IF(ISNUMBER(C2), SUM(C2*99, IF(D2="Yes", 19, 0)), )
The Google Sheet owner has entered the formula across all rows in the
Total Amount column so that the value is automatically calculated when a new form response is submitted.
The problem is that the formulas in Google Sheets are automatically deleted when new responses come in. That’s the default behavior and even if you protect the column range, the formulas in the cell will be erased on new rows.
There are several workarounds to this problem.
Instead of adding formulas inside individual cells of the column, add an Array Formula to the first row of the column that contains the computed values.
=ARRAYFORMULA(IF(ROW(C:C)=1, "Total Amount", IF(ISNUMBER(C:C), C:C*99 + IF(D:D="Yes",19,0),) ))
Here’s a simple breakdown of the formula:
IF(ROW(C:C)=1, "Total Amount", ...- If the current row number is 1, add the column title.
IF(ISNUMBER(C:C), ...- Calculate the amount only if there’s a numeric value in the C column.
C:C*99 + IF(D:D="Yes",19,0),)- Multiply $99 with the item quantity and add $19 if the column D is set to Yes.
You can use the new MAP function of Google Sheets that takes an array of values as input and returns a new array formed by applying a Lambda function to each value of the array.
=MAP(C:C,D:D, LAMBDA(Qty, Delivery, IF(ROW(Qty)=1,"Total Amount", IF(ISNUMBER(Qty), Qty*99 + IF(Delivery="Yes", 19,),)) ))
If array formulas sound complex, here’s an alternate approach.
Create a new sheet in your Google Spreadsheet and use the
QUERY function with a SQL-like statement to import the required data from the Form sheet into the current sheet.
=QUERY('Form Responses 1'!A:D,"SELECT A,B,C,D",TRUE)
We are only importing the sheet data that has been entered in the form response and all the calculations will happen in this sheet, not the main sheet.
Paste the simple formula for amount calculation in cell E2 and drag the cross-hair down to auto-fill the formula across all rows.
This is the recommended approach if you would like to preserve row formatting and conditional formatting when new survey responses come in.
Google Developer Expert, Google Cloud Champion
Amit Agarwal is a Google Developer Expert in Google Workspace and Google Apps Script. He holds an engineering degree in Computer Science (I.I.T.) and is the first professional blogger in India.