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)), )

Google Sheets Formula

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.

How to Prevent Formulas from Deleting

There are several workarounds to this problem.

Use an ARRAYFORMULA

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.

Use MAP with a LAMBDA function

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.

LAMBDA function

=MAP(C:C,D:D,
  LAMBDA(Qty, Delivery,
    IF(ROW(Qty)=1,"Total Amount",
    IF(ISNUMBER(Qty), Qty*99 + IF(Delivery="Yes", 19,),))
  ))

Use a QUERY function

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.

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

Query Function for Google Sheets

This is the recommended approach if you would like to preserve row formatting and conditional formatting when new survey responses come in.

Amit Agarwal

Amit Agarwal

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.

Amit has developed several popular Google add-ons including Mail Merge for Gmail and Document Studio. Read more on Lifehacker and YourStory

0

Awards & Titles

Digital Inspiration has won several awards since it's launch in 2004.

Google Developer Expert

Google Developer Expert

Google awarded us the Google Developer Expert award recogizing our work in Google Workspace.

ProductHunt Golden Kitty

ProductHunt Golden Kitty

Our Gmail tool won the Lifehack of the Year award at ProductHunt Golden Kitty Awards in 2017.

Microsoft MVP Alumni

Microsoft MVP Alumni

Microsoft awarded us the Most Valuable Professional (MVP) title for 5 years in a row.

Google Cloud Champion

Google Cloud Champion

Google awarded us the Champion Innovator title recognizing our technical skill and expertise.

Email Newsletter

Sign up for our email newsletter to stay up to date.

We will never send any spam emails. Promise.