Excel PIVOTBY Function to Create Completely Dynamic PivotTable Formula Reports! EMT 1845

[@ubaidillahmuhammad20]

Mike ..lett"s continue ...new emt ...❤

[@excelisfun]

I can't wait too. But I have been taking care of my dying mother for last 4 months. As soon as I can finish her affairs, I will be right back at it : )

[@ubaidillahmuhammad20]

I am sorry to hear that..

[@eduardobarbosa1958]

Hi Mr. Girvin, my name is Eduardo, i'm 42 yo, i'm from Brazil and this is my first comment. I'm a "client" of your content about 13 years. Your videos changed my life at work and made me love Excel for life. Your didatics is amazing and the happiness during all the contents is contagious. Thanks for a life dedicated to sharing your knowledge with us. I don't speak english very well, so, forgive me if a wrote something wrong. Be safe, be healthy and be fun as always. I'll be here for ever! 🤓👏

[@excelisfun]

I am very happy to help. Thank you for your kind words - it means a lot : ) : ) : )

[@vishal.pandey2001]

It's 12:00 am in India right now but I cannot resist watching the exciting ways excel mesmerizes More Important than that if it was not because of you I would never be able to know enhancement and advancement in excel Thank you very very very much for the efforts you put in

[@excelisfun]

You are welcome very much!!!!

[@SunilYadav_ExcelsDive]

Sir, Your videos make more more interested to excel, Thank you for your guidance!

[@tibibara]

After your first EPIC LAMBDA MECS video, I started to take advantage of the fact that in LET we can 'comment' a bit 🙂 -> in the single cell/complex formulas, so in the first line I usually add a 'quick-note' to the solution and then ALT+ENTER, so when I come back and review it, I can get some quick-info in the formula bar. So now, for this solution I see this in my formula bar: '=LET(fxnote, "", ' Thanks a lot, Mike! 🙏

[@excelisfun]

You are welcome a lot!! Notes are good : ) : )

[@excel_along_the_way]

Hi Mike, in Pivot Tables when you drag something into the filter option, you have the options to multiselect or select all. In the PIVOTBY you can at least mimic a select all by doing the following in the [Filter array] argument in your example" XLOOKUP(K4,C2:E2,C2:E91,F3:F91*0)=K5" and clear the selections in cells K4 and K5. When there is no selection in K4 the [If_Not_Found] argument in XLOOKUP will return array F3:F91*0 that is = to an array of zeros and it will be equal to K5 that is blank but excel take it also as 0 thus will return an all TRUE array until you select something in K4 and K5. This will make the [Filter array] more dynamic. This one was fun.

[@Vivek-j2t2z]

King of Excel You Always Super Pass the expectation

[@excelisfun]

That is just what happens when we have too much fun : ) : )

[@bonmonil]

Simply put: Amazing vedio I've been watching your channel for years now, your walkthroughs are the best . Thank you for your devoted work

[@ExcelsDive]

This is really a magical channel for excel, I learned from you. Thank you very much sir 🙂

[@excelisfun]

You are welcome, ExcelsDive!!!!

[@kiwikiow]

Amazing video. PIVOTBY and drop-down lists are magical. They work well together to extract required information instantly. Thank you Mike 💚

[@excelisfun]

You are welcome my Ghostly Friend : )

[@roywilson9580]

Wow great video. Excel just keeps getting better and better for us users. It is about time the marketting department at MS got behind selling the 365 licenses to businesses - some of these new functions would be so useful in the office. Just imagine turning out an ad-hoc report without having to write a whole pivot table and try to impress on the dept. head the need to REFRESH the report if s/he adds extra data to the table! That is a world I want to be a part of :D

[@excelisfun]

I 100% agree! Get that marketing department fired up !!!!!

[@richardhay645]

Great video! Another great new function! Spreadsheet PTs may be a bit easier if you accept the "obnoxious" default format. But PIVOTBY and GROUPBY make the job pretty easy. Almost unlimited possibilities for combining with other functions. Bottom Line: I'm a fan!!

[@excelisfun]

