I bet YOU DON"T KNOW all these MIND BLOWING PIVOT TABLE TRICKS! 🤩

in today's video we are talking about 10 awesome pivot table tricks now I would want you to do two things for me by the end of this video number one please let me know which tricks did you not know of and number two I have a couple of more tricks that I'd like to share apart from the ones that I'm going to be discussing in this video let me know in case you would like to take a look at those as well only if you comment I will then make another video talking about more awesome pivot table tricks all right no further Ado let's start [Music] pivot table trick number one let's just say that I have this simple pivot table in the First Column I have region the customer the total revenue and the total profit right here and I would like to apply the filter to total revenue and total profit now total revenue and total profit are calculations which I have dragged in the values section of the pivot table and because those are calculations I cannot apply a filters like a native filters on these two columns but I would like to apply a filter how do I do that in case you are selecting the headers of the column going back to the data tab trying to apply the filter right here the filter is going to be grayed out and you cannot apply the filter so what do you do what you're going to do is you're going to step outside of the pivot table in the cell which is blank adjacent to that and then apply the shortcut to apply the filter Ctrl shift l or in case you are the mouse type of a person go to the data Tab and then apply the filter and voila what do you get you get the filters on the calculations as well now in case you would like to get rid of the filters you will also have to get rid of the filters the same way in Excel of the pivot table and then go back to the data tab using a shortcut whatever you like just remove the filters from there and they are gone awesome pivot table trick number two this is pretty awesome take a look here we have the customer column and we have the region column and maybe I'd like to order the regions in a custom order I would want to have the West first then I'd like to have the South and then the rest of the two regions so I'd like to reorder these regions how do I do that the easiest way to reorder is to start typing the region wherever you'd like it so in the first one I'd like to have West I would just start typing West and commit to that and everywhere it has changed to West the next one was I believe South I just do that and maybe North I just do that and everywhere in all the sections of the customers the regions have changed their order this is awesome trick number three saving the custom sort order in Excel what do I mean by that now let's just say that I have the months here running from January up until December and maybe I'd like to sort the months not in the month order January December but in the Indian Financial year order that means we start in April and we end up till March how do I do that now sure enough according to the previous trick you can just maybe go ahead and type APR as the first month then May June July so on and so forth and by the end you will have March but that's going to be a lot of typing and I'll have to do it every single time I create a pivot table why don't we store that as a custom sorting list in Excel and let Excel do its job how do we do that I'm going to create a manual list in Excel so I'm just going to copy all of these Ctrl C on that and paste that right here just a manual list and gen Feb March come right here so this is the order that I would want April up until March I have created one list let's store this list as a custom sorting order in Excel how do I do that I'm going to go over to file file in the file I'm gonna go over to options in options I am going to go over to Advanced in advance I'll scroll down right at the bottom to take a look at something like edit custom list I press on that now here is where I can click on this list that I have created click on import the list is actually going to come right here and then from here I can just click on ADD and the list gets added right here now you can see that since I have already stored the list my list is already added but in case you're doing it for the first time it's going to be added all right once the list has been added I click on OK and we are good to go and now if you just maybe go over to the header and apply the filter and say that hey I'd like to sort this by the oldest and newest this is going to get sorted in the order that we defined in the custom list pretty awesome trick Number Four showing items with no data what do I mean by that please take a look a simple pivot table I have months right here and total revenue I also have a slicer right here that is selecting a customer called India Trotters now let's just start to read the pivot table so I have Jan sales Feb sales March April May June July August there is no August right here now when you're reading the pivot table in the flow like a user you would probably miss out that this data doesn't contain August it has just 11 months of data so in order for me to show that the customer did not give us any Revenue in the month of August I need to have August but the month of August should be blank how do I do that I'm going to maybe select on this particular field right here right click and I'm going to say field settings in field settings I'm just going to go over to layout and I'm going to say that even if you don't have the data please show items without any data as well I'm gonna say okay and now is where I also see the orchids it's also showing a couple of junk entries which I can certainly remove so uncheck that and uncheck that and now this pivot table shows me items with no data as well trick number five how do you do commonly done business calculations within the pivot table let me just try to help you understand what am I trying to do the first calculation in this pivot table which is where I have the ears the month and the total revenue that I would like to do is the YTD calculation that means that I'd like to add a y to D column right here and in that I'd like to have like a running total so when I'm doing the Vitality for the month of March I'd like to add these three numbers up and show it right here against June I'd like to add the first six numbers up shown right here so on and so forth and the YT should stop right here and then it should start again from the next year that's what I would like to do Vice D is a very commonly done calculation in the business how do you do that in the pivot table so what am I going to do I'm going to go back to my pivot table field list right here and take the amount column the amount is nothing but presented as total revenue so same thing I'm just going to take the amount column put that in the value section of the pivot table once again now the same number show up once again but hey I don't really want to take a look at the same number I want to have the Y to D I can just change the nature of the calculation right click on that show the values as I'm going to say that this is going to be a running total right here and the run total is going to be for the months I'm just going to say okay and this transforms the calculation into nothing but a YTD of course you can change that to y2d revenue and we are good to go the next calculation that I'd like to speak about is growth over last year so how do you do growth over last year take a look at these two numbers the month of January in the year of 2006 had reported one lakh 40 000 of sales 140 000 and in the month of January of 2005 it had 150 000 so the sales have decreased as compared to the last year I would want to show maybe the losses compared to the last year how do I do that now in case you were working with Excel you will probably come here and write a formula so this cell divided by the previous cell and minus one this is what you would do and and show the negative six and a half percent growth but there is a way to do that in the pivot table what I can do is I'm just going to go back to the pivot table in the field list I will take the amount column and drag that to the value section once again now I get the same numbers once again however I don't really want to show the number I want to show the growth over last year how do I do that let's just transform this calculation right click and I'm going to say show values as and I'm going to say percentage difference from Once I do that it just shows me this field list right here this selection list right here I'm going to say that the based field is going to be the years and the base item is going to be the previous that means I'm trying to compare the current year with the previous year as simple as that click on OK and this is nothing but your ear on ear growth percentage how awesome is that trick number six doing top analysis using a pivot table now let's just take a look at this pivot table we have ear we have the customer and the total revenue right here now at the moment I have have like 15 customers right here but your customer list could probably be very very long now in order to contain the pivot table maybe I'd like to show not all the customers but the best performing or the worst performing customers there are a couple of ways to do that what you can do is maybe just go over to the customer column right here click on the drop down value filters and I'm going to say something like a top end once I go to top end I'm going to say hey I'm looking at only top three customers these are going to be individual items based on the total revenue column right here click on OK and what you're going to get is top three customers for every single year only pretty awesome trick number seven storing the default layout of the pivot table now one of the most frustrating things in the pivot table is that every single time you create it and if you have a custom layout the way you would like to see the data in the pivot table you will have to do that over and over again guess what you can store the default layout that you would want and the memory of Excel and Excel is going to create the same pivot table over and over again so how do we do that what I'm going to do is I'm going to maybe just format the pivot table a bit so I'm going to go ahead in the design tab in the design tab I'm going to say that the report is going to be in the table format so that the year and the sales rep come in two different columns maybe I also don't really want to have the column total right here so I'm just going to go over to the design tab in the grand totals I'm going to say that the roto tools is something that I don't really want so Grand totals uh on for columns all right once the totals have been removed maybe this is the layout that I would want and I'd like to store this layout in the memory of Excel how do I do that I'm going to click on the pivot table come to file right here go to options Excel options and go over to data and this is where I can store the default layout of the pivot table so I'm going to click on edit default layout it just gives me a box I can just import the layout currently from my pivot table which is made in my screen or I can also mention the different items of the pivot table that I'd like to customize so maybe I just click on this pivot table right here click on import it just updates all the settings I can click on OK and that pivot table layout is going to be stored in my Excel the next time I create a pivot table it's going to be exactly in this particular layout trick number eight is repeat the labels but only for one selected column what do I mean by that take a look so here I have a slightly more dense pivot table we have the First Column as ears the second column as month then we have the region the sales rep and then we have the revenue made by that sales rep now what I what I want to do is in this pivot table I would want to pull down the region to the blank cells like fill it down fill it down fill it down but once I do that I don't really want to fill down the months or the years I only want to selectively fill down the region not the months not the years in case you already know that you're probably going to click on the pivot table go to the design tab in the report layout you're going to say repeat all the item labels but once you do that all the item labels of all the columns get repeated however there is a trick to that what you can do is right click on the region and say that you would like to go to field settings since we are only trying to drop down the region so in the region I'm gonna go over to the layout and print and this is where I'm gonna say repeat the item labels only for the field on which we have right clicked I'm going to say okay and the region is the only field that gets repeated pretty awesome trick number nine remove the plus and minus signs I have seen a lot of times people taking the data copying the data of the pivot table doing a paste special values of the pivot table just because they wanted to get rid of the plus minus sign they expand or the collapse sign right here on the left now there is no need to copy paste the pivot table what you can do is click on the pivot table go to the analyze tab far right you can turn off the plus minus signs and the plus minus signs are not going to bother you anymore final trick trick number 10 disable the double click details of the pivot table what do I mean by that here we have a simple pivot table against the ears and the total revenue and everybody knows that if you double click on the revenue what you're going to get is the detail of that particular year or that number on which we have double clicked now that's good but maybe you don't really want the end user to be able to drill down and take a look at the very details of the pivot table maybe you would want to restrict that how do you do that right click on the pivot table go over to pivot table options this time I'm going to jump over to the data and I'll say that hey please do not show the details disable that option click on ok now this time if the user double clicks on the pivot table he's going to get nothing but the error nice all right that's been it those were my 10 interesting awesome pivot table tricks let me know which tricks did you not know about and in case you would want me to do a follow-up video talking about a couple of more interesting tricks on pivot tables please do drop in a comment uh requesting that video as well in the end I'd like to give a big shout out about my Dax and my power query courses in case you are a beginner and trying to learn power bi right from scratch trying to understand the fundamentals of power query data modeling and Dax you'd like to build up your base level first and then start solving more difficult more challenging problems I'd highly recommend that you take a look at my courses it's going to be super awesome thanks so much for sticking all around and I will catch you again in the next one bye now yeah thank you

Channel: Goodly

Views: 199,477

Keywords: Excel, pivot table excel, pivot table excel tutorial, pivot table tricks excel, advanced pivot tables

Id: dF6I89426B8

Length: 13min 54sec (834 seconds)

Published: Fri Feb 10 2023