Excel PIVOTBY Function to Create Completely Dynamic PivotTable Formula Reports! EMT 1845
HTML-код
- Опубликовано: 15 июл 2024
- Download Excel File: excelisfun.net/files/EMT1845....
You will not believe what you see in this video. See the basics of the new PIVOTBY function to make PivotTable Formula Reports, then see how to create monthly sales reports (with a hack), then see a completely dynamic Formula PivotTable Report where you can use cell inputs to change, Row, Column and Filter Conditions, Change the Function for the report and create a dynamic label that describes the report. Simply Amazing!!!
Topics:
1. (00:00) Introduction.
2. (00:22) PIVOTBY function, complete description and examples.
3. (05:50) Monthly Sales Report with PIVOTBY function.
4. (08:11) Fully dynamic PivotTable report with cell inputs for the criteria and function in the report.
5. (18:57) Summary.
6. (19:23) Closing, Video Links.
#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp #excelformula #excelfunctions #excelfunctions #excelformulasandfunctions #lambda #pivot #pivotby #groupby #pivottable #pivotables
![Sean Rii, Karyon, Sharzkii - Taungule [My Love] (Official Music Video)](http://i.ytimg.com/vi/eJoMuypbSzQ/mqdefault.jpg)
Mike ..lett"s continue ...new emt ...❤
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 : )
I am sorry to hear that..
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! 🤓👏
I am very happy to help. Thank you for your kind words - it means a lot : ) : ) : )
More Excel magic. Thank you Mike.
You are welcome for the magic!!!~!
Sir, Your videos make more more interested to excel, Thank you for your guidance!
Thank you so much Amazing Mike for this EXCELlent video.
You are welcome so much, Fellow Teacher!!!!
King of Excel You Always Super Pass the expectation
That is just what happens when we have too much fun : ) : )
wow, its superb, I'm out of words.
Thank you Mike for covering this :)
Thanks Mike.
Will surely try all tomorrow. Thanks again
You are welcome again, Formula Guy John!!!!!
This is really a magical channel for excel, I learned from you. Thank you very much sir 🙂
You are welcome, ExcelsDive!!!!
Simply put: Amazing vedio
I've been watching your channel for years now, your walkthroughs are the best .
Thank you for your devoted work
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! 🙏
You are welcome a lot!! Notes are good : ) : )
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.
I am so very happy to help. Go Team!!!!!
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.
Mike, thanks again for another epic Excel video!
You are welcome again!!!!
Fantastic work!!!!
MS did do fantastic in giving us these cool function!!!
Amazing video. PIVOTBY and drop-down lists are magical. They work well together to extract required information instantly. Thank you Mike 💚
You are welcome my Ghostly Friend : )
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
You are welcome very much!!!!
Really amazing stuff. Thank you. We need another book
this is super fun and awesome! thanks Mike!
You are welcome!!!
brilliant as always!
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
I 100% agree! Get that marketing department fired up !!!!!
Brilliant video
Glad you like it : ) : )
Huzzah for Mike 😁
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.
Yes, a thousand times better : ) : ) : )
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!!
Love this wording: "obnoxious" default format . So true lol I am a fan too : )
Amazing Master Mike :) Thanks
You are welcome : ) : )
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.
Great!!! Too good is EXCELlent ; )
EPIC and Amazing
Awesome!
Thanks, Teammate!!!!
Absolutely Amazing 😅
Glad you like it!!!
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.
brilliant with a few flaws. I guess we can live with that. Funny: mildly disappointing !!!!
Amazingggg as alwayssssssssssssssss thanks
You are welllllllllllllllllllllllllllllcome!!!! : )
Thanks!
Thank you for the kind donation : )
🔥🔥🔥
Amazing
Glad you like it!!!!
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?
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.
That is a really great idea, Teammate millawitch!!!!
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
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.
The only version that will have them is Microsoft 365 Excel. They have home and professional for that version.
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.
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.
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.
Was PercentOf taken out with the intention of being added back in? I was excited to use that for allocation purposes!
We don't know. It was there for two days and then it wasn't...
Could function textjoin work for the list of functions inside choose?
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!
I did not use an Excel Table becasue the table formula nomenclature looks messy. In real analysis I would use an Excel Table.
@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.
@@richardhay645 Excellent!
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)
Hi Mike,
I tried to check those new forrmulas,
however on my Personal and work PC on betachannel i dont have them :(
Thanks for checking!
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
Beta should be in a week or so. All of 365 in a few months.
Thank you Mr Girvin. It's a pleasure to follow your channel
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
Hi Mike, do you know any reason why my MS365 does not have this Pivotby or Groupby? I have done my latest update.
Delete the function PercentOf?
It was there for 2 days, and then Microsoft took it out. We don't know why
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 🤝
@@ivanmamchych5802 That would be great if they added in all of those options to match standard pivot tables.
Hi Sir ..What kind of Subcription we should have to get these functions..I have 365 Version 2310
He's probably using an insider version (beta tester). In some months we'll be updated.
@@eduardobarbosa1958 Yes.
Any idea why I would not have this formula under Microsoft 365 Apps for Enterprise?
It is still in beta, but should be to all 365 soon : )
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.
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.
@@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
@@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.
@@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 : )
What version excel is this ? It doesn’t appear as an option for me
We are using office 365
It has been in beta in Microsoft 365 for a few months and should be out soon in 365.
Did they get rid of it? I don't have this formula.
It is in beta but should be in all of M 365 Excel soon : )
@@excelisfun Got it. Thanks so much for responding.
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} .
All these dynamic formulas are great except there is no way to have dynamic formatting too
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 : )
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 mike why don't you teach us SQl, python etc.
I do not. Sorry : (
Nirvana?
So true : ) : ) : )
There should be a function to UNpivot as well.
Maybe soon : )
Hi Mike, can you please help me to do XLOOKUP to data in Excel Data Model, without loading data to excel sheet.
Interesting, but how is this better than a pivot table? What am I missing?
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.