|
Welcome to the Google Sheets Tips newsletter #308, your Monday morning espresso, in spreadsheet form! We're under an "Excessive Heat Watch" in West Virginia this entire week, with temperatures in the mid- to high-90's. Yuck! It's probably my least favorite type of weather. I'm an Englishman who grew up with summer temperatures between 15 and 20°C (i.e. in the 60's) with a side of rain! ;) A big thank you to this week's sponsors who help keep this newsletter free to read: ➜ Google Sheets Tip #308: Formulas and Google Sheets TablesTwo weeks ago we looked at the basics of the new Tables features. And then last week we looked at the data analysis tools built into Tables. This week, we're looking at how formulas work with Tables. This 3-part guide is also available on my website here. Adding formulas to TablesIf you add a formula to the first row of a Table, you're prompted to fill the entire column with Suggested Autofill: (Note: This is the same behavior as regular non-Table formatted data.) Table Reference SyntaxTable References are a special way to access data inside a Table. Instead of A1-style cell references, we can use the Table name and Column headings in our formulas. Let's use this simple Table to illustrate the Table Referencing syntax: I've named this Table Properties. (Note: Table names can only contain letters and numbers and can't start with a digit or have a space.) Here's how the Table Referencing works:
(Notes: column heading names CAN contain spaces, but must be unique within that Table.) It's easier to understand visually and I've added formulas to show how they work: If you're familiar with Named Ranges, then you'll feel right at home using Table referencing. (Note: you can still access cells inside Tables using regular cell references, e.g. =A1.) Benefits of using Table ReferencesThere are a number of benefits to using Table References over standard A1-style cell references.
Using Table References in FormulasNow we understand structured table references, let’s see them in action with a practical example. Let’s say we have a Table of real estate transactions. We call it RealEstate2024 (remember, no spaces allowed in Table names). Now suppose we want to lookup a client name from that Table to retrieve transaction details. To do that, we'll use an XLOOKUP formula. With our a client name (the search term) in cell G2 and using Table References, our formula is: =XLOOKUP( G2, RealEstate2024[Client] , RealEstate2024[Sales price] ) RealEstate2024[Client] refers to the "Client" column inside the "RealEstate2024" Table. Similarly, RealEstate2024[Sales price] refers to the "Sales price" column inside that same table. In our Sheet, it might look like this: Here's the equivalent A1-style formula: =XLOOKUP( G2 , 'Copy of Sheet4'!D2:D21 , 'Copy of Sheet4'!E2:E21 ) The Table Reference version is cleaner and easier to understand. Plus, any new rows of data added to the Table will be automatically included by the formula. Whereas, with the A1-style formula, we'll need to remember to update the range references. Now, I'm not about to tell you that you can throw away your A1-style referencing! Tables References are super useful and worth using if you have your data in a Table. However, A1-style references are more flexible and still necessary for more complex formulas. _______ Thanks for reading this 3-part guide to Tables. As mentioned earlier in the email, all 3-parts are combined in this blog post on my website. Do you have any questions about Tables? Or any use cases to share? _______ If you enjoyed this newsletter, please forward it to a friend who might enjoy it. Have a great week and stay cool folks! 🥵 Cheers, |
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 the Future Proof newsletter, your Monday morning espresso, in AI form! Today's tip is a simple one, but it's super useful. Scheduled Actions let you put AI to work automatically in the background, handling recurring prompts, research, and daily check-ins on a set schedule. Find out how to create them below. But first, some news from the world of Google Sheets, Workspace, and AI. → News I.Earlier this year, Google launched Sheets Canvas, which lets you turn spreadsheet...
Hi Reader, Welcome to the Future Proof newsletter, your Monday morning espresso, in AI form! (Formerly the Google Sheets Tips newsletter.) One of the stories that jumped out to me in the past week came from an interview with a tearful Cornell mathematics professor, asking "what's the point?" when AI surpasses human ability and steals the meaning (and joy) from mathematical research. (Which is a calling, not just a job to many.) One line stuck with me, and I'm paraphrasing here, but basically,...
Hi Reader, Welcome to the Future Proof newsletter issue 5, your Monday morning espresso, in AI form! (Formerly the Google Sheets Tips newsletter.) Lots of Gemini and Google Workspace news to share today and a tip on custom instructions. Find it all below, but first a little anecdote from my weekend. Kids say the funniest things. My 9-year-old declared over the weekend that he was going to build his own AI company and call his AI tool "Psycho"! I laughed and told him I loved the name. I was...