Get better at working with Google Sheets! Join 50,000 readers to get an actionable tip in your inbox every Monday.
Which function will you choose today?
|
Welcome to the Google Sheets Tips newsletter #294, your Monday morning espresso, in spreadsheet form! A big thank you to this week's sponsors who help keep this newsletter free to read: ➜ New videos on YouTubeI was a guest on last week's episode of Totally Unscripted with Alice Keeler, Martin Hawksey, Charles Maxson (from Google), and Kara Ireland (from Google). We chatted about Google Sheets and spreadsheets' persistence, and I demoed the powerful LET and NAMED functions. _______ ➜ NewsI. Read more about preset dropdown chips here _______ ➜ Google Sheets Tip #294:In this tip, we look at a hidden use for the SUBTOTAL function. Typically, it's used to... well, err, create subtotals of data, without double-counting. (See this post for a detailed explanation.) But today I want to show you another use case for the SUBTOTAL function. It can do more than sum your data. It has 10 other data aggregation choices, including count, min, max, and several statistical operations. Combine it with a drop-down menu and you can let your users choose which aggregation function to apply! Let's see how: Build a Function Chooser ToolSuppose we have this dataset: We might want to run different aggregations to understand the data, e.g. SUM, AVERAGE, COUNT, etc. Rather than insert every one of these functions into our Sheet separately, we can use a single SUBTOTAL instead. The SUBTOTAL function lets us specify what aggregation method to use. For example, this formula will sum the values in our dataset above: =SUBTOTAL(9,B2:B11)whereas this one will calculate the average value for us: =SUBTOTAL(1,B2:B11)Notice that the only thing that changes is the function code in the first argument (changes from 9 to 1). The full set of function codes is: So, we can create a Dropdown menu (here's how) from these function options: Then, we let users select a function from this dropdown and an XLOOKUP returns the corresponding function number from the table. We then nest this inside the SUBTOTAL: =SUBTOTAL( XLOOKUP( D5, G2:G12, H2:H12), B2:B)Now the aggregation option is driven by user choice! _______ If you enjoyed this newsletter, please forward it to a friend who might enjoy it. Have a great week! Cheers, |
Google Sheets Tips
by Ben Collins
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 this anniversary edition of the Google Sheets Tips newsletter, your Monday morning espresso, in spreadsheet form. This is the 300th edition of this weekly newsletter! 🎉 I've been sending it out for six years. The first issue — about keyboard shortcuts — was sent on 30 April 2018 to 2,357 of you. (Huge shout-out to anyone who's still with me!) In honor of this milestone, I want to look back at the six-year journey: The newsletter began life as the "Monday Tip" newsletter,...
Hi Reader, Welcome to the Google Sheets Tips newsletter #299, your Monday morning espresso, in spreadsheet form! A big thank you to this week's sponsors who help keep this newsletter free to read: SHORTFORM Raising the Bar on Book Summaries With Shortform, you get deep insights that go beyond the typical 1-pager, with chapter breakdowns, well-researched commentary and analysis, connections to other books and counter-arguments, and even interactive exercises. Instead of spending days on a...
Hi Reader, Welcome to the Google Sheets Tips newsletter #298, your Monday morning espresso, in spreadsheet form! I hope you are doing well on this fabulous Monday morning. As an adult, it's rare to experience something completely new in day-to-day life. Days follow a familiar pattern, even if the specifics are different. Last week, however, the New York region was hit by a rare earthquake (thankfully, not too serious). I happened to be in Legoland New York with my family, in the gift shop,...