Love this wording: "obnoxious" default format . So true lol I am a fan too : )

[@dschmid8845]

Mike, thanks again for another epic Excel video! Another totally awesome solution I wish I had years ago for analyzing our data. What adds power to the whole thing is the use of validation drop-downs to make it more interactive and dynamic. The really old way for me would be to build macros and a complex series of pivot tables requiring a refresh to update (yawn). The new way is a thousand times better.

[@excelisfun]

Yes, a thousand times better : ) : ) : )

[@samirkapadia3423]

As usual superb video tutorial. Have been waiting for M365 to update the excel version since the day this video was posted. The update came yesterday ... finally....I couldn't test it / apply what you were showing in this video until now... The permutation and combinations of the tricks and building multiple scenarios is just too good.

[@excelisfun]

Great!!! Too good is EXCELlent ; )

[@markpodesta4605]

More Excel magic. Thank you Mike.

[@excelisfun]

You are welcome for the magic!!!~!

[@Jim-zm6fw]

Hi Mike: Thanks again for your commitment to up-leveling this community! I am always amazed by your solutions (and by the hints from some of the members). I love your enthusiasm and teaching style. You have helped be be a much better Excel user.

[@excelisfun]

I am so very happy to help. Go Team!!!!!

[@SyedMuzammilMahasanShahi]

Thank you so much Amazing Mike for this EXCELlent video.

[@excelisfun]

You are welcome so much, Fellow Teacher!!!!

[@ricos1497]

Another comprehensive video Mike, that is fantastic. I love the CHOOSE function idea, you can actually return more than one FUNCTION type using that too, if ever you needed such a thing. Like in the groupby video, CHOOSE() works, as does HSTACK() for multiple ETA function returns, but this can't be replicated in LAMBDA unfortunately. Again, you also can't differentiate between line and subtotal calculations in the LAMBDA either, which is mildly disappointing (you can't replicate DAX's HASONEVALUE() functionality). That would be next level great. Weirdly, you don't seem to be able to not output Subtotals only (without grand totals) too. Otherwise, these two new functions are brilliant.

[@excelisfun]

brilliant with a few flaws. I guess we can live with that. Funny: mildly disappointing !!!!

[@thebhaskarjoshi]

Mike, thanks again for another epic Excel video!

[@excelisfun]

You are welcome again!!!!

[@johnborg5419]

Thanks Mike. Will surely try all tomorrow. Thanks again

[@excelisfun]

You are welcome again, Formula Guy John!!!!!

[@edge5817]

this is super fun and awesome! thanks Mike!

[@excelisfun]

You are welcome!!!

[@ExcelInstructor]

wow, its superb, I'm out of words. Thank you Mike for covering this :)

[@jist6953]

Really amazing stuff. Thank you. We need another book

[@tecwzrd]

This is huge with dynamic data updating automatically without having to refresh a "normal" pivot table. The filtering abilities is top notch as well. Can you also filter the numbers e.g., filtering out zero amounts or filtering < or > as easily?

[@eduardobarbosa1958]

It needs to be tested, but if you can do it in a filter function, you can do it here too using * to "and" conditions and + to "or" conditions and separating the conditions inside parentheses. I do it all the time using filter functions.

[@excelisfun]

Sure. As we know, anything is possible with Excel. I am not at a computer with the new functions for another day or two to try and test it.

[@millawitch]

Very cool! I'm thinking with a little bit more work you could limit report errors with further dependent data validation drop down lists for the criteria - once you pick one, only the other 2 remain available. But in the end it all depends on how the file is going to be used, and by how many people.

[@excelisfun]

That is a really great idea, Teammate millawitch!!!!

[@reng7777]

Nice video!! regretfully that fucntion is just for Office MS edition and not for home edition, i hope MS can release it for Homde edition as well.

[@excelisfun]

The only version that will have them is Microsoft 365 Excel. They have home and professional for that version.

[@anthonygeo3]

Brilliant video

[@excelisfun]

Glad you like it : ) : )

[@davorercegovac3674]

Amazing Master Mike :) Thanks

