> ## Content Index
> Fetch the complete content index at: https://www.jeffsu.org/llms.txt
> Use this file to discover other available public pages before exploring further.

# Google Sheets: The Formula Fix That Updates Itself
- URL: https://www.jeffsu.org/newsletter-233/
- Published: 2026-01-23T13:00:03.000Z
- Updated: 2026-09-03T08:57:46.000Z
- Description: #233
- Author: Jeff
- Tags: #newsletter, Google Sheets

*Want to see these tips early?* [*Subscribe to the newsletter*](https://www.jeffsu.org/newsletter/?utm%5Fsource=newsletter&utm%5Fmedium=post&utm%5Fcampaign=beginning)*!*

---

I'm not proud of this, but back when I was a Product Marketing Manager at Google, I would get extremely frustrated at teammates who made (in my opinion) stupid and avoidable errors.

**Chief among them:** Writing formulas that referenced a fixed range instead of accounting for new data.

## The ~~stupid~~ issue

You write ‘=SUM(F2:F13)’ to total your sales column. Next week, you add three new rows of data. The problem is (obviously) that your formula is still only summing F2:F13\. **The numbers you're seeing are now wrong.**

![](https://embed.filekitcdn.com/e/4QWkasbZfS6CzzToEx4GVD/qYcgwDHX13XzJAcWRgNeQT/email)

The old workaround was using open-ended ranges like ‘=SUM(F:F)’, but this gets messy when you have headers, multiple data sets on one sheet, or need to reference specific columns by meaning rather than letter.

![](https://embed.filekitcdn.com/e/4QWkasbZfS6CzzToEx4GVD/s8hT5Zh8SqcYcXc9FaPMaR/email)

## Dynamic Table references

Google Sheets tables offer a better solution (thanks to Gemini): **Table references.** Instead of ‘=SUM(F2:F13)’, you:

1. Convert the unformatted data into a table (right click on data \*\*\*\*> Convert to table)
2. \=SUM up a range and Google Sheets/Gemini automatically converts the static range into a dynamic range

0:00 

/0:50 

1× 

As you can see in the above video, the formula uses the table name and column header, not cell coordinates.

**The practical benefit:** when you add or remove rows from your table, the formula's range automatically expands or contracts.

### Where Gemini Comes In

When you convert a data range to a table, Gemini now automatically generates a meaningful name based on your data. Instead of "Table1," you might get "Sales\_Pipeline" or "Project\_Tracker." This makes your formulas readable and easy to understand.

💡

****Pro tip:** If you're already using a formula that overlaps with a table range, Sheets will suggest converting it to a table reference automatically. Accept the suggestion and you're done.

Try converting one of your existing spreadsheets to a table this week and let me know how it goes!

---

**Whenever you're ready, here are some other ways I can help you:**

💻 [The Workspace Academy:](https://academy.jeffsu.org/workspace-academy?utm%5Fsource=newsletter&utm%5Fmedium=post&utm%5Fcampaign=footer) Never lose a file, task, or note again with my CORE workflow for Google Workspace. Adopted by over 10,000 Googlers.

✅ [Build Your Command Center in Notion:](https://www.pressplay.cc/link/s/064F24D8?ref=jeffsu.org) Plug-and-play dashboard that surfaces today’s priorities automatically and organizes your life in seconds. #1 rated Notion Course globally.