NEW Excel Functions - GROUPBY and PIVOTBY [with a Complete Guide] #excel #newfeatures #office365

welcome to this video Microsoft just released two brand new functions in Excel called Group by and pivot by I will show you these two amazing functions with realtime scenarios these functions let you aggregate the data with just one formula and they are very much powerful and easy to use if you have ever struggled with data aggregation in Excel you will love these new functions they have a bit of complex name but they are very intuitive and simple to understand please like this video so that YouTube shows this video for more people thank you let's dive in and see how they work equals to group by it has seven arguments first three are mandatory and the next four are optional arguments we will see first the mandatory arguments and then we will dive into the optional arguments first one row Fields row fields are always the categorical Fields going to select segment comma values values are always measures going to select the sales comma and function function you can choose any one function out of all these functions I'm going to select some these are the three mandate arguments provided close bracket hit enter it identified all the unique items from the segment column and added up the corresponding sales amounts so if you look at the sales column the total is 55,8 27.78 which is equal to 55.8 27.78 in our output since I have provided only single categorical column and a single measure column it returned only two columns in the output let us include two categorical columns segment and Country hit enter so in the output also it returned two categorical columns and one measures it identified the unique record from both segment and Country column and updated the sum to for the respective record similarly we can also include the measures more than one column here I'm going to include sales and profit hit enter the in the output we have got two categorical columns and two measures here if you look at the output we don't have the headers let's open the cell and go to the fourth argument which says field headers here there are four choices zero stands for no headers one stands for yes but don't show headers two stands for no but generate headers three stands for yes and show headers so I'm going to select three here and hit enter it updated the headers also it updated the totals as well and let's open the cell go to the fifth argument which says total depth in this fifth argument we have five choices zero stands for no totals one stands for Grand totals two stands for Grand and sub totals minus one stands for Grand totals at top minus two stands for Grand and subtotals at top so first let me select zero here and see the output we have already totals in the bottom hit enter the totals have been taken out open the cell I'm going to put now Grand and subtotals which stands for two hit enter at each segment there is a sub total you can observe clearly at the same time we have the grand total at the bottom suppose I don't want the sub totals I need only the grand total I can choose one or ignore that argument it removed all the subtotals and kept only the totals open the cell go to the end put one more comma sixth argument sort order suppose if I want to sort by the sales column I need to give number three because the three stands for the third column in the output so sales in the third position we need to give the three here and hit enter this will sort the sales column in ascending order suppose if you want the descending order same column number negative number minus 3 hit enter hit sorts in a descending order seventh argument filter array put comma at theend of the formula suppose if I don't want the mid Market to be to include in my output I can take it out select the segment column not equals to Mid Market hit enter so the mid Market has taken out from the output so this is how as you supply the parameters you can still narrow down the results and you can perform beautiful analysis using using the group by pivot by function equals to Pivot by this pivot by function has 10 arguments in total there are four mandatory arguments and six are the optional arguments first we will check the mandatory fields and then we will dive into the optional arguments here also row Fields will be the categorical Fields I'm going to select the segment column comma column Fields even the column Fields also categorical Fields going to select the product here then comma third argument values this is the measures that we need to select going to select the profit here comma and the function out of these functions you can choose anyone based on your requirement I'm going to select the sum now all mandat fields are supplied then close bracket hit enter the whatever the columns we have selected those are pivoted here if you look at the column column field that we selected the product column so each product spilled across horizontally the profit for each combination of segment on the product have been summed up now let's get into optional arguments after some put comma the fifth argument comes in picture if you want to the headers you can choose based on this headers I'm going to select three yes and show the headers comma a row total in depth this is to update the grand totals and sub totals if you want only the grand total you can give one suppose if you want the subtotals as well you need to select two categorical Fields you cannot select one categorical field and expect the output for subtotals so I'm going to select two categorical Fields here segment and Country I'm going to select Grant and subtotals which stands for two close bracket hit enter now the subtotal is applied for each segment on the grand total also updated seventh argument open the Cell at the end put comma row sort order if I want to sort by segment in a descending order segment is being First Column in the categorical Fields I'm going to give one since I need the descending order I need to give minus one it will sort in descending order can see s came to the top and C has gone down eighth argument if you want the column subtotal you can give here but I don't need colum level grand total or sub total here so I'm going to give zero and column sort order if I want to sort by any column like I have already selected products as a column right if I want to sort the columns in descending order I can give minus one it will sort the columns in descending order you can see the passive came to first and k gone to Lost 10th argument put comma filter array for some reason I don't want to include the Enterprise into my output I can take it out select the segment which is not equal to Enterprise and hit enter Enterprise has been taken out from the output so this is how you can implement the P by function by supplying the parameters one after other P by function helps us to analyze the data in a great way similar to our P table but by looking or by listening to that name please don't afraid of that formula it is more easy and flexible to implement just try to one or two times will get to use now it's time for bonus equals to Pivot by row Fields going to select uh date field comma column Fields select the product and the values select the profit comma the function is sum close bracket hit enter now this is pivoted the data by some coming at each day level I don't need the day level suppose if I need for month level open the bracket the date field un need to wrap it up with the text function comma with double quote mm close double quote close the bracket hit enter now one stands for January all January sales has been summed up next march next April and so on all the months level has been summed up open the cell again put Hy four * y hit enter now it is updated month along with the year if I need by year you can remove this M and hyon H enter now this is summed up at year level so how cool is that so there are so many ways to apply the group by and pivot by functions I hope you have enjoyed a lot with this video and learned a lot to implement in your daily work the only thing I request you all is please try once and get to use it and you will definitely love with this output thank you very much see you in the next video

Channel: Chethana Pixel

Views: 2,080

Keywords: excel, excel pivot table, excel tips and tricks, microsoft excel, excel pivot tables, pivot table excel, pivot table, pivot tables, excel pivot table tutorial, excel tutorial, excel pivot table group by week and month, group by week and month in excel pivot tables, excel 2013, excel online course, advanced excel tricks, groupby excel worksheet function, group by week in pivot table, pivot table group by month excel 2016, how to use groupby function in excel

Id: 6F2SZHVvhxs

Length: 10min 13sec (613 seconds)

Published: Tue Nov 21 2023