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 : )
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! 🤓👏
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
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.
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! 🙏
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!!
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
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.
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.
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.
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.
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.
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?
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.
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 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.
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)
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.
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
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
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
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 🤝
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?
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.
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.
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 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)
@@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
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 : )
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} .
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.