|
Google Sheets
| #233
Google Sheets: The Formula Fix That Updates Itself
Want to see these tips early? Subscribe to the newsletter!
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.
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.
Dynamic Table references
Google Sheets tables offer a better solution (thanks to Gemini): Table references. Instead of ‘=SUM(F2:F13)’, you:
- Convert the unformatted data into a table (right click on data ****> Convert to table)
- =SUM up a range and Google Sheets/Gemini automatically converts the static range into a dynamic range
0:00
/0:50
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: 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: Plug-and-play dashboard that surfaces today’s priorities automatically and organizes your life in seconds. #1 rated Notion Course globally.