Get better at working with Google Sheets! Join 50,000 readers to get an actionable tip in your inbox every Monday.
Sheets Tip 320: Bet you didn't know you could do this with pivot tables...
|
Brought to you by:
Welcome to the Google Sheets Tips newsletter #320, your Monday morning espresso, in spreadsheet form! I'm currently experimenting with Google Gemini and ChatGPT to write my Google Sheets formulas and do data analysis. They do a much better job now than they did even just 6 months ago. The way we work with spreadsheets will change fundamentally in the next couple of years. I'll share results from these experiments with Sheets Insiders members in Thursday's email. Meanwhile, in today's Google Sheets Tips email, I have another amazing pivot table trick for you. Like last week, it's not well known because it's hidden in the right-click menu. Check it out below! ➜ NewsI. These are smart chips that pull information from third-party apps into Google Sheets. It's all about reducing the distance ( = friction) between your work apps. As a user of Loom, I'm excited to use a Loom smart chip in my Google Sheet. ➜ Sheets Tip #320:Following last week's tip, when we looked at date grouping in pivot tables, I want to show you another hidden grouping technique in pivot tables. It's incredibly useful. And it's easy to use. Let's start with a pivot table that has text values in the rows, e.g. client names, sales campaigns, exam topics, or, in our example, city names: What if we want to group them into east and west coast buckets? (Perhaps we have two different sales teams.) Step 1Click on the first city in the group, e.g. Boston. Hold down the Ctrl key (or Cmd on a Mac) and click the other east coast cities: Miami, New York, and Washington, DC This highlights all these cities together. Step 2Right click, making sure to keep our cursor over one of the 4 cities we've selected, and select "Create pivot group": This will group the 4 cities into a single custom group Step 3Click on the new group subhead and type in "East Coast" to rename it: which then looks like this: Step 4Highlight the other rows and repeat the steps to create a second custom group for West Coast: Over to you... Get creative with your custom grouping! If you enjoyed this newsletter, please forward it to a friend who might enjoy it. Have a great week! Cheers, P.S. What it feels like trying to get together with friends when you all have young families ;) |
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 the Google Sheets Tips newsletter #328, your Monday morning espresso, in spreadsheet form! This is the last Google Sheets Tips newsletter for 2024. A big thank you for being part of this journey and reading these tips. Your Monday-morning-espressos-in-spreadsheet-form will return on the 6th of January 2025. In the meantime, I wish you all a wonderful holiday season with your loved ones. Tip 328—creating a 2025 calendar with a formula—is found below, but first: ➜ 2024 In...
Brought to you by: Hi Reader, Welcome to the Google Sheets Tips newsletter #327, your Monday morning espresso, in spreadsheet form! Recently a reader asked about a problem importing data from one Sheet to another when the sheet names were changing. Below, we look at how to solve this and see yet another benefit of the new Google Tables feature. My family and I spent the weekend at Fairmont University in West Virginia, where my eldest son was competing in a Lego robotics competition. With his...
Brought to you by: Hi Reader, Welcome to the Google Sheets Tips newsletter #326, your Monday morning espresso, in spreadsheet form! Last week, we looked at the concept of "walking the first row" to understand your data. This week, we're pairing that with the Column Stats tool. Using both of these techniques will ensure you have a full understanding of what's in your dataset and a good sense of the metrics (e.g. row count). Check it out below! Also, there is no Google Sheets Tips email next...