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

[@petercompton538]

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

[@mattschoular8844]

Amazing..... 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..

[@excelisfun]

That is too funny, Matt : ) : ) : ) : ) You are right, but only in this regard: I am only good at telling stories to make complicated things less complicated. So the story starts in my brain and then I enact it on the computer lol As always, I depend on the really smart guys like Bill Szysz, ExcelLambda, and so many others to help show me the efficient methods, then I just invent a story to help slow and not-so-smart people like me to understand : ) You are welcome for the share, as always, Matt. I am happy to share. Go Team!!!!!

[@bamakaze]

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

[@excelisfun]

Epic is for sure!!!!! We are finally having too much fun ; )

[@kiwikiow]

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

[@excelisfun]

Yes, and the Ghost even says Wow!!!!!! They are magic : )

[@iankr]

Mind-bogglingly brilliant!

[@excelisfun]

Riiiiight... Microsoft has given us so many new cool functions!!!

[@bali501]

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

[@excelisfun]

Glad this helps!!!

[@MisplacedTrust]

Super impressive, pure genius

[@excelisfun]

Glad you like it, Anthony!!! The story telling part of the video, to make the complications less complicated, is the contribution that I make ; ) the true genius comes from Microsoft and Formula Master Teammates like ExcelLambda abd Bill Szysz ; )

[@darrylmorgan]

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

[@excelisfun]

Yes, HSTACK and VSTACK to enhance the fun level. You are welcome, Felloe Biker!!!!

[@777kiya]

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

[@excelisfun]

One Cell Formula Fans!!!!! I love that. I am one of those : ) : ) : ) Thanks for the cool phrase.

[@777kiya]

@@excelisfun You are welcome

[@excelisfun]

@@777kiya Go One Cell Formula Fans!!!!

[@grahamc5531]

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!

[@excelisfun]

The wait is almost over. HSTACK and VSTACK change so many things : ) : ) LOVE how you said this: "Excel skills are from the realms of Dark Magic and Witches"!!!!! As you say, confirmation of their sessions WILL be greatly enhanced when these functions are deployed to all : ) : ) : )

[@shubhampawar8506]

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

[@excelisfun]

It does take practice. Even for me, with a formula like this, I have to practice it a bunch before making the video : )

[@nadermounir8228]

Amazing video Mike 📹 👏

[@excelisfun]

These functions have changed our Excel lives : )

[@excel_along_the_way]

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

[@excelisfun]

Yes, it is a bit harder with cross tab. But you said it correctly: Epic!!!! Too much fun : )

[@canirmalchoudhary8173]

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

[@excelisfun]

PivotTables will not be replaced because they are just too easy : ) : ) But these new single cell reports certainly give many of us the ability that we have sought for so long: reporting without the refresh!!! Glad you liked the video and that Alt + Enter trick.

[@wayneedmondson1065]

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

[@excelisfun]

You are welcome for the "opening up" demo, Wayne!!!!!

[@ricos1497]

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!

[@excelisfun]

Yes, that is a great idea, Rico S!!!!! I actually wanted to show the Conditional Formatting and the LAMBDA version in this video, but then those topics might have taken away from the excitement of HSTACK and VSTACK and their new arrival : ) But, wow, you idea to have 0 for sum and so on is over-the-top cool : ) : )

[@amardeepsingh5252]

@@excelisfun have you got a video on this?

[@amardeepsingh5252]

Whoever checks with me for Advanced MS Excel related videos, your name is always top of the mind!

[@lampros6156]

Excellent!

[@excelisfun]

EXCELlent ; )

[@Al-Ahdal]

Too good, beyond excellent! Simply waoo............formatting is epic.

[@excelisfun]

Lots of complicated work in comparison to PivotTable, but it is dynamic : ) Glad you liked the vid, Syed Hassan!!!!

[@Al-Ahdal]

@@excelisfun , I tried but the formatting is not working when changing the row and column header from the drop down. I made 2 variables, i.e. column and row header.