[@excelisfun]

You are welcome : ) : )

[@syrophenikan]

Fantastic work!!!!

[@excelisfun]

MS did do fantastic in giving us these cool function!!!

[@carolines2232]

This is amazing SO much!! Is there any way to add a search bar to this? Eg I’m using data with over 50 cities in a column and don’t want to scroll down the dropdown

[@AnandGautam9901]

Absolutely Amazing 😅

[@excelisfun]

Glad you like it!!!

[@ExcelHechoFacil]

Mike por favor intenta esto: GroupBy(Region,[Sales:COGS],Lambda(x,y,Sum(_x)/Sum(y)),3) x: The Filter for Criteria and y: The entire Columns for criteria. Thanks.

[@pmsocho]

Awesome!

[@excelisfun]

Thanks, Teammate!!!!

[@snipelite94]

Huzzah for Mike 😁

[@paser2]

brilliant as always!

[@bonmonil]

Thanks!

[@excelisfun]

Thank you for the kind donation : )

[@blahdelablah]

Great video, thanks. Just curious, was it necessary to hard code the list of functions as a comma separated list in the LET formula or could you look up the values from the list of possible values you already had on the sheet?

[@thelastfry23]

Was PercentOf taken out with the intention of being added back in? I was excited to use that for allocation purposes!

[@excelisfun]

We don't know. It was there for two days and then it wasn't...

[@chrism9037]

Mike, that LET formulas was insanely amazing! Question: since the table ended on row 91, and you were using row 3 to row 91 as cell references instead of table nomenclature, if you add rows at the end of the table, how do you handle the additional rows? I might have missed it. Thanks Mike!

[@excelisfun]

I did not use an Excel Table becasue the table formula nomenclature looks messy. In real analysis I would use an Excel Table.

[@richardhay645]

@excelisfun I also use Tables almost exclusivly BUT when I have situation where I cannot/do not I find the easiest way to let the report axpand Iis to use TAKE. I've been a big user of TAKE almost from it's inception. I simply put the report array (or TAKE-wrap the formula genersting the report) in the TAKE first argument and I use COUNTA in the "number to keep" argument and reference the entire columns of the primary data set (rather than the fixed 3-91range). This creates the expansion of the report. I started doing this since about a week after TAKE appeared--when I realzed that the number argument of TAKE would accept any function that generates a number. BTW I believe Mynda did a recent video on this method.

[@chrism9037]

@@richardhay645 Excellent!

[@aruannovincenzo]

Good morning Mr. Girvin, congratulations on your professionalism. When will we see the new features on excel desktop 365? I'm a beta Microsoft Office user but when i try to digit Pivotby in a cell i don't see It. Thanks very much for your teaching

[@excelisfun]

Beta should be in a week or so. All of 365 in a few months.

[@aruannovincenzo]

Thank you Mr Girvin. It's a pleasure to follow your channel

[@aruannovincenzo]

similar function is in google sheets an it's called =QUERY. I'm glad to see =pivotby and =groupby in excel 365. bypass basic pivot and refresh command. Very powerfull

[@mohamedchakroun4973]

Amazingggg as alwayssssssssssssssss thanks

[@excelisfun]

You are welllllllllllllllllllllllllllllcome!!!! : )

[@FinExcel]

❤ very informative ! But sir can you please help me to generate a report according to the Financial year . Let's say you have the same data but I want to do analysis as per FY . How can we do it at the Pivot table .

[@excelisfun]

You have to add a column to the table of data, like in this video: kzbin.info/www/bejne/b3vFeYNqnMR_e9U

[@ExcelInstructor]

Hi Mike, I tried to check those new forrmulas, however on my Personal and work PC on betachannel i dont have them :(

[@excelisfun]

Thanks for checking!

[@JonathanCodorniu]

Any idea why I would not have this formula under Microsoft 365 Apps for Enterprise?

[@excelisfun]

It is still in beta, but should be to all 365 soon : )

[@xlisgr8]

