Use slicers with PIVOTBY, GROUPBY & FILTER in Excel | Excel Off The Grid

with the announcement of the new group by and pivot by functions in Excel there's been a lot of discussion about whether these new functions will be compatible with slicers because they have pivot table type functionality the good news is yes we can use slices with these functions and I'm going to show you how not only that but this method even works with the filter function so if you're ready let's get started here we are an Excel and this is the data that we're working with our data is inside a table and we have a column called item region size and value if we want to add slices for this table we select any cell in that table then click insert and then come across to Slicer in the insert slicers dialog box we can select which columns we want slices for let's select item and region and then I'll click okay so that has now created those two slices and because this is a table when we click on those those slices it will then filter that table for the items that we have selected here we are on a new worksheet I've already copied across my two slices and now let's enter our pivot by formula equals pivot by Open Bracket the first argument is what values do we want in the rows well let's suggest that we want to have our item value in the rows so from our data table we want our item column the second argument is what values do we want across the columns let's suggest we want the region so again that will come from our data table and we want our region column the next argument is which column do we want to calculate on in the body of our pivot by function we're going to use our values column so again from our data table and we will pick up our value column the next argument is what function do we want to perform we're going to keep this simple today so we're just going to use the sum function this now brings us to the optional arguments the first argument is field headers it asks whether the data that we've provided does that have any headers in this case it doesn't so I'll enter zero the next argument is total row depth this is about whether we have totals and subtotals in this scenario we do want Grand totals so I'll enter one then we have the row sort order this is where we get to declare which row we want to sort on I'm going to enter one so we're going to sort on the first row in ascending order the next argument is the column total depth this is whether we want totals and subtotals to appear in our function let's say we do want Grand totals I'll enter a comma and that brings us to the column sort order going to enter one so that means our column will be in ascending order finally we come to our filter array we're going to come back to this in a few moments time so let's just close our pivot by and press return and make sure that this is functioning correctly so down the rows we have that item column across the columns we have the region column and in the body we have the values column and we've summed those values and you can see that we have totals for our columns and totals for our rows right it's now time to add our slicer into our pivot by formula for this we're going to use the subtotal function so let's come up here to our filter array argument now how this works is that we need to calculate an expression for each row of our source data so an expression for each row of our data table if the value of that expression is true it means that row will be passed into our pivot by function for calculation if that expression is false it means it's not passed into our pivot by function for calculation so how can we do this well we know that subtotal responds to a slicer and will only include the items which have been selected so let's use subtotal Open Bracket now we want to count the rows so we want a value of one if the row is visible or zero if the row is not visible so we're going to use use three so that's the count a option now we can't just subtotal the entire column because subtotal is an aggregation function it only returns a single value instead we need to calculate a value for every Row in our table so we're going to start by placing a placeholder in here of R okay so that's our subtotal piece but as I said we can't calculate subtotal in this way we need to enter this into a brow l Lambda combination so we'll enter by row and the values we're going to use we could use any column from our table but let's use the value column and then we want to enter a Lambda and that Lambda is going to use that parameter of R so what that means is that for each row in our value column it's going to use that as the placeholder of R so now let's close our formula and we'll press return so far nothing's changed until we click on our slicer and you can see that as we select those items that pivot by now updates to just show the items that we have selected so using this brow Lambda and subtotal combination we can make our pivot by function respond to a slicer that's connected to that Source table we're not restricted to just using this function combination with pivot by we can also use it with Filter so I'm going to select this section of the formula I'll press contrl C to copy that and now let's come down to cell E18 and add a filter function equals filter Open Bracket and our array so the first argument will be our data table then we have our include argument these are the rows that we want to include well let's enter our by row Lambda and subtotal combination and if empty let's just enter no values we'll close that function press return and now we have a filter function so if we click on Alpha and East for example you can see that that adds up to 98 so that's what's in our pivot by function in our filter function our values there add up to 198 so we can aggregate our values but also we can see a breakdown of those values using the filter function now you're probably wondering what happens if we get new data do our slicers update automatically well let's find out here we have our data and you can see that we have a new region called Central so I'm going to select all of this additional data and I'll add this to the bottom of the table so that table is now expanded let's come back and you can see that we now have a new region of Central which is also in our pivot by function if we select that fantastic we can see that in our pivot by and we can also see it in our filter function so the answer is yes our slices do update for whatever items we have inside our table and that's it that's how we can use a slicer with our pivot by Group by and filter functions we just need to know that brro Lambda and subtotal combination and we can easily get a true or false value for each row in our data that then determines what's returned by each of those functions if you like this video don't forget to subscribe and I'll catch you next time

Channel: Excel Off The Grid

Views: 10,923

Keywords:

Id: jFU5Cdd7Ys8

Length: 8min 7sec (487 seconds)

Published: Thu Feb 29 2024