Pros Use This Technique to Avoid PivotTables

if you find pivot tables a bit daunting then tables are a great alternative because they can also be filtered with slices to create an interactive report and with the right functions you can aggregate the data while ignoring the rows that are filtered by the slicer let's take a look here I have some sales data by segment country product and date it's already formatted in an Excel table and on the contextual table design tab of the ribbon you can see the table name is financials now if your data isn't already formatted in an Excel table you can use the keyboard shortcut contrl T to convert it now there are about 700 rows of data which is way too much to make sense of just by looking at the table I'll make some space at the top to insert some slices and with the table selected on the table design tab of the ribbon I can insert a slicer here I can choose the columns I want slices for these are the columns that I want to filter I'll have segment country product and month now with one already selected I can contrl a to select them all and move them to the top of my table let's reduce the height and width so they fit in the space available I'll move the month slicer to the edge of the table header space and I'll set their slicer to two columns then holding shift I'll select the product slicer and reduce the height of the buttons so they fit without the scroll bar and R A again to select them all and on the slicer tab under a line I can distribute them horizontally and a line to the top so while I have them all selected I'm going to change the color to match my table so from the slicer Styles drop down okay that's formatting done as I select items in the slice of the table filters accordingly for example if I want to see January sales for amarilla you can see there are five results and notice the slicer button formats change to indicate when there's no data for an item for example the segment slicer has Channel partners midmarket and small business in a light shade of blue and we can see in the table that those segments are not present as you select more items the table is further filtered that is slices are additive also notice the row numbers in the table at Blue to indicate these rows are part of a filtered data set I can clear the slice of filters one by one by clicking on the clear filters button in the top of the slicer or on the data tab I'll select a cell in the table and then clear all filters I actually have this button on my quick access tool while cuz I use it all the time you can right click to add it it's great out for me because I already added it now one of the limitations of slices for tables are date slices unlike pivot tables that can group dates into months and years tables don't and the timeline slicer that works for pivot tables doesn't work for table this is why I've added columns to my table for month and year alternatively you could add a column for the year and month together for example I can use the text function to take the date and format it y y y y- mm the table automatically includes this new column in its range let's rename it year month and I can insert a slicer for this new field notice it's automatically sorted in ascending order one of the great features of pivot tables is the way they automatically summarize the data to get totals counts averages Etc let's look at how we can do this with tables here I have two slices for country and segment and I've turned off the slice I headed to Safe space check out my slicer formatting video linked to here for more tips I'll filter the table for Canada and small business just so it has less rows on the table design tab I can add a row total and choose an aggregation type from the drop down let's leave it at some now if you look in the formula bar you can see it has inserted the subtotal function subtotal ignores rows hidden by filters so you can change Selections in the slices and it automatically updates which is cool however having the totals at the bottom of the table is a bit inconvenient instead I can use the subtotal function at the top outside of the table for example here let's let's get the total sales equals subtotal the first argument is the aggregation type that you want numbers 1 through 11 include values on hidden rows and numbers 101 through to 111 ignore values on hidden rows now rows hidden as a result of filters like I'm using here are ignored with either set of function numbers so we're only talking about rows that are hidden manually with the right click ey rows so I'm going to choose nine for some and I'm going sum the sales column and now my total is at the top of the table I can happily change the slic of selections and the formula updates accordingly shout out to John Vaden for this tip thanks John another function we can use that allows us to choose whether to ignore filtered rows or not is the newer aggregate function I say newer but it's been around since EXL 2010 let's say I'd also like to see the average sales we'll use Aggregate and as you can see it has even more op options to choose from I'll go with one for average then the next argument lets me specify what to ignore it's the opposite of how subtotal works that is you have to specify if you want to ignore filtered rows and any rows hidden manually are always ignored I want to ignore hidden rows so I'll choose one which also ignores other subtotal and aggregate formulas in the table not that I have any but just in case someone changes something in the future future this is going to prevent them double counting it let's move the tool tip out of the way and select the sales and selecting items in the slices feeds through to the formula result pretty cool one of the limitations of slices for tables is they can only filter one table unlike pivot tables you can't connect them to multiple tables at the same time the other challenge with slices is they take up a lot of space so check out this video on how to format slices and make them small neck thanks for watching I'll see you in the next video

Channel: MyOnlineTrainingHub

Views: 109,747

Keywords:

Id: j5hQ3WAx6sI

Length: 6min 38sec (398 seconds)

Published: Tue Nov 21 2023