HSTACK and VSTACK Functions: Create Cross Tabulated Report With Total Rows. Excel Magic Trick 1781

These two functions were released in March 16, 2022 and are in Beta at that time.
Learn about the new HSTACK and VSTACK functions to Create Single Cell Cross Tabulated Report with Total Rows.
Topics:
1. (00:00) Introduction
2. (00:37) Video Link for Old Version of Formula
3. (00:54) Caveat: PivotTables are Easier. But if you want solution to instantly update, then Formulas are the only method.
4. (01:14) Concept Trick to make the complicated formula easier to understand.
5. (02:14) LET Function to build variable inputs and deliver the final report.
6. (03:13) Create unique lists for employees and products with SORT, UNIQUE and TRANSPOSE functions.
7. (03:50) How to look at each part of formula when building a formula with the LET function.
8. (04:20) Build First Row of Report using HSTACK function.
9. (05:39) Build Second Row of Report with HSTCK.
10. (05:43) SUMIFS to create sum of sales for product by employee.
11. (07:22) SUMIFS to create sum of sales for employee.
12. (08:05) Build Third Row of Report with HSTACK and SUMIFS.
13. (09:18) Create final report with VSTACK.
14. (09:46) Test formula by adding new data.
15. (10:21) Conditional formatting updates when new data is added.
16. (10:57) Summary
17. (11:09) Closing and Video Links

[petercompton]

What a great explanation. You almost succeed in making it seem simple!

[kiwikiow]

Magic HSTACK and VSTACK 🪄🧙‍♂️ Wow 😮 👍👍

[bamakaze]

Mike, this is beyond excellent! The let function with H-Stack, V-Stack, and conditional formatting is epic.

[mattschoular]

Mike, in my world my PC is my computer and my monitor is my output device...I believe in your world, your brain is the computer and your PC is merely your output device. You're a Genius. Thanks for sharing, as always..

[grahamc]

Excellent stuff! Been waiting for something like this....My boss already thinks my Excel skills are from the realms of Dark Magic and Witches....this will just confirm that suspicion when the company gets these functions at some time in the future!

[darrylmorgan]

Boom!Really Cool Fun Class, Epic Formula With Let-Hstack And Vstack...Thank You Mike :)

[kiya]

Excel's becoming very powerful, and yet very easy for one cell formulas fans.
Thank you

[shubhampawar]

Bunch of advance function in single formula ...Great ❤️👌😍...but it quite difficult too for me ..but I will go through it again 🤟

[excel_along_the_way]

Hi Mike, epic. I thought the first video will be the basic functionality. But cross tabulation was epic. Thank you.

[ricos]

Love it Mike. Typically, my internet breaks and I have to go through the suspense of waiting for a new router to arrive to test this!
Probably too much for one video, but an idea for your next one would be to create a PIVOT lambda with this function. Something like: PIVOT(rows, columns, values, includeRowTotals, includeColumnTotals) and then just apply the same logic. The values criteria could be an option to choose 0 for sum, 1 for count, 2 for average etc. Or it could be a lambda. Or just keep it simple and exclude values!

[Al-Ahdal]

Too good, beyond excellent! Simply is epic.

[bali]

Beautiful. That's was a joy to watch. Thanks!

[wayneedmondson]

Awesome Mike! Wild stuff.. the new functions open up all sorts of new possibilities. Thanks for the great demo! Thumbs up!!

[pmsocho]

One more comment because these functions are so cool!

[canirmalchoudhary]

This can replace classic pivot table report, Alt enter trick is cool.

[nadermounir]

This video is epic. Thanks a million Mike for your hard work 👍

[sevagjb]

Sure is much fun, Thanks Mike i love it

[HusseinKorish]

Wow ... that's amazing ... i still using the formula explained in EMT 1526 .... but this is greater... thanks Mike.

[JoseAntonioMorato]

Dear Mike,
Your demonstration of how to Create Cross Tabulated Report With Total Rows was fantastic.
But, I'm not understanding why the files that are available for download are coming with the .xlsm extension, if there is no macro in them.🤗

[spilledgraphics]

Like n° 42, comment n°3 🔥 Amazing Mike!

1. so no more pivot tables? ouch! haha just kidding!
2. Loving the enthusiasm you're putting into the video! 👌👌
3. Loving the BAM! 💣...drops... 💥!
4. Go TEAM! 🏆!!!