stop using pivot tables use this new Excel function [Music] instead up till now if you had a table like this and your manager asked you to find the sales by country you would simply head over to insert and click on pivot table from here let's say we want it in an existing worksheet somewhere down over here and click on okay then we would get all the pivot table fields and we would select the country as our row and take the sales as our values and you can see we get the full breakdown of the sales by country the problem with this method is when the data updates for example let's suppose that I change the value over here and add three more zeros just so it looks very big you'll notice that nothing actually happens on the pivot table that's because every time there's an update we need to go to pivot table analyze and click on refresh and only then can we see the new value same thing goes down over here if I added a new row I would need to refresh it every time that's a bit problematic luckily we now have an alternative to the pivot tables which is the group by function in Excel and you can download this exact same table in the video description to follow along and keep in mind that the group by function is still being rolled out so it may not be available in your Excel file just yet but let me show you how it works suppose we want to find the same thing so it's going to be the sales by country we will type equals Group by hit the top key there and the row fields are simply all of our rows which would be our countries we're going to select them with control shift down you'll notice it now says table one and that's because we have this formatted as a table comma the values are all of the sales figures that's what we're interested in we'll select that with control shift down comma and the function what do we want well we want the sum of Revenue by country so we'll select the sum there by hitting the toab key close up parenthesis and hit enter and just like that you can see we have the exact same breakdown but the key difference is when there's updates over here let's suppose we remove the 3 Z that we added earlier and hit enter you'll notice that this upper part with the new function updates automatically but the same can't be said for this pivot table down below same thing goes if we were to add a new country here let me just add a new name quickly so you can see I've added a new row for Romania and added some other details there and you can see that Romania is now inside of our list up here but that's not the case in the pivot table down below now let's be honest pivot tables can do a lot more than just that so let's go over a harder example for the group buy and in this case let's suppose our manager is asking for the sales by country but also for the percentage of total with a pivot a table that's super easy we just need to drag and drop the sales again over to the side and for the second column I can right click show values as percentage of grand total and you can see there we have the exact numbers keep in mind we need to refresh this so I'm going to go to pivot table analyze and click on refresh now that's looking updated however to do that in the group bu it's slightly different but it can definitely be done for this right here where it's the function part we need the sum and the percentage of total as well so we're going to be able to include both of them with the h stock formula open up the parenthesis we first want the sum as array number one comma and the second array is going to be the percentage of this is the percentage of total basically we're going to delete that parenthesis so we have it fully wrapped up for the h stock close the parenthesis again this time for the group by function and hit enter now you can see we have the percentage of total and if I format this as a percentage we should have the exact same values as below as it seems to be the case awesome before we continue with even cooler features of the group by function we should all be on the same page when it comes to data analysis and a great way to guarantee that is using hubspot's free introduction to data analytics report they're kindly providing this 50-page PDF completely for free free which you can download using the link in the description below in the file you'll find a comprehensive breakdown of what data analysis is what types of data analysis there are and some best practices as well it's not just a report full of text though there's also supporting visuals to make sure you understand this resource is great if you're a beginner as well as if you've already taken a fair share of statistic classes like myself I personally find this most most useful to refresh my memory on the key terms and techniques when it comes to data analytics so I recommend you check this out in the link in the description below to download this completely free guide from hopspot to level up your data analytics skills and thanks to them for sponsoring this video back in Excel here and I've simplified the group by a bit more simply to have the sum at the end instead of the sum and the percentage of like before and now suppose our manager wants us to get rid of the total Tes and also add headers up top for this we can customize further by looking at all of these inputs that have parentheses these square brackets these are the ones that are basically optional so if we hit on the comma key there for the field headers these are all of the headers that we have in dark blue here and we have a few options no means we don't have any then yes but don't show is just hiding them and now we want to go to yes and show because we want to show those hit a three comma and the total depth is the breakdown of whether we want the totals or not in our case let's put a zero because we don't want any totals and hit enter to see what that looks like you can see we now have the headers but we don't have the totals down below getting rid of the totals doesn't make that much sense here but for something like the average price let's suppose we selected instead of the sales here I'm just going to delete all of this area and select the prices in USD instead and let's say we wanted the average hit the top key and hit enter now for something like the average price it doesn't make much sense to have the sum of the totals as these are just averages so getting rid of the total there can be quite handy this group by function still has so many more features so let's go over another one let's say our manager is asking us to sort all of these values so they're a bit easier to read for that we would get back inside of the formul hit a comma again and now we have the sort order and how this works is if we were to put a one it sorts based on this first column now if we were to put a two it sorts based on this pricing one so that's the one we're interested in let's say I just put a two and hit enter now you can see that it's going from low to high if we want it to reverse that we can put a minus instead so a minus 2 now it's going from high to low as you can probably imagine we can also do things like changeing the number of decimals like you would with any other formula we show this to our manager and now he wants to know not just the sales by country but also the sales by country and product so for this we're going to delete this formula entirely and we need to move this down as it might be in the way so I'm going to press crl X after I select it all and just paste it down over here with contrl v that's going to move it lower down and we'll do another group buy hit the Tab Key and the row fields we don't just want the country anymore we also want the product so we can select both of them simply by control shift down and then shift right you can see we have both selected comma the values is we're interested in all of the sales comma and for the function we want the sum of them we'll close the parenthesis and hit enter now you can see we have the values over here to the side let me remove that formatting and make it all more consistent simply by adding commas and no decimals so right now we've added two row values for Coca-Cola we get the full breakdown by country and same thing goes with all of the other products if you thought up till now it was still fairly simple the group by function still has more and that's in the form of filtering let's suppose that our manager is telling us to get rid of Powerade as that's a product will be discontinuing so we don't want to see it in the totals well for that we would go back inside the formula and if we take a look there's the filter array at the end to get all the way there we're going to need to put the comma key a few times all the way till that part gets bolded so here we are in filter array and here we want to say that hey out of all of the products that are currently available we'll select all of those as you can see there we don't want it to equals and we do that with this greater than and smaller than sign two in quotations power raid close the quotations and hit hit enter and you notice how Power Aid is now gone right now we did not equals to but as you can imagine we can't change that to let's say equals to just with the equal sign and instead of Power Aid let's suppose we go for Fanta and hit enter now we only get the breakdown for Fanta if you use pivot tables often you might have realized there's one thing that's missing we take a look over here and we select this pivot table you'll note that we have columns to the side for example we could have the country on one side and the product on the other like so and we could get that full breakdown that's not really the case with the group buy it's not an option in here to add the column that's where the pivot buy formula comes Hy so let's take a look at it we're just going to delete this one and type equals pivot bu hit the top key and you'll notice this is exactly the same as the group byy except there is the column Fields as well well so we could select all of the countries as the row Fields comma select all of the products as the column Fields with control shift down comma and let's say we just want the sum of sales so we'll select all of the values so the sales values here comma and when has the function we'll take the sum closeup parenthesis and hit enter and now you can see we get the exact same breakdown as down below and maybe surprisingly here it seems like only the US is is selling all of the products we have available to show you a better example of when the group buy could be used let's head over to this other sheet and you'll notice that we now have a breakdown by quarter so I'm just going to add the pivot by function over here a bit more quickly and we're going to put the quarters as the column field so I would select let's say all of the countries comma the porters as the columns comma and as the values let's say we just want the same sales and the function is going to be the sum of sales close a parenthesis and hit enter you can see what that looks like where in q1 we were missing two countries but it seems like we're expanding a lot more as in Q3 things are looking a lot better so this top part can be really useful for something like the years the months or the quarters awesome if you think you have a good grasp of excel challenge Yourself by watching this video one of the things that most people get wrong in Excel or take our Excel course to continue improving over here hit the like and that subscribe and I'll catch you in the next one