Best Solution of Formulas in Google Sheets disappear when new rows are created.

by

Formulas in Google Sheets can be deleted when new rows are added to the sheet or when new answers are entered through Google Forms. The solution is easy!

Formulas in Google Sheets

Formulas in Google Sheets

An order form created in Google Forms asks customers to provide their full name, number of items, and whether home delivery is required. The final invoice amount is calculated using 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 Sheets owner entered the formula in the Total Amount column for all rows 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 arrive. That’s the default behavior, and even if you keep the column range, the formulas in the cell will be overwritten in the new rows.

How to prevent formulas from being deleted
There are several solutions to this problem.

Use ARRAYFORMULA
Instead of adding formulas to column cells, add an array formula to the first row of the row that contains the values you want.

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

Using MAP with the LAMBDA function
You can use Google Sheets’ new MAP function that takes an array of values as input and returns a new array created by applying a lambda function to each value in the array.

Use the QUERY function
If your array formulas are complex, here’s an alternative approach.

Create a new sheet in Google Sheets and use the QUERY function with a SQL-like statement to insert the required data from the form sheet into the current sheet.

=QUERY('Form Responses 1'!A:D,"SELECT A,B,C,D",TRUE)

We only retrieve the sheet data entered in the form response and all calculations are done in this sheet and not in the parent sheet.

Paste the simple formula for size calculation in cell E2 and drag down to auto-complete the formula for all rows.

=IF(ISNUMBER(C2), SUM(C2*99,IF(D2="Yes",19,0)),)

This is the recommended approach if you want to preserve row formatting and conditional formatting as new responses are added to the survey.

You may also read:

https://www.google.com/sheets/about/

https://notesquiz.com/adventure-road-trips-in-india/

https://notesquiz.com/7-google-photos-tips-your-pics-on-google-pixel-7/