[@nadermounir8228]

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

[@excelisfun]

Glad you liked the epic fun, Nader!!!!

[@johndurran614]

Awesome Mike. Well done

[@excelisfun]

Glad you like it, John!!!!

[@robertovelicaz7719]

Absolutely brilliant Mike! Thanks 😃

[@excelisfun]

Glad it is brilliant for you, Roberto!!!!

[@murat.hasanoglu]

This is excellent Mike, Thanks.

[@excelisfun]

You are welcome, Murat!!!!!

[@davorercegovac3674]

This is excellent Mike. Thanks🙂👍

[@excelisfun]

You are welcome for the EXCELlent fun, Davor!!!!

[@9019828585]

Yet another Masterpiece 👌👌👌 .

[@HusseinKorish]

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

[@excelisfun]

Yes, it is greater : ) : ) : ) You are welcome, Hussein!!!!

[@sevagjb]

Sure is much fun, Thanks Mike i love it

[@excelisfun]

Glad you love it, Sevag!!!!!!

[@chrism9037]

This is so awesome, thanks Mike!

[@excelisfun]

You are welcome, Chris M!!!!!

[@SAHIR129]

Amazing

[@excelisfun]

Glad it is amazing for you, MOIZ!!!

[@pmsocho]

One more comment because these functions are so cool!

[@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! 🏆!!!

[@excelisfun]

No, no, no. don't even joke. PivotTables will get sad lol PivotTables rule for most reports, but sometimes, those formulas are just perfect, like your spilled MODS reports where the criteria keeps changing : ) : ) : )

[@excelisfun]

BAM!!!! Go Team : ) : ) : ) : )

[@spilledgraphics]

@@excelisfun haha! oh my! like the name of your awesome channel: Excel is fun !!! p.s. what's MODS, stand for? 😵🧐😅

[@excelisfun]

@@spilledgraphics Much Optimistic Delicious Shredding ?

[@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.🤗

[@excelisfun]

I changed it to .xlms. The file can have that file extension even if there is no VBA in the file. I use mostly .xlsm files at Highline College because I often have code in them to help with my grading.

[@excelisfun]

Glad you like the demo : )

[@pmsocho]

Simply beatuiful! 👍👍

[@henryg5735]

Blown away - amazing! 🤠So logical and easy to follow your steps. Whether I can re-create/do my own is a far different matter. I have always disliked pivot tables due to the refresh issue, but of course still use them. Now all I need is a pivot table to formula converter!

[@excelisfun]

Glad you like the story that I tell to make the steps easy to follow, Henry G!!!!! I am sure that you too can build a story so that you can build these sorts of reports too. BUT, really, I use PivotTables most of the time because they just have so many advantages: drag-and-drop, click to change the calculation, formatting is always there even if you pivot or move the report, and so much more. We can live with the refresh... : )

[@barttitulaerexcelbart9400]

Hi Mike, a long applause... I personally would have preferred to first generate the 3 parts in a separate part then combine it (if possible anyway?). For the experts combine it in one mega LET....If I look at your LET formula however, you "only" refer to the table and the headers. I can imagine that the developers in Redmont will/can create a new function in 2024 to generates this dynamic Pivot table. Let's call this MPT (Mikes Pivot Table!) 😉

[@excelisfun]

Too funny: MPT. But, I am confused, I thought I did generate the 3 parts: first row, second row and third row... Maybe I did not understand what you mean?

[@barttitulaerexcelbart9400]

@@excelisfun Yes you started explaining this, and that was good! But I meant maybe first generate 3 different formula's on 3 different cells to get the result of the pivot table. (if possible I don't know...) If that works the next step is to make the whole result with one LET formula....(as you did...)

[@excelisfun]

@@barttitulaerexcelbart9400 Yes, that is a fine method. If I understand you correctly, that you would build the three formulas in the cells and then do HSTACK. And for most users that is a more approachable method. Good point, Bart : )

[@bagnon]

