Is this Brand NEW Function Better than Pivot Tables?

have you checked out this brand new Excel function and will it make ditch pivot tables let's take a look and let me know in the comments if you prefer this over the pivot tables or not say your boss wants to know the sum of the total sales by City And sales rep well the usual way to do this would be to create a pivot table that's old school let's look at how we can do this by using a new group by function to do the same in a blank cell type equals Group by and then open the parenthesis this function has seven arguments with which can be used but only three of them are required and the other four are optional as they're in square brackets for the Ray Fields simply select the columns you want to aggregate up for example if you want the total by city just select the city column and if you wanted it by City And sales rep you can select both press comma to move on to the values argument this is just the values column you want to perform the action on in this case that's the sales value column the last required argument is the function the options in here are similar to those that you find in the subtotal or aggregate functions for this example we will use the sum function close out the parenthesis and press enter and there you go check it out as quick as that we've got the same as we'd get with a pivot table and one thing which is really neat is that because this is a function it's fully Dynamic save foam Simpson had a really crazy sales day it automatically upd dates but with a pivot table you need to refresh each time your data changes now let's take a look at a couple of the optional arguments the first one is the field headers which there are a number of options I'll select three for yes and show this is currently made the first line in the data the headers CU we first need to make sure that for the row fields and the values we include the headers in the selection which you can do by clicking on the columns twice the next one is a total step which is all around adding totals and subtotals a two in this argument will produce a subtotal for each City and a grand total at the bottom the last optional argument we'll take a look at is the sort order the values for this function appear in the third column and we need to enter three in for this argument a positive three will sort by smallest to largest and a neg3 will sort by largest to smallest there you have it I'm still not sure that it will make me ditch my of the tables just yet but let me know what you think

Channel: Dave Sylvester

Views: 31,887

Keywords: microsoft excel, excel tricks, excel formulas, excel tutorials, advanced excel tricks, excel for analysts, excel tips and tricks, ms excel, worksheet event, excel, excel function, functions, excel for beginners, pivot tables, group by, advanced excel, microsoft, new functions, new function

Id: fQvfXsJpTT8

Length: 2min 34sec (154 seconds)

Published: Fri May 10 2024