I am really enjoying this PivotBy function and have some doubt. Can we have subtotal function inside the lambda? I am trying to use Table Features along with PivotBy function. Since Tables are also having the slicer feature, if it is possible to use PivotBy along with slicers, then dashboarding will be very easy. I make use of subtotal function to find the sliced table data. Is it possible in PivotBy also? Now I am doing this workaround. =PIVOTBY(Order[Region], Order[Category],BYROW(Order[Row ID],LAMBDA(x, SUBTOTAL(3,x)))*Order[Sales],SUM)

[@SimonCai-q7h]

Hi Mike, do you know any reason why my MS365 does not have this Pivotby or Groupby? I have done my latest update.

[@OliverH_ATL]

What version excel is this ? It doesn’t appear as an option for me We are using office 365

[@excelisfun]

It has been in beta in Microsoft 365 for a few months and should be out soon in 365.

[@ExcelHechoFacil]

Delete the function PercentOf?

[@excelisfun]

It was there for 2 days, and then Microsoft took it out. We don't know why

[@ivanmamchych5802]

Perhaps Microsoft decided to improve PERCENTOF with more optional arguments, e.g. percent of total row, percent of total column, percent of grand total. It would be nice to get such options for percentage. P.S. Thank you, Mike, for a great review of the new function 🤝

[@brianxyz]

@@ivanmamchych5802 That would be great if they added in all of those options to match standard pivot tables.

[@mauhernan]

Could function textjoin work for the list of functions inside choose?

[@patricklonski]

In attempting to use Pivotby over pivot tables I have found pivotby to be an absolute disaster. It caused a long established financial workbook to crash repeatedly. Fortunately my "save and backup" macro makes multiple daily copies so I did not lose anything. I tested various versions with and without pivotby as well as other changes. Clearly pitvotby is not ready for prime time.

[@excelisfun]

You are right. That is why it is so delayed. The first release of the function was OK, but somewhere during the Beta updating something went wrong. I have also had some terrible crashes as you describe.

[@matthewhart3719]

Amazing

[@excelisfun]

Glad you like it!!!!

[@HusseinKorish]

EPIC and Amazing

[@aleb8243]

All these dynamic formulas are great except there is no way to have dynamic formatting too

[@excelisfun]

So true... Default anyway. Conditional formatting works. In addition, if you compare them to formulas, then the no formatting is the same. If you compare them to PivotTables, well Pivots have dynamic formatting. They have a use, though, for analysis with cell inputs where the inputs change all the time, then these new functions and the extra hassle of conditional formatting is worth the effort : )

[@monique1112223]

Did they get rid of it? I don't have this formula.

[@excelisfun]

It is in beta but should be in all of M 365 Excel soon : )

[@monique1112223]

@@excelisfun Got it. Thanks so much for responding.

[@rtrbs8383]

Hi Sir ..What kind of Subcription we should have to get these functions..I have 365 Version 2310

[@eduardobarbosa1958]

He's probably using an insider version (beta tester). In some months we'll be updated.

[@excelisfun]

@@eduardobarbosa1958 Yes.

[@Heybat1]

Hi. First thank You very much for a such great video explanation! I want to add to same scenario some fields such as price. For example, Gigi sold 200 kg ( 😊 ) Yanaki product for 2$ in West province, in MidWest for 2.5$ etc. Want to get average prices as we can add Field in Pivot Table (Price = Value/QTY). Thanks.

[@excelisfun]

I am unclear on data setup. Can you give me an example of the source data table and the result you want, then I might be able to help.

[@Heybat1]

@@excelisfun thanks for reply. Will give a very simple scenario. In first column date of purchase. In second column name of purchase items. In third column quantity (piece) of purchased items. In fourth column price of each purchased item. In fifth column name of city where item was purchased. There’s possibility that same item on same date may be purchased with different prices Need to get as row - name of items only (no need name of city) , on columns need dates of purchases, result as AVERAGE price of same item on mentioned date in Pivotby function. For example item name Apple on 1 January purchased as 10 pieces with 5$ in Milan. Same date purchased as 50 pieces with 10$ in New York. I need to get average price of Apple on 1 January. 10 x 5$ = 50$ 50 x 10$ = 500$ Average price =Total Value / Total purchased pieces 550$ / 60 = 9.17$ (average price)