Is there a video that has the names of all the different types of formulas, operators, and reference types like the # and @? If not, that would be useful to put in one video.

[@excelisfun]

I got a book coming out that is like a laundry list of everything: operators, logical tests, lookup functions and much more. I will eventually make videos too - and I am sure that atr least one will have the list that you want : )

[@AnandGautam9901]

Eagerly waiting for the Combination

[@excelisfun]

I hope you will get them soon, ANAND!!!

[@msantosh1220]

Thanks!

[@msantosh1220]

Happy Holi, 😊 thanks for this video,

[@excelisfun]

Go Team!!!!!!!! Thank you, Santosh : ) : )

[@excelisfun]

@@msantosh1220 You are welcome for the vid!!!

[@GeertDelmulle]

Great application of HSTACK and VSTACK! This is how we want Excel to work! -) (No more ghost records, etc.) Hope to have these new functions soon. (And I hope they will be expedited to the MS365 semi-annual update cycle as well) PS: surely this affects your book, no?

[@excelisfun]

You said it so well: "This is how we want Excel to work!" !!!!!!!!!!!!!!!!!!!!!!! The book will be missing lots of stuff, but as a foundation book for people who want to be good in Excel, it should be OK. I hope it is released to all M 365 soon. I feel like these functions are sort of like FILTER - just sooooooooooo many useful ways to use them. I always think of you and Recalc Or Die when it comes to using formulas for everything in your work so you and your colleagues can have the what-if analysis working all the time : )

[@johnborg5419]

Thanks Mike. :) :)

[@johnborg5419]

Amazing!!!!! Simply Amazing!!! Thanks Again. :) :)

[@excelrobot]

Good stuff!

[@excelisfun]

Glad you like it, Om!!!!!

[@Surachetphunsawat]

Very good I like video

[@richardhay645]

Great to see another video so soon. The LET vigilantes should be VERY happy! LOL. But maybe append the new data with VSTACK (and create product header list on top row with unique and TOROWS rather than TRANSPOSE)?

[@excelisfun]

I love it: LET vigilantes !!!!! Yes!!! I thought I did append with VSTACK? I am not following for your first row idea, with UNIQUE and TOROW? What is your idea for this formula?

[@richardhay645]

@@excelisfun YES! the LET folks are passionate!! For the append , I was referencing the video I think about 9:49 or so where data are appended to the original table twice with cut and paste. For the product headers, something like UNIQUE(TOROWS[Product Column]),TRUE. Maybe can avoid the "data is in row" argument of UNIQUE by taking the UNIQUE before applying TOROWS?

[@excelisfun]

@@richardhay645 I am still not following, but I am famous for not understanding what people ask or say : ( Are you saying that you want to append the new data to the Excel Table fSales? What I was trying to do with that is just show that if someone is adding new data regularly, that is sort of what it would be like. But I see you are referring to just the Product column, so I am not sure.

[@richardhay645]

@@excelisfun Definitely not being critical of your approach. I am trying to push the limits of these new tools. That is how I best learn their capabilities. I am intrigued with using these functions to create single cell reports using apparioriate combinations of the "new 14" plus other DA functions--especially FILTER, SORT & UNIQUE. I have been able to append with VSTACK/HSTACK but they append Tables by creating relationships not by modifying the tables (as happens with cut and paste). But the result I have is a filtered appended column or row by combining with SORT & UNIQUE the automatically updates as new data is added to any one of the tables. I have done it and it works. I will continue to work on my single cell formula and share whit is finished hopefully by the first of the week! I will also comment on any crash-and-burn scenarios!

[@excelisfun]

​@@richardhay645 O No, I knew that you were not being critical. The beauty of our Team is that we all push the limits and keep trying and because so many eyes are all looking, we end up with many more creative and efficient solutions. I can not wait to see your formula : ) : ) : ) : ) Go Team!!!!

[@JonathanExcels]

Impressive

[@excelisfun]

Fun too : )

[@adin6429]

This seems to be a total game changer. Btw, are these functions only available on 365 beta program for now?

