Get better at working with Google Sheets! Join 50,000 readers to get an actionable tip in your inbox every Monday.
Sheets Tip 370: Upgrade your Tables with this quick trick
|
Brought to you by:
Welcome to the Google Sheets Tips newsletter #370, your Monday morning espresso, in spreadsheet form! This is the last Google Sheets Tips email for 2025. We're taking a break from publishing for the holidays and will return with Tip 371 on 5th January 2026. Thank you so much for reading and replying to these emails. I love learning from all of you when you write back and share your own tips and tricks (and occasional corrections! 😉 ). In the meantime, I wish you all a Happy Holidays! 🎅 🎄 🎁 ☃️ ➜ Sheets Tip #370: Upgrade your Tables with this quick trickIf you've been reading these emails or attended one of my recent webinars, you'll know that I'm a strong advocate of using Tables. They have many built-in benefits such as data validation, column types, named ranges, etc. One of those benefits is the ability to add a footer to our Tables with a single click. And, if we set the column type to one of the numeric data types (e.g. Currency), the footer will automatically add a SUM function to our data. Add a footer from the Table menu (next to the Table name): In our Sheet, it looks like this: This is great! But, but, but... (There is always a but.) If we apply any filters to our data, then that SUM total will be incorrect. Consider this example, where we apply a filter to show only the vegetables: The SUM function still shows the total value of all the items ($40.70) and not the total of only the vegetables ($12.10). Hmm? That's a problem... The solution is to switch the SUM function to a SUBTOTAL function, which only includes the visible rows in the aggregation calculation. Delete the SUM function and type this one instead: =SUBTOTAL( 9 , Table1[Expense] ) where the number 9 specifies a SUM calculation. Other options include 1 = Average, 4 = Max, 5 = Min (see here for a full list). "Table1[Expense]" refers to the Expense column of Table1. In our Sheet, the total now displays the correct value: If you enjoyed this newsletter, please forward it to a friend who might enjoy it. Have a great week! Cheers, |
Google Sheets Tips
Get better at working with Google Sheets! Join 50,000 readers to get an actionable tip in your inbox every Monday.
Hi Reader, Welcome to Future Proof #4 (formerly the Google Sheets Tips newsletter), your Monday morning espresso, in AI form! My son and I had some fun 3-D printing over the weekend. I printed a rugged case to hold my Field Notes journals (see the finished result at the end of this email). The print run just beginning. It's impressive to watch something grow from nothing, although since the full print took around 3 hours I didn't watch the whole thing ;) And now, perhaps more than ever...
Hi Reader, Welcome to the Future Proof #3 (formerly the Google Sheets Tips newsletter), your Monday morning espresso, in AI form! Here in the Mid-Atlantic region, fall has arrived with cooler temperatures, blustery winds, and grey, rainy days. (As a Brit, it makes me feel right at home!) That means it's officially the end of the shorts-as-the-default-choice season. And, there's a riot of color coming our way over the next few weeks as the maple trees put on a show 🍁. Today, I'm sharing a tip...
Hi Reader, Welcome to the Future Proof #2 (formerly Google Sheets Tips), your Monday morning espresso, in AI form! Sorry this second issue is a day late. I was really sick at the end of last week and over the weekend, so even though the newsletter was largely written, I wasn't well enough to write this intro and press send yesterday. So, here it is. Thanks for all the kind responses last week when I introduced the new format. It seems like the majority of you understand the pivot and are...