A formula you drag down next to Form responses stops working on new submissions because it only exists in the rows you filled. The fix is to use ARRAYFORMULA in the header row of your extra column, so one formula covers every row, including new ones, or to build your formulas on a separate clean tab that reads the responses.
Why do my formulas not fill new rows?
Google Forms adds each response as a new row at the bottom of the responses tab. It fills only the columns it owns, the ones that match your questions. Any extra column you added holds a formula in each existing row and nothing in the new one. In a sheet with a weekday column, rows 2 to 40 may show Mon, Tue and so on, while row 41, a new submission, stays blank.
Dragging the formula down again only fixes the rows that exist today. The next response has the same gap.
How do I fix it with ARRAYFORMULA in the header row?
Put one formula in the header cell of your helper column. It writes the header text and then fills the whole column. Say the form's date is in column B, the amount in D and the payment mode in E, with responses from row 2. In G1, type:
- ={"Month"; ARRAYFORMULA(IF($B$2:$B500="", "", TEXT($B$2:$B500, "mmm yyyy")))} to show Dec 2026 style labels.
- In H1: ={"Is cash"; ARRAYFORMULA(IF($E$2:$E500="", "", IF($E$2:$E500="Cash", "Yes", "No")))} to flag cash entries.
The curly brackets stack the header text above the array. As new responses land in rows 41, 42 and onward, the formula already covers them, up to row 500. Raise 500 when the sheet gets near it.
Is a separate clean tab safer?
For anything you care about, yes. The responses tab belongs to the form, and its structure can change when you edit the form. A clean tab only reads from it, so you can lay out your own columns without fear.
- Add a tab called Clean.
- In A1:E1 type your own headers, such as Date, Category, Amount, Mode and Month.
- In A2 put =ARRAYFORMULA('Form responses 1'!B2:B500), then the same style for the category, amount and mode columns, matching the column letters in your responses tab.
- In E2 put =ARRAYFORMULA(IF(A2:A500="", "", TEXT(A2:A500, "mmm yyyy"))).
- Build all totals and charts on Clean.
The tab name has to match yours exactly, including the number at its end. Because Clean has its own headers, you can reorder, rename and hide columns without touching what the form writes.
What can still go wrong?
| Problem | Cause | What to do |
|---|---|---|
| New question shifts columns | Adding a question to the form adds a column in the responses tab | Check the column letters in your formulas after any form edit |
| Dates show as numbers | Cell format on the helper column is plain | Select the column and apply a date format |
| Blank rows show zero | A formula returns 0 for an empty source cell | Wrap in IF(source="", "", ...) as in the examples |
| Formula count grows slow | Very large ranges in many array formulas | Use a fixed range, not whole columns, and trim unused rows |
Should I use QUERY or pivot tables instead?
Both can sit on the Clean tab and work well once the data is in a tidy block. A pivot table groups by category and month without any formulas, and it refreshes by itself when the source grows. Learn the array approach first, since it teaches you why the rows are not filled.
Checklist for formula-safe responses
- Do not type or paste into the responses tab beside the form columns, apart from the header-row array formulas.
- Use ARRAYFORMULA in row 1, or a Clean tab.
- Pick a range limit well above the number of rows you expect.
- Recheck column letters after editing the form.
- Keep a copy of the sheet before large changes, using File > Make a copy.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

