Pivot Table Super Trick in MS Excel | Excel Tutorials 2021

greetings everyone I am Rashad Puglia from Excel X training solutions in the next 2 minutes I am going to show you a couple of tricks from pivot table and this is especially for those professionals who are using pivot table but may not have seen this super super trick now to illustrate that point I am going to take an example of a day down which is roughly 60,000 lines now here I have a company which sells equipments sporting equipments in terms of bikes clothings components to the different customers which are based out of North America so these are the customer names and have particularly highlighted those heading which are relevant for our report so for example customer the main business segments which has been sub categorized into category the model number of the equipment the color of the product the sales date the sales date and you see other items including order quantity and sales amount now let me make a quick pivot table before I show you the couple of tricks let me use control a to be able to choose the data and I'm going to insert tab clicking on pivot table the box that comes up next on my screen simply is a confirmation of the selection and I press ok now once this pivot table is created let me activate one of my favorite settings which actually allows me to be flexible with both both the functionalities of Excel 2003 and 2007 with respect to pivot tables so pivot table options I go to display and here lies an option saying classic pivot table layout which will enable you to drag these fees the fees which are on my right to the grid which is present on the left let me press ok now here it goes let me put the business segments on the row field so these are the 4 business segments let me put category under them so either I can put under the row fields in the same section else I can put the category under rule labels under this business segment now let me put order quantity in this area this is what I offer as mean action area we could all all mathematical numbers are presented in this main action area now I want some detailed report in terms of the early trends of sale so I'm going to put sales date see his date on top of this now this is what this is column label or column field in case I put that in column label is this the same thing that you're going to get now look these are individual dates these are not of my help because I want a summary report so I right-click on any date mind you right-click on any date and you'll get to see something called grouping once I go to grouping you will be amazed that Excel pivot table has this ability to group these numerous dates in two years or months or both let me pick up only yours as of now oh I have your wise product category wise the quantity that I have been selling over the past couple of years now this is not the main trick the main trick is yet to arrive let me put customer under page field now this page field is the same as report filter so instead of putting the customer under page fields I could have put customer under report filter now just be careful about this name report filter there's something unique about this name report filter why because at the outset it appears that I can choose a one client and the result is going to be based on that but wait this is not my target my target is to create the sheets individual sheets based on individual customers and each sheet should represent the data of that individual customer now is there are 600 plus customers can you imagine how much time that is going to take to create those sheets to name those sheets and to copy and paste the data from here on you will never spend more than a minute on such data here's how let's go to options that's the pivot table option within which let's click on this small drop-down please note I am NOT clicking on the word options if I click on the word options this dialog box that comes up is of no help to me right now let me click on the small dropdown now wait there's a super option that lies underneath this option called show report filter pages now what is report filter report filter is where I have given customer so if I substitute report filter with customer how does it how's it being read show customer pages as I press on this option it says show report filter pages based on the customer now let me press the magic button okay you would have never imagined what is going to come up next hmm no my computer is not hyung and this is individual sheets being created for each and every customer and each sheet the sheet tab is named based on the customer and each sheet represents the data only of that customer so while I am completing my sentence 600-plus sheets are being created on your pivot table imagine the application if you wanted to create sheets based on vendor based on products based on customer based on region based on the different sections of the taxes that are being deducted as you go ahead and make the transactions the applications are immense so do go over this video once or twice make sure you know how to use this particular option just make sure that before you activate the option the report filter pages must have some heading based on which Excel knows how to create the different sheets so let me give some extra time and by the end of few seconds you'll see the entire set of reports being created IMing patient in real life I would have gone and grabbed a cup of tea and while I returned as soon as I returned you'll notice the different sheets that are being created and each sheet each sheet named accordingly as I expected and each sheet pertains to the data only of that customer now isn't that wonderful so quickly let me just recapitulate what I did I must activate report filter it must have something near based on customer or maybe the product name and once you have had that in place good option click on this drop down and you must click on this option so if you liked it do share this video with everyone who you think we'll find this useful thank you signing off this is Richard Buccola from xlx trading solutions

Channel: ExcelNext

Views: 1,649,430

Rating: 4.9018941 out of 5

Keywords: pivot table, excel, pivot table in excel, pivot tables, excel pivot tables, how to use pivot table, pivot table tutorial, create a pivot table, excel tutorial, excel pivot table tutorial, pivot table excel, excel pivot table, how to use pivot tables, create pivot table, microsoft excel (software), how to make a pivot table, MS Excel - Pivot Table Super Trick, excel 2019 new features, Pivot Table Super Trick in MS Excel, Excel Tutorials 2021, pivot table excel advanced

Id: yUndp9hJ_0c

Length: 6min 48sec (408 seconds)

Published: Sun Apr 06 2014