MS 365 Excel Basics 09: PIVOTBY & GROUPBY Functions Beat PivotTable: 10 Mind-Blowing Examples!

Link to LAMBDA video:

In this video learn about PIVOTBY & GROUPBY Functions:
Topics:
1. (00:00) Introduction
2. (00:46) Topics in video
3. (01:48) Full list of Array Functions seen in this video
4. (02:10) Fundamental of Dynamic Spilled Array Formulas. Learn about the SEQUENCE array function.
5. (06:17) Conditional Formatting for Dynamic Spilled Array Formulas
6. (07:40) GROUPBY & PIVOTBY arguments
7. ( 08:07) History of Single Cell Reporting Formulas
8. (10:07) When to use a PivotTable and when to use GROUPBY & PIVOTBY functions.
9. (14:19) Example of Instant Update for GROUPBY & PIVOTBY functions and compare it to the PivotTable Cache.
10. (16:28) Example 1: GROUPBY & Excel Chart to create frequency distribution that updates instantly when source data changes.
11. (20:29) Description of field_relationship argument in the GROUPBY Function.
12. (22:09) Add conditional formatting to spilled report.
13. (23:02) Connect Excel Chart to Dynamic Spilled Array Formula
14. (23:37) Know Bug in GROUPBY & Excel Chart When you insert a column into the work sheet. At this minute mark in this video, I have inserted the 2-minute video I made to ask the YouTube audience for help. I also inserted a 30 second video with the replies about this known bug.
15. (26:44) Example 2: Use Eta-LAMBDA ARRAYTOTEXT inside the function argument in the GROUPBY reporting function.
16. (27:58) Example 3: This problem uses a boomerang company Baltic birch aircraft plywood product cost table of data. Use Eta-LAMBDA MEDIAN inside the function argument in the GROUPBY reporting function. Use the field_relationship in the GROUPBY function to sort the second column as a table, rather than in a way that respects the hierarchical relationship between the two columns.
17. (30:40) Example 4: Use XLOOKUP to calculate sales in the values argument of the GROUPBY function.
18. (32:33) Use the VSTACK function to add custom report headers to the GROUPBY generated report.
19. (33:20) Example 5: Use HSTACK function in the function argument of GROUPBY to have two columns in the report with two different calculations.
20. (34:21) How to use the DROP array function.
21. (35:33) Example 6: Create a payroll report that avoids a helper column and instead makes the hourly calculation directly in the values argument of the GROUPBY function.
22. (37:17) Example 7: PIVOTBY function to calculate the % of Grand Total and % of Parent Row Total. Learn about the related_to argument in the PIVOTBY function. Connect the PIVOTBY function to a formula input from a cell in the worksheet so that the user can switch between reports easily.
23. (38:32) compare the arguments in GROUPBY and PIVOTBY.
24. (42:28) Example 8: PIVOTBY function to create a cross-tabulated report.
25. (43:13) Example 8 (second part): Create a three-in-one report that allows a user to select the type of report from a cell and then the chart updates to reflect that new chart. The three reports are: % of Grand Total, % of Row Totals, % of Column Totals.
26. (44:57) Example 9: Use GROUPBY to help create an X-Y Scatter chart. PivotTables cannot be the source data for an X-Y Scatter Chart.
27. (45:17) Example 10: Test GROUPBY and PIVOTBY array functions on 990,000 rows of data. Then perform well.
28. (45:47) Conditional Formatting with an AND Logical Test to apply dynamic formatting to a Dynamic Spilled Array Formula Report.
29. (49:49) HW Practice Problems for you!!!
30. (49:54) Summary
31. (51:07) Closing

Using the gear icon setting button below the video, you can watch subtitles in these languages: Afrikaans, Arabic, Bengali (some videos), Bangla, Dutch, Filipino, French, Hindi, Indonesian, Khmer, Malay, Malayalam, Nepali, Persian, Polish, Spanish, Swahili, Tamil, Telugu, Thai, Tibetan, Urdu, Vietnamese.
Using the gear icon setting button below the video, you can listen to an audio track in these translated languages: French, German, Hindi, Indonesian, Italian, Japanese, Portuguese, and Spanish.

Link to LAMBDA video:
#excel #groupby #pivotby #dataanalysis #groupby #pivotby

#excel #excel365 #excelisfun #highlinecollege #mikegirvin #freeexcellessons #excelformulas #excelfunctions #groupby #pivotby

[excelisfun]

At 28:00 minute mark, the table of product purchase data is based on historical past data, not current prices for products.
At 32:30 minute mark, I made the joke: "PivotTable with a VLOOKUP in it". I meant to say: "It is just like we have always wished for: PivotTable with a VLOOKUP in it!"

[MichaelVB]

You will always be my MVP, Mike. The Only App that Matters is an awesome resource. I can't wait to own your dynamic array book too! I've got all your books and they have all helped me in my quest to become a better data analyst. Thank you for all your work over the years. Much appreciated!!

[BIZSKILLPRO]

Wow! Excel King, I've been learning from you for ages. No one provides such deep knowledge and understanding.

[Softwaretrain]

Hi Mike, this video is full of great insights. Regarding the point at 25:39: My testing shows the issue relates to the first argument of the SERIES function. To avoid reference misalignment when columns are added, ensure this argument does not point to the top-right corner of a spilled formula range. Instead, either omit the first SERIES argument or reference a cell outside the spill range’s top-right corner to maintain stability.

[richardhay]

It's very easy to refresh a Pivot Table but it's even easier to forget ti do it!

[DavidBolte]

Viewers have another heroic excelisfun lesson from a true Excel Hero, Mike Girvin. What a stimulating bundle of Excel erudition, classic Mike Girvin enthusiasm and empathetic pedagogy showcasing these exciting new functions! Many many thanks Mike!

[gvitullib]

Truly amazing this pair of functions. And when you say 'old school', I can't believe how things are changing with Excel. As always, a great video. Thanks Mike.

[MaydayAggro]

Another advantage of pivot tables is that you can double click and drill down, creating a separate table that can be edited. That's the feature I use most often to assist with account reconciliations.

[karolinab]

This is truly better than any other streaming platform!❤ thank you so much for explanation of field relationship and relative_to arguments.

[nsanch]

Mike! This is a fantastic video! I'm taking my time getting through it but I am so happy you put this together. You're teaching some details which I knew I was missing on both pivot by and group by. Thank you.

[Excelambda]

For Excel aficionados this is better than Netflix !!😉✌🏼👍🏼

[sscire]

excellent video and very complete!! I particularly appreciated the detailed explanation of the last optional parameter of GROUPBY ... that I had never found anywhere ... now everything is clear. Thanks

[dougmphilly]

arraytotext might just save my life. thank you mike!!

[chrism]

Thanks Mike, great way to kick off the week. Go Team!!!!

[markpodesta]

Thank you Mike. Very useful, although conditional formatting can be quite fiddly when using GROUPBY and PIVOTBY. Hope they will improve it. 😃

[shubhampawar]

Great Video❤🎉. The helper column/ xlookup inside the group by really changed the game ❤

[cidfidchris]

Thanks Mike. Awesome as usual!! I have been following you for 15years + and your like the g good 🍷.. getting better with ages 😂 thanks

[reanalytics]

Let me first, like, comment and share before even watching because I trust the quality 💯💯💯💯💯💯

[godwinfrancis]

Thanks For Adding Malayalam. Its helping me alot.

[cheikhabdallahcamara]

I sincerely appreciate the invaluable instructional videos you are providing. The audio track is essential for us, as we follow along and do not speak English (I speak Italian and French). Unfortunately, in this video, the audio track feature is not available.