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
8 ай бұрын
I am sorry to hear that..
@eduardobarbosa1958
9 ай бұрын
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
9 ай бұрын
I am very happy to help. Thank you for your kind words - it means a lot : ) : ) : )
@vishal.pandey2001
8 ай бұрын
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
8 ай бұрын
You are welcome very much!!!!
@excel_along_the_way
9 ай бұрын
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.
@tibibara
9 ай бұрын
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
9 ай бұрын
You are welcome a lot!! Notes are good : ) : )
@richardhay645
9 ай бұрын
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
9 ай бұрын
Love this wording: "obnoxious" default format . So true lol I am a fan too : )
@dschmid8845
9 ай бұрын
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
9 ай бұрын
Yes, a thousand times better : ) : ) : )
@SunilYadav_ExcelsDive
9 ай бұрын
Sir, Your videos make more more interested to excel, Thank you for your guidance!
@roywilson9580
9 ай бұрын
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
9 ай бұрын
I 100% agree! Get that marketing department fired up !!!!!
@kiwikiow
9 ай бұрын
Amazing video. PIVOTBY and drop-down lists are magical. They work well together to extract required information instantly. Thank you Mike 💚
@excelisfun
9 ай бұрын
You are welcome my Ghostly Friend : )
@user-qj7hn1ps9v
9 ай бұрын
King of Excel You Always Super Pass the expectation
@excelisfun
9 ай бұрын
That is just what happens when we have too much fun : ) : )
@SyedMuzammilMahasanShahi
9 ай бұрын
Thank you so much Amazing Mike for this EXCELlent video.
@excelisfun
9 ай бұрын
You are welcome so much, Fellow Teacher!!!!
@Jim-zm6fw
9 ай бұрын
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
9 ай бұрын
I am so very happy to help. Go Team!!!!!
@ExcelsDive
9 ай бұрын
This is really a magical channel for excel, I learned from you. Thank you very much sir 🙂
@excelisfun
9 ай бұрын
You are welcome, ExcelsDive!!!!
@samirkapadia3423
8 ай бұрын
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
8 ай бұрын
Great!!! Too good is EXCELlent ; )
@ricos1497
9 ай бұрын
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
9 ай бұрын
brilliant with a few flaws. I guess we can live with that. Funny: mildly disappointing !!!!
@johnborg5419
9 ай бұрын
Thanks Mike. Will surely try all tomorrow. Thanks again
@excelisfun
9 ай бұрын
You are welcome again, Formula Guy John!!!!!
@markpodesta4605
9 ай бұрын
More Excel magic. Thank you Mike.
@excelisfun
9 ай бұрын
You are welcome for the magic!!!~!
@bonmonil
9 ай бұрын
Simply put: Amazing vedio I've been watching your channel for years now, your walkthroughs are the best . Thank you for your devoted work
@thebhaskarjoshi
9 ай бұрын
Mike, thanks again for another epic Excel video!
@excelisfun
9 ай бұрын
You are welcome again!!!!
@millawitch
9 ай бұрын
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
9 ай бұрын
That is a really great idea, Teammate millawitch!!!!
@tecwzrd
9 ай бұрын
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
9 ай бұрын
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
9 ай бұрын
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.
@edge5817
9 ай бұрын
this is super fun and awesome! thanks Mike!
@excelisfun
9 ай бұрын
You are welcome!!!
@davorercegovac3674
9 ай бұрын
Amazing Master Mike :) Thanks
@excelisfun
9 ай бұрын
You are welcome : ) : )
@reng7777
8 ай бұрын
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
8 ай бұрын
The only version that will have them is Microsoft 365 Excel. They have home and professional for that version.
@anthonygeo3
9 ай бұрын
Brilliant video
@excelisfun
9 ай бұрын
Glad you like it : ) : )
@syrophenikan
9 ай бұрын
Fantastic work!!!!
@excelisfun
9 ай бұрын
MS did do fantastic in giving us these cool function!!!
@ericrobbins6953
9 ай бұрын
Really amazing stuff. Thank you. We need another book
@snipelite94
9 ай бұрын
Huzzah for Mike 😁
@ExcelInstructor
9 ай бұрын
wow, its superb, I'm out of words. Thank you Mike for covering this :)
@AnandGautam9901
9 ай бұрын
Absolutely Amazing 😅
@excelisfun
9 ай бұрын
Glad you like it!!!
@thelastfry23
9 ай бұрын
Was PercentOf taken out with the intention of being added back in? I was excited to use that for allocation purposes!
@excelisfun
9 ай бұрын
We don't know. It was there for two days and then it wasn't...
@paser2
9 ай бұрын
brilliant as always!
@carolines2232
7 ай бұрын
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
@bonmonil
9 ай бұрын
Thanks!
@excelisfun
8 ай бұрын
Thank you for the kind donation : )
@mohamedchakroun4973
9 ай бұрын
Amazingggg as alwayssssssssssssssss thanks
@excelisfun
9 ай бұрын
You are welllllllllllllllllllllllllllllcome!!!! : )
@pmsocho
9 ай бұрын
Awesome!
@excelisfun
9 ай бұрын
Thanks, Teammate!!!!
@chrism9037
9 ай бұрын
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
9 ай бұрын
I did not use an Excel Table becasue the table formula nomenclature looks messy. In real analysis I would use an Excel Table.
@richardhay645
9 ай бұрын
@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
9 ай бұрын
@@richardhay645 Excellent!
@ExcelInstructor
9 ай бұрын
Hi Mike, I tried to check those new forrmulas, however on my Personal and work PC on betachannel i dont have them :(
@excelisfun
9 ай бұрын
Thanks for checking!
@aruannovincenzo
9 ай бұрын
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
9 ай бұрын
Beta should be in a week or so. All of 365 in a few months.
@aruannovincenzo
9 ай бұрын
Thank you Mr Girvin. It's a pleasure to follow your channel
@aruannovincenzo
9 ай бұрын
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
@ExcelHechoFacil
9 ай бұрын
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.
@matthewhart3719
9 ай бұрын
Amazing
@excelisfun
9 ай бұрын
Glad you like it!!!!
@blahdelablah
9 ай бұрын
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?
@ExcelHechoFacil
9 ай бұрын
Delete the function PercentOf?
@excelisfun
9 ай бұрын
It was there for 2 days, and then Microsoft took it out. We don't know why
@ivanmamchych5802
9 ай бұрын
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
9 ай бұрын
@@ivanmamchych5802 That would be great if they added in all of those options to match standard pivot tables.
@HusseinKorish
9 ай бұрын
EPIC and Amazing
@xlisgr8
2 ай бұрын
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)
@mauhernan
9 ай бұрын
Could function textjoin work for the list of functions inside choose?
@rtrbs8383
9 ай бұрын
Hi Sir ..What kind of Subcription we should have to get these functions..I have 365 Version 2310
@eduardobarbosa1958
9 ай бұрын
He's probably using an insider version (beta tester). In some months we'll be updated.
@excelisfun
9 ай бұрын
@@eduardobarbosa1958 Yes.
@Heybat1
6 ай бұрын
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
6 ай бұрын
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
6 ай бұрын
@@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
6 ай бұрын
@@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
6 ай бұрын
@@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
6 ай бұрын
@@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 : )
@JonathanCodorniu
8 ай бұрын
Any idea why I would not have this formula under Microsoft 365 Apps for Enterprise?
@excelisfun
8 ай бұрын
It is still in beta, but should be to all 365 soon : )
@user-ix1sh6ig8m
9 ай бұрын
Hi Mike, do you know any reason why my MS365 does not have this Pivotby or Groupby? I have done my latest update.
@patricklonski
4 ай бұрын
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
4 ай бұрын
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.
@channelbreak6929
6 ай бұрын
🔥🔥🔥
@OliverH_ATL
6 ай бұрын
What version excel is this ? It doesn’t appear as an option for me We are using office 365
@excelisfun
6 ай бұрын
It has been in beta in Microsoft 365 for a few months and should be out soon in 365.
@dharmendarrana4191
9 ай бұрын
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} .
@monique1112223
8 ай бұрын
Did they get rid of it? I don't have this formula.
@excelisfun
8 ай бұрын
It is in beta but should be in all of M 365 Excel soon : )
@monique1112223
8 ай бұрын
@@excelisfun Got it. Thanks so much for responding.
@aleb8243
9 ай бұрын
All these dynamic formulas are great except there is no way to have dynamic formatting too
@excelisfun
8 ай бұрын
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 : )
@sleric3
9 ай бұрын
There should be a function to UNpivot as well.
@excelisfun
9 ай бұрын
Maybe soon : )
@user-ws9zk8zl6i
8 ай бұрын
Hi mike why don't you teach us SQl, python etc.
@excelisfun
8 ай бұрын
I do not. Sorry : (
@venuchaganthi
9 ай бұрын
Hi Mike, can you please help me to do XLOOKUP to data in Excel Data Model, without loading data to excel sheet.
@krimbos1
9 ай бұрын
Interesting, but how is this better than a pivot table? What am I missing?
@excelisfun
9 ай бұрын
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.
Пікірлер: 129