[@excelisfun]

@@Heybat1 , I cannot figure out how to do the GROUPBY cannot do this calculation directly. There are many ways to do this: First: =LET( date,B3:B11,product,C3:C11,quantity,D3:D11,price,E3:E11, h,{"Date","Product","Average Daily Price"}, CriteriaRowHeaders,SORT(UNIQUE(B3:C11)), AveDailyPrice, MAP(TAKE(CriteriaRowHeaders,,1),TAKE(CriteriaRowHeaders,,-1), LAMBDA(r,rr,SUMPRODUCT(--(date=r),--(product=rr),quantity,price) /SUM(IF((date=r)*(product=rr),quantity)))), VSTACK(h,HSTACK(CriteriaRowHeaders,AveDailyPrice),HSTACK("Total","",SUMPRODUCT(quantity,price)/SUM(quantity)))) Second: =LET( DateProductColumns,B3:C11,q,D3:D11,p,E3:E11, gtp,GROUPBY(DateProductColumns,q*p,SUM), gtq,GROUPBY(DateProductColumns,q,SUM), h,{"Date","Product","Average Daily Price"}, RowCriteria,DROP(gtp,,-1), GroupSumSalesTotal,TAKE(gtp,,-1), GroupSumQuantityTotal,TAKE(gtq,,-1), VSTACK(h,HSTACK(RowCriteria,GroupSumSalesTotal/GroupSumQuantityTotal))) Third, just do two formulas and forget a single cell solution: =SORT(UNIQUE(DateAndProductColumns)) =SUMPRODUCT(--(Date=H3),--(Product=I3),Quantity,Price)/SUMIFS(Quantity,Date,H3,Product,I3) (copy this one down) Fourth: =SORT(UNIQUE(DateAndProductColumns)) =MAP(H3:H6,I3:I6,LAMBDA(r,rr,SUMPRODUCT(--(Date=r),--(Product=rr),Quantity,Price)/SUMIFS(Quantity,Date,r,Product,rr))) (This spills) Fifth: and the best by far is a standard PivotTable, where you add a column to multiply quantity by price for each transaction and call the field Sales. Then build a Calculated Field in the PivotTable (PivotTable Analyze tab, Calculations group, Field, Items and Sets dropdown arrow and then Calculated Field, like: Sales/Quantity

[@Heybat1]

@@excelisfun thanks a lot for prompt reply, appreciate 🙏🏻. I actually do it using Pivot Table. I thought that can be done with new function pivotby.

[@excelisfun]

@@Heybat1 Yes, the new PivotBy and GroupBy do not have much capabilities as PivotTable. I hope to do a few videos next month on this topic : )

[@channelbreak6929]

🔥🔥🔥

[@dharmendarrana4191]

How to find missing dates in a set of date ranges by using Excel 2016 formula. For example A1 is date 01-11-2023 02-11-2023 07-11-2023 08-11-2023 09-11-2023 10-11-2023 I want required in this answer {3,4,5,6} .

[@AshwiniLatha-j2l]

Hi mike why don't you teach us SQl, python etc.

[@excelisfun]

I do not. Sorry : (

[@krimbos1]

Interesting, but how is this better than a pivot table? What am I missing?

[@excelisfun]

For 30 years the dividing line between when to use a PT or Formulas is simple: formulas update instantly, Pivots do not. It is not that one is better than the other, it is that they each provide a different analytic tool. For some projects that easy of Pivot is perfect because required solution does have regularly changing inputs. Analytics that have a lot of what if situations, then formulas suit that better.

[@venuchaganthi]

Hi Mike, can you please help me to do XLOOKUP to data in Excel Data Model, without loading data to excel sheet.

[@sleric3]

There should be a function to UNpivot as well.

[@excelisfun]

Maybe soon : )

[@henryg5735]

Nirvana?

[@excelisfun]

So true : ) : ) : )