Google Sheets Formulas Disappearing: Use ArrayFormula Fix

When you rely on Google Sheets to process data from a Google Form, you quickly discover a frustrating glitch: formulas that you painstakingly built can vanish the moment a new row is inserted or a fresh form response arrives. This issue not only disrupts calculations but also forces you to constantly re‑apply formulas, wasting time and increasing the risk of errors. The good news is that the problem has a straightforward fix that works for any sheet, whether you’re handling simple sums or complex array formulas. In this article we’ll explore why formulas disappear, examine the underlying mechanics of Google Sheets, and walk you through a reliable solution that keeps your formulas intact no matter how many rows you add.

Why Formulas Disappear When New Rows Are Added

Google Sheets treats each row as an independent entity. When a new row is inserted—either manually or via a Form submission—the platform copies only the formatting of the surrounding rows, not the underlying formulas. This behavior stems from the default “Insert row below” action, which does not extend the formula range automatically. As a result, any calculation that relied on a static range (e.g., A2:A10) stops working because the new data falls outside the defined boundaries, and the cell that should contain the formula remains empty.

Using Array Formulas to Preserve Logic Across Dynamic Ranges

One of the most powerful tools in Google Sheets is the ARRAYFORMULA function. Unlike regular formulas that evaluate a single cell, an array formula can operate over an entire column, automatically expanding as new rows appear. By placing a single ARRAYFORMULA at the top of a column, you create a self‑adjusting calculation that never needs to be copied down. This approach eliminates the need for manual dragging and ensures that every new entry is immediately included in the computation.

Step‑by‑Step Fix: Converting Standard Formulas to Array Formulas

• Identify the original formula. Locate the cell range that currently contains the static formula (e.g., B2:B10 = C2*C3).
• Rewrite using ARRAYFORMULA. Replace the range with a column reference and wrap the expression in ARRAYFORMULA(). For example: =ARRAYFORMULA(C2:C * D2:D).
• Place the formula in the header row. Insert the new array formula in the first row of the target column (usually row 1). This ensures the calculation starts at the top and propagates downward.
• Test with a new form submission. Submit a dummy response to the linked Google Form and verify that the result appears instantly without any manual copying.
• Remove old static formulas. Once the array formula works, delete the previous individual formulas to avoid duplication.

Alternative Solutions: Protecting Ranges and Using Apps Script

If an array formula isn’t suitable—for instance, when you need different logic per row—there are two additional tactics:

• Protect the column. By locking the column that contains the formula (Data → Protected sheets and ranges), you prevent accidental deletion when new rows are added. Users can still input data in adjacent columns, while the protected formula stays intact.
• Automate with Apps Script. A short script can detect row insertions and automatically copy the formula down. Example snippet:

  function onFormSubmit(e) {
    var sheet = e.range.getSheet();
    var lastRow = sheet.getLastRow();
    var formula = '=C' + lastRow + '*D' + lastRow;
    sheet.getRange('E' + lastRow).setFormula(formula);
  }
  

  This script runs every time a form response is recorded, ensuring the new row receives the correct calculation.

Best Practices for Future‑Proof Sheets

To keep your spreadsheets resilient, adopt these habits:

• Design with dynamic ranges. Prefer ARRAYFORMULA or functions like FILTER and QUERY that naturally handle expanding data sets.
• Document your logic. Add brief comments in the header row (using Insert → Note) so collaborators understand why a column uses an array formula or a script.
• Regularly audit protected ranges. Ensure that only the necessary cells are locked, avoiding overly restrictive settings that hinder data entry.
• Test after each major change. Submit a test form entry after adding new formulas or scripts to confirm everything still works as expected.

Conclusion

Formulas disappearing in Google Sheets when new rows are added is a common annoyance, but it’s easily solved by embracing dynamic solutions. By converting static calculations into ARRAYFORMULA constructs, protecting critical columns, or deploying a simple Apps Script, you guarantee that every new piece of data is automatically accounted for. These techniques not only save time but also reduce the likelihood of human error, keeping your spreadsheets accurate and reliable. Implement the steps outlined above, adopt the best‑practice habits, and you’ll enjoy a seamless workflow where formulas stay put, regardless of how many rows your Google Form generates.