[@excelisfun]

Game changer for sure. Yes, only in about 50% Insider. A couple weeks all of Insider, then a few montsh later, all of M 365.

[@migcramer]

Great demo, Mike! As an FYI, I tried changing your EMT1781.xlsm file to R1C1 notation which kicked up stacks of “Name Conflict, Name cannot resemble a reference” forcing me to rename _xlpm.C [and similar] objects. (you also have a bunch of Names with #REF! in your demo file, which may not help either). I freaked out thinking that this Excel Beta development version is going to corrupt [all] my .xlsx files, as I always use R1C1 notation because it takes away a lot of agony when building reference, not least in Conditional Formatting… (e.g. =IF(ISNUMBER(RC),TRUE,FALSE) , RC being any active Cell in the Cond. Form. Range) (A habit [which I consider good] that I picked since developing Excel 4.0 Macros… yes back in 1992, 30 years ago!... ). But I copy/pasted Values of your Table to a virgin new .xlsx and it all worked fine. Having said that I renamed your variables to long names, and only then it worked (for some reason, I guess there was no other way of keeping _xlpm happy). So I edited the Function to: =LET( SalesCol,fSales[Sales],EmployeeCol,fSales[Employee],ProductCol,fSales[Product], EmpoyeeFN,fSales[[#Headers],[Employee]],ProductFN,fSales[[#Headers],[Product]], EmployeeList,SORT(UNIQUE(EmployeeCol)),ProductList,TRANSPOSE(SORT(UNIQUE(ProductCol))), FirstRow,HSTACK(EmpoyeeFN&"/"&ProductFN,ProductList,"TotalSales"), SecondRow,HSTACK(EmployeeList,SUMIFS(SalesCol,EmployeeCol,EmployeeList,ProductCol,ProductList),SUMIFS(SalesCol,EmployeeCol,EmployeeList)), ThirdRow,HSTACK("TotalSales",SUMIFS(SalesCol,ProductCol,ProductList),SUM(SalesCol)), VSTACK(FirstRow,SecondRow,ThirdRow)) Cheers, and I cannot thank you enough for your brilliant videos! I keep learning something new and useful every time I watch one. Thanks to you I now use UNIQUE() nested in =”IN(‘”&JOINTEXT(“’,’”,FALSE,UNIQUE(Table1[Column#]))&”’)” to help building IN() lists for T-SQL WHERE Clauses, saving me oodles of time! 😉

[@salahderdar5148]

Wow, it's look like QUERY function in Google sheet

[@thierrybernot9369]

Woaw.

[@excelisfun]

Well said : )

[@garethwoodall577]

Mind blown. Off to bed

[@excelisfun]

Glad you like it : )

[@michaeldingee743]

I came across this great video., I created a cross tabulated report, using your formula. Is there a way to add a slicer or slicers to filter the report? LET(V,Data[Vendors],s,Data[StartOfMonth],O,Data[Orders],c,Data[ColumnCount], sl,TRANSPOSE(SORT(UNIQUE(s),,-1)), vl,SORT(UNIQUE(V),,1), cl,COUNT(c), fr,HSTACK("Start of Month",sl,"Total Orders"), sr,HSTACK(vl,SUMIFS(O,V,vl,s,sl),SUMIFS(O,V,vl)), tr,HSTACK("Total Orders",SUMIFS(O,s,sl),SUM(O)), VSTACK(fr,sr,tr))

[@excelisfun]

I am pretty sure that you can use the FILTER function on the input columns. Or when the new beta functions actually make it into M 365 Excel, you can use the up and coming PIVOTBY function.

[@michaeldingee743]

@@excelisfun I have the beta version with Groupby and Pivotby. The customer who is requesting this report has 365 but not the 2 new functions. I will try using the filter function.

[@msantosh1220]

Thanks!

[@msantosh1220]

Tagged to wrong video😅

[@excelisfun]

Thank you so much, Santosh : ) : ) You really help me so much : )