30 Pivot Table Tricks for ADVANCED Excel users

hello everyone i am rishabh from yoda learning academy and in this one hour video i'm going to share the top 30 pivot table tricks right from the beginning till the advanced one of the common challenges people tell me while using pivot table is that they are not very sure which fields to drag where to be able to create the report that they desire i'm going to clear out that doubt apart from that there are many other tricks such as grouping percentage calculation how do you create hundreds of report just with a click of a button all the charts associated with the pivot table through sparklines and much more so if you like any one of these tricks do comment in the section below i love to hear the feedback from you let's start the session in this video we'll talk about pivot table and if you have not worked with pivot table before you will love this new feature of excel and even if you have been a user of pivot table i'll show you some of the tricks which are going to be extremely beneficial a lot of people ask me why should they invest time in learning pivot table well in this video in a very fast manner i'm going to show the various features and how it can help you in your work so if you are interested in knowing the details you will have to watch the other videos but right now let me take you through a roller coaster ride well before me lives a columnar based data talking about an organization's employee database the data of joining name salary division rating and age so primarily i've taken a data which spans over roughly roughly 400 row items and i want certain reports to be generated out of it so i press ctrl a i go to insert i go to pivot table okay so once i go there i activate one setting the details and the logic of which i'll share this in my next few videos at this moment let me show you the report that i'll be starting with so i want division wise salary details in terms of how much salary cost has been incurred by this organization i may not only want salary but also that how many people are working in the organization there you go not only that i also want the average salary average salary per employee per division maybe that gives an hint that which division should i target for so once i get the answers i'm going to quickly format this data and once i do so this is your report talking about total salary average salary per employee and the count of people working in each division now i may want that the same numbers which you get to see may be presented in percentage form so yes within a few clicks i go to an option which allows me to show the percentage and there you go if there are 100 people working in the organization then 35 percent of them are working with rad and i also have the facility to sort it based on the highest to lowest category which means the one which is heavily staffed and the one which is sparsely staffed so this was report one report number two and this is going to be something in eye opener if you have been using pivot table and did not get time to explore more so what i do i pick up salary i pick up age and i use a certain technique which allows me to group this salary and age in the manner which i wish to so eventually what's going to happen is i can find out how many people in my organization 417 and that to in which age category group and in which salary bracket not only that i may wish to find out what has been the sequence of recruitment what has been the trend for recruitment so i may as well as use doj date of joining group it and once i do so i choose months and years both and whoa i get a report which tells me you're wise month-wise people who would perhaps be joining this organization not only that i can prepare a report which may exhibit itself like this so your wise month wise a proper trend not only that i may also want to see how has january been in terms of recruitment for different years and if somebody likes this particular report of course he would want to see this report being generated for each of his departments so i use a certain techniques which quickly allows me to show these different sheets being replicated you notice ad cdfd ed hfd lgad all these prepared in what less than two seconds so that was a quick overview of how pivot table can be useful for your work before i start speaking about pivot table and its various features and the way to achieve them i must make you aware about the prerequisites of pivot table well the data that you're going to work with you cannot just pick up any data and apply pivot table and i've given some very basic examples to show you the differentiating factor between the data that you should be taking versus the data you cannot work with right now i have a data very simple data on the top left corner of my screen which talks about chocolate ice cream and in two years what has been the sales amount well this is not one dimensional data this is not a columnar data this actually based on two dimensions of variables if you want to work with pivot table you have to have columnar data or in other words one dimensional data which means you would have data which says chocolate 2008 and then the sales 100 so each one line item is an independent line item so if you have a data which looks like this you have got to convert this into this form before you apply pivot table and this pivot table which you apply based on the table number two is going to give you a lot many more flexible features which you can apply and make use of secondly you must not have any header which is left blank for example if you keep this header blank which may or may not be hidden and if you go ahead and try to make a pivot table eventually it stops you it gives an error message saying that this is not allow you cannot have a header which is blank the third point you cannot have a data which has merged cell for example i have particularly ensured that the stable number two the header has a merge cell and if i try to choose the entire block of data notice the extra cell which is not merged also gets chosen so if i go ahead and say insert and pivot table okay yeah same error why because this extra cells that got chosen is not having any header so in in simple words the three points that you need to take care of before you start preparing pivot table one it should be in columnar form secondly it should have an header which is given with certain text or number but should not be left blank and third there should be no merge cell so once this prerequisites have been maintained then we talk about pivot table after looking at some of the introductory session on pivot table i'm sure that you are roaring to find out how this pivot table reports are being made and what are the logics behind every click that we eventually be doing so i don't want you to learn in an ad hoc manner so what i'm going to do is for every one particular click and feature i will tell you the background logic behind it and i'll start with the simple data once we cover all these tips and tricks we will then graduate to a data which may span over 60 000 lines and move multiple columns at this moment we have a data which is roughly 400 line items what is it it's a database of employees of a hypothetical company the date of joining given salary in terms of number division that they're working with and rating one is good five is bad age also given now from this columnar data where the headings are all filled with some text it's not a blank anymore i am going to prepare a pivot table so the procedure to do so is that you will have to choose the entire block of data so we take the help of the best practices i pick up doj that's the first cell shift control right shift control down once i choose the data i just want to check whether there are any more for the lines below this data so i press shift control down again there isn't any more data so shift ctrl up now remember one of the shortcut keys from our earlier video session which allows you to go to the header of the data without losing the selection yes control backspace key so all the control backspace key is not necessary but this just allows you to look at the headers while you are working with the data the point is you've got to choose the data this way now once having done so we will go to the insert tab which is present on the top left corner of the screen once i go to insert tab let me click on pivot table once i click on it the generic box that comes before my screen it says is the data that you have chosen full and final i say yes and it also asks you where would you want to present this pivot table report at in the new worksheet or in the current worksheet or any specified location which you have in mind at this moment i am happy if excel says it's a new worksheet that in which pivot table is going to be generated into so that let me press ok and whoa you get a new sheet generated and that has the grid of pivot table so this is step one after having covered the prerequisites of the data requirement we went ahead and created a pivot table template i assume you would have created a pivot table template on your screen so from here we begin our discussions now from the data which we're working with that was of staff details and from there we have graduated to a sheet which has the template once i reach till this stage let's explore what are the different buttons and options that lies before my screen and that will help you make the pivot table on the right hand section you'll notice something called pivot table fields and you'll have all the field names which are relevant for the data below that you have four sections which i'll be talking about very very soon it's called filters columns rows and values now for a beginner this might be a little confusing to look at it all at one go and trying to figure out how the report is being made well i'll make your life easier what i'm going to do is i will activate one setting which belongs to one of the vintage version of excel and that lets you choose other options which otherwise would not be choosable on this current version of excel 2010 or 13. so right click i go to pivot table options when i do a right click ensure that you are right clicking on the grid area of the pivot table so pivot table options i go to the display tab and within display tab i check on this option which says classic pivot table layout once i click on classic pivot table layout it tells me that it will allow you to drag the fields directly into the grid now what does that do i'll press ok to find out the look of the pivot table grid changes and it's much more structured right now it says report filter fields column fields row fields and value fields and you also have the facility to drag these fields and start making your pivot table i'll talk about the details how do you make the pivot table what logic are you going to follow but right now the most important part is to observe that these four sections have a corresponding four sections on the bottom right of your screen which means what whatever you do with report filter fields out here it gets adequately reflected in the filter box here simultaneously in fact if i put salary in somewhere out here values you notice it also reflects in the value field on the main grid area of pivot table that's value fields for you so practically speaking these four areas of pivot table grid has a four corresponding boxes on the bottom right of the selection of pivot table as we progress i'll tell you practically what those can be used for but at this moment i'll just end by saying that this one area value fields this is primarily dedicated for all mathematical calculations so if i put a salary in the row fields i get the list of salary but if i put the same thing salary in this value fields it's going to show you some action in the form of some calculation which currently is being shown as sum of salary now just one last observation the same thing you will get to see in the fourth section of this four box menu which says values so as we had discussed all these four sections of the grid area of paper table has four corresponding boxes so as we progress ahead i will make use of all these features and we'll then see how report can be generated i assume that you would have watched the prerequisites and the intro part of the pivot table because now we are going to step on the accelerator and start making the pivot table reports that we intend to so as of now i'm going to select the data lying in front of us ctrl a ensuring all the headings are proper with no columns with a blank heading so once having done so i go to insert tab and pick up the pivot table the general prompt that it provides me is do you want to use the same data which you have chosen of course yes and the new worksheet is the one where the pivot table grid area is going to be generated once that is generated as we have seen earlier i right click on the grade area go to pivot table options and then go to display and choose the classic pivot table layout option on okay once having done so then let's start making our pivot table report the starting point is going to be where i'm going to present the report in a manner which shows me what are the list of divisions and what salary are you paying to each division total salary that goes inside every division that is what i'll be wanting not only that i also want how many people are working in every division and finally average salary per employee for every division so let's start i go to division and i pick up this field now practically speaking you can make a tick mark on it directly takes to the row fields or else you can drag this using mouse and put it in the row fields i find that very convenient and once having done so you get is the same thing as what we got earlier by checking this check box on also this division you can also throw inside this third box called rows it automatically feeds that in the row field so practically you have three options three ways three approaches with which you are going to start making the paper table report now once having this division in front of my screen next thing i want is salary salary total now salary has to be a number of course and that i want a mathematical calculation of in terms of sum so let me go to salary drag this up and put there in the value field option this is the area where we had referred as main action area or all mathematical calculations happen here so i just throw that there yes i got a data let me optimize the window which uh spans over millions and it's telling me 95 million dollars happens to be the total salary outflow for this organization and this is the breakup now eventually i'll be doing the formatting part of it but as of now let me also go ahead and apply a technique which give me count of people now the trick is i use the same salary field and drag it and put it on top of these set of numbers notice i am not throwing that on top of the header total but on top of the numbers category area this is the main action area if i just release the mouse click it's going to pop up with another column which says sum of salary now what i'm going to do is i'm going to focus on this heading which says sum of salary why because if you double click on this header right now you get something called value field settings when do i double click wherever you get to see a terminology of mathematics so i double click there now when i get to value field settings i wonder what's containing inside it well if i start reading about it i will sleep so let me get straight to the point this particular tab which allows you to change it to count as you do so you get the count of salary 41 people working for division 80. if i double click again on the heading you still get the same thing value field settings which you can change it to average wait my question was get all three at one go so let me bring back salary once again on top of the main action area release the button of the mouse and then go to the third heading double click if you're not preferring double click you may also right click and then go to value field settings so this allows you to manipulate the calculation to let's say count and once i do so i get in sequence the sum of salary average salary and the count of salary it's very good technique if you want to have a summary sheet for any data you're working with for example if you have a sales data you get to see how many client names are there and how many sales order have they given to you and what are the amount of sales that has been affected through them if you have purchase data you can find out all the vendor names you can find out how many purchase order you have issued and what are the total amount of purchases that you have placed before them so once having done so i'll just take some extra seconds to ensure the formatting is picture perfect so i go to home i apply the comma style for the milling separator and i reduce the decimal and there you go this becomes your pivot table report in the series of pivot table videos what i'm first doing is making you aware of all the important tips and tricks eventually as we progress and we have been made aware of all the important tips and tricks we will take bigger data right now i'm continuing with a previous section where we had created a list of all the division names the sum of salary average salary and count of salary now you might also wonder that besides the absolute number you may also wish to get the percentage value that means if out of 417 employees 41 belong to the division ad which is approximately 10 then i should have the 10 percent value here so let me do one thing let me go back one step let me take away the salary option away from this check box and this pivot table gets back to one level down now against the division names i'm going to put salary in the main action area not once but twice now with salary amount i want the percentage okay so how do i proceed well i go to the second heading which says sum of salary the one which is highlighting right now i double click on that my intention is to go to value field settings so i go to value field settings and then there are two tabs side by side i go to the second tab and here if i start looking at the various options it says show value as as what and as i click on the drop down menu you notice that it says percentage of grand total percentage of column and row total in fact the terminologies might be slightly different in the older version of excel in 2007 version vis-a-vis 2010 and 13. but never mind i'll take you through all the various options eventually as of now i'm interested in percentage of grand total what is grand total this is grand total for this particular column so what is it going to do it is going to do 8 million divided by 95.7 million dollars and that is how the percentage gets derived if you want to sort this entire list based on the percentage yes you can you can say right click and sort sort largest to smallest so what we saw was a very very brief overview how a particular number in the value field gets converted to percentage which can be looked at side by side besides the absolute number i assume you would have spent some time in practicing the earlier practice session exercises let me take you few notches above in terms of the complexity of the features of pivot table now inside value field show value as tab you would have noticed in the earlier videos we talked about grand total percentage of grand total well this time with an example i'm going to show you examples how percentage of column or how percentage of row total can benefit us if we have the requisite data so right now what i'm going to do is i'm going to keep the sleep slate clean and return back to the normal grid area of the pivot table i keep the division in the row field once having done so let me keep the rating in the column fields area so basically this report is going to tell me under which division how many people got reading one two three four and five one being the good rating five being the bad rating now i need name in the main action area notice i'm not simply taking this name on because by default since name is a text field it is being thrown inside row field which then gives you the list of all the names i want name not in the row field but in the main action area where i'm going to throw this particular field into once i have the numbers before me this automatically gives me count of name single reason being since name is a text nothing can be averaged nothing can be summed only one mathematics can be applied and that happens to be what count yes now from here the story begins what if i asked you that i want a calculation in the pivot table which says 7 by 41 so typically we would like to select the cell 7 divided by 41 correct pressing enter so this shows me approximately 17 percent or at times i may want a calculation which says 7 divided by divided by the grand total the total employees in the organization enter and that's how much roughly 1.6 and at times i may ask you equal to 7 divided by 63 which is at the bottom of this column enter so that's approximately 11 so every one of these calculations have different interpretations and the approaches to generate them is also different so we'll see how those will be generated and what interpretation does it bring before us so what i'm going to do is i'm going to choose this yellow cell the one which i've colored right now count off name and within which i'm going to double click upon i go to the second tab which says show value as under value field settings and here let me start with percentage of grand order okay so as i can see this divide by two to four one seven happens to be one point six eight percentage so this calculation is an overall calculation and is relevant when all the variables are of same nature or same scale currently the nature of the ratings are different one is a good rating five is a bad rating so i perhaps may want to calculate this number divided by the relevant total of that one column so i double click on the header i go to show value as and then i choose percentage of column i press ok this calculation 7 divided by 63 that is 1 by 9 hence showing you 11.11 so what story does it hold for us well this tells you that out of 63 people one third of them belong to rad who have received rating one within the rating one pool 27 belongs to which division hfd so this story would have not been exhibited had used the earlier example of percentage of grand total in fact i'll show you one more variation where i'm going to double click on the header and get back to the percentage drop down and choose percentage of row total once i press ok this time each division is being shown as a separate business unit sbu and within the same business unit if i have to bifurcate the entire group of people into five categories rating one till five i get to see the percentage this will be even more clear if i reduce the decimal so as to make this uh look more easy on the eyes there you go so if i read the line number one which pertains to rating a d it tells me within a d 17 percent have got rating one twenty percent have received rating two thirty two percent have received the average rating rating three and remaining thirty two percent have got reading four and five in total so this is giving an overall view of division by so as we proceed the more data we take up we'll see different interpretations hold different answers and clues to the question we might ask this time we talk about grouping feature which is embedded in pivot table now this is not the same grouping as you would have guessed by now that is data and then grouping no this is something new and this primarily works with three kinds of data set one number second date third text now with numbers and date you have a tremendous advantage why because you can automatically group into clusters i'll show you an example let's assume let me start with numbers so when i look at the field items i see salary is a number rating is a number age is also a number based field now if i put salary either in column field or i put salary in the row field either of the two scenarios once i do so and i right click on any one of the salary items towards the middle part of this option i get something called group i'm going to click on it group now let's read carefully what this tells me it tells me automatic grouping which would start at a number one zero eight nine eight and ends at a certain number and by what interval will it show the grouping in terms of one lakh one tenth of a million now if i press ok i'm not changing any option right now i'm just simply pressing ok notice the clustering has been done automatically starting from the minimum salary and with an interval of one lakh now this automatic clustering can also be made to look beautiful if i again right click and go back to grouping and i then start with just one dollar now what it means is you are modifying the starting point and as i do so notice now it becomes 1 to 1 lakh 1 lakh 1 to 2 lakh 2 lakh 1 to 3 lakh and so on so quite often even for sampling purpose or maybe any mis report you would want to cluster the data and that data could belong to salary it can belong to the due dates for which the open invoices are existing or may even be in terms of age so if i put age in the column field a similar treatment i would like to meet out to this numbers so i right click on any age number right click on that i go to group i click on group and it gives me something similar it says starting 19 which means the minimum age in the organization happens to be 19 and ending at 53 so as i press ok the intervals it has been classified in 10 if i want to change the enter well right click group again and then the interval let me make it five okay so you have more intervals this time 90 to 23 24 to 28 both inclusive 29 to 33 and so on so forth now this grouping feature is available for numbers and as we move ahead i will also show you how this can be made to use for dates and text so i'll put the name name field in the main action area and after having received some numbers i'm going to format this without the percentage which now shows me that the total number of employees are 417 the breakup in terms of various salary brackets and within them the different age brackets in which the employees have been classified so when i look at the entire data what i get to see is 49 and above the age group of 49 and above there are approximately 15 employees in the organization so this is grouping automatic grouping based on numbers last session we saw how to group numbers into different clusters and that we took example of in terms of age and salary this time i am going to show you how to group and cluster dates and this is a very fascinating feature which i have seen people not using and once you watch the video you yourself will appreciate that how wonderful this exercise could be so what i'm going to do is i'm first taking away all the fields from this grid area of pivot table and i'm going to restart from scratch so we have a couple of fields which includes date of joining and when i try to find for other fields which are dates i don't find any so let me start with date of joining i go to date of joining i put that in row fields now what i get is if these are correct dates dates which are in the correct format as excel can understand it they are sequentially placed in an ascending order now i'm going to right click on any one date once i do that i will go back to the option which says group i click on group and i get a box which shows me what is the starting and the ending date in my data and how would i want to group or cluster it at this moment i'm going to choose both months and years i don't need to press ctrl and then choose this if you simply click any one options it gets chosen so notice as i double clicked it quickly gave me year and month based classification and that too in a sequence so if you had a data which spans over multiple years you'll be able to find a trend a very very nice trend so let me find out how many people join in which year which month and for that to happen let me bring name in the main action area and then let me release it so i've been told that there was one gentleman or a person who joined in the month of february in the year of 1995 and the similar kind of trend i am seeing as timeline moves on now you might tell me that this is a report which is very lengthy and cannot be printed in a single a4 size paper so now let me show you another trick you notice as you broke the date into two components it says years and doj and similar thing i find in the row section of this fields box now what happens if i take doj that is the month one and put it inside column i get month displayed on the column fields that is horizontally and here vertically now it's a perfect compact report which can be printed out as a single a4 size page and which exactly tells you the entire trend year wise and month wise so if i try to find a trend that which month has been the busiest in terms of recruitment and i compare the numbers uh i look at the month which is october in this data october november these two months have been the busiest in terms of recruitment and which year i picked up maximum amount of people in my team if i compare the different numbers i see multiple instances of 25 and one instance that was here 2012 where 26 people were recruited in the same year now it's not over yet i may also want to find a trend that how has january been in terms of recruitment vertically so what i may do is i will put doj below years but then you would say that this is the same report we started from then i will respond that years let me interchange and put it below doj as you could see from the cursor sign i get month month of different years so january how has it been over the different years the entire group of trend so again there are so many combinations that you can apply once you have broken down a date into years and months and not only this if you want further trends you can also apply percentage of row and column as we had discussed in the previous videos and you will find a very very nice trend coming up and that you can exhibit in terms of charts so that was dates based automatic grouping so we have been seeing some of the features of grouping inside the pivot table and it's not finished yet because after having seen the automatic way in which the numbers and dates can be grouped i want us to see how text can be grow though it can be grouped but you'll have to do it manually so let's see how this is done i will put division in the row field and i'll just put a normal number let's say name in the main action area so as to see how many people working in each division that's count of name for you now let's assume for the time being that there are four divisions which are being led by mr johnson and the remaining four divisions may be led by mr sengupta now pivotable doesn't know which division is being left by mr singupta and which one is being led by mr johnson so i want to cluster them under two umbrellas the two leaders names so the trick is i choose a d let's say one of the divisions i press control and i'm going to choose e d hfd and maybe the last one r double d so all these four division let's assume they are being led by mr johnson so once i have chosen them using control key i will say right click on the selection and i will go to group immediately what happens they have been clustered together under group one i show this very quickly because i also want to show you one area where people make mistake in grouping text instead of choosing the cell from the middle and thus with that white block cross they choose the entire row and then they press ctrl and continue the selection now this is an incorrect way you gotta choose just that one cell not the entire row using this white block cross and then keep control pressed choose another division let's say this time i'm choosing some off from h15 lgad and then i right click and then i go to group so all these four divisions which i choose have been grouped under one cluster called group one now what do i do i'm going to name this as mr johnson the next time i will choose the remaining four divisions and i'll say right click and go to group again they have been clustered under group two which i am going to name as mr sengupta so if you want another line which says total the total number of employees led by mr johnson and led by mr sengupta respectively then i will right click on division and say subtotal but whoa i see subtitles already been applied now this is a common problem with most participants most learners that they get confused which column are they applying subtotal into right now i want the subtotal for which main category that's mr johnson so hence i will right click on any one cell in this area and i'll say subtotal division 2. i get mr johnson 172 mr sengupta 245 so that's the breakup of employees under the two teams if i right click and then i say expand collapse let's collapse the entire set of field what i get is just two liner items mr johnson and mr gupta and if i click on this plus button it quickly expands with details so this was manual grouping with text with text you can only apply this manual grouping where you have to choose the items and right click and say group this time we'll talk about the few aspects of pivot table which helps us refresh the pivot table based on the change in the original source of data now the change in the original source of data can be twofold one amongst the existing line items the numbers can be changed or the data can be changed just for example i'm just changing all of the division to hfd in the selected region or towards the end of the data you might have new line items that get added every now and then so from two perspective we'll see how to refresh the pivot table which you may have already created first let me populate some dummy reports so i'm just choosing division and i'm choosing name okay i want to put name in the main action area so that i can see the total number of employees in the organization division wise now let's look at the data first 80 division has 41 employees assume for a moment that in the staff details sheet that's the main sheet one of the employees got transferred from hfd to the division 80. now by itself the pivot table doesn't get refreshed unless you right click on any portion of the pivot table and you say refresh as you do that you notice the number in the division 80 changes and so does in hfd now you can also affect this refresh by going to pivot table tools in the older versions of excel you will find the word options here but in the newer version it will say analyze within analyze you'll notice something called refresh i'm going to click on refresh the shortcut key as given it says alt f5 i'm going to click on that now why do you think refresh all has been given assume that there are some multiple pivot tables which are present in the workbook now they may have been made for different purposes for example the second pivot table which i've just copy pasted could have been made for let's say division and let's say salary salary total not count so i want that i do not need to go to individual pivot tables and say right click and refresh so for that i need a better option i go to analyze pivot table tool analyze i go to refresh button and i'll click on refresh all the shortcut key to that is ctrl alt f5 as mentioned in the key tip so it refreshes all the data points in the pivot table based on any change in the source data but this is different from a scenario where new line item gets added towards the end of data so for example i am going to press ctrl semicolon to give me today's date that's 19th of january 2015. enter i'm going to write my own name rishabh and let me give a salary a hefty salary in my name yep there i go and i'm giving the name of the division ed rating 1 of course and a hypothetical age 28 now this data doesn't get reflected in my pivot table unless i go to the pivot table i go to analyze tab the same tab can also be called as options tab in case you are using 2010 or seven version of excel but then you notice besides the refresh button there's something called change data source so i go to chain data source i click on it and then i just ensure that i re-choose the data starting from the first cell header shift ctrl right shift ctrl down including that extra one line i press ok and now when i do that notice the grand total the grand total of salary has changed and if i refresh the other pivot table the question is does it get refreshed in the similar manner no because this change data source works pivot table by pivot table so if i go to the first pivot table again i'll have to go to analyze tab again chain data source button and then ensuring again the data set is chosen till the last very line now wouldn't you find this cumbersome i know you would and for this very reason i have a quick solution for you one i can first ensure that the raw data is first recognized by excel as table so i go to insert tab i go to table option table control t is the shortcut key as mentioned in the key tip now once i do that it is a coherent unit one single unit and based on this i'm going to prepare a pivot table so insert pivot table this gets me a new pivot table in a new sheet so i'm going to put division and name in the main action area now why am i not able to see that main action area right now because as we remember from our last few discussions that you have to right click on the grid area of the pivot table go to pivot table options and then within the display tab click on classic and once i do that i get the old layout vintage layout so i'm going to put name in the main action area currently it shows me four one eight people okay fine i go to the raw data which is sheet one staff details now i'm going to insert a new name probably on the same date date of joining let's say ajay i'm gonna put the salary notice as soon as i add that one line the change in color as i can clearly notice indicates that the new item has been added to the table so i just add some of the basic raw data and when i go back to our own pivot table i just have to refresh that's it refresh 419 people so this time i didn't have to go to chain data source separately why because the table the entire raw data table has been recognized by excel as a formal table and that we executed by first applying table and after having done so then we applied pivot table so this was a quick trick to ensure that you save some time on change data source next time you work with pivot table and expect some changes and addition of line items this is about a quick shortcut with respect to refreshing pivot table when you right click on any pivot table and if you go to pivot table options within one of the tabs which says data tab there's an option which says refresh data when opening the file now if you are expecting that a lot of data inside the source data is going to get updated or getting revised every now and then then you perhaps would be better off if you keep this option chosen refresh data when opening the file which means you don't have to say right click and refresh every time you are seeing a change in data set so there you go small trick on the option side in this mini video i'll show you how two tricks which are not immediately related to pivot table can be applied with pivot tables so that you get something very fascinating on your screen first thing is going to be a shortcut key which helps you generate a pivot chart so if you place your cursor on any one cell oops i double clicked on it by mistake never mind if i place my cursor on pivot table any one cell from the pivot table grade area and if i press f11 function key f11 see what is going to happen in a moment's time it generates a pivot chart in a completely new sheet and that pivot chart is completely customizable as per the drop down of pivot table so that if you choose let's say a couple of divisions from this check box and press ok the chart automatically changes based on the user inputs this was shortcut key number one if i go back to the pivot table let me make sure all of the divisions are selected now although i'll be talking about spark lines and much more greater detail in subsequent videos but i want to show you that if you spark lines with the data that you have been given by the pivot table how wonderful experience would you have in fact even the end user who you're going to send this file to so what happens is next i go to insert tab from 2010 version of excel onwards they have come out with something called sparklines the section you'll find this under insert tab sparklines if i click on line and what it asks me thereafter is where is your data range so if i choose the data range somewhat like this notice i am not choosing the text value nor am i choosing a grand total and i'm not choosing the header i'm just choosing the numeric values if i press ok this is the trend which i can see based on the different division something similar i go to insert but if i go to column and then i go to these numbers and simply choose them and press ok there you go this is a similar tin but shown in bar diagram so just integrating two shortcuts and tricks along with pivot table to give you something more this time i'm going to show a small trick which allows you to get into the deeper details of the pivot table you are working with for example you may have made a pivot table which shows the eight divisions and the number of people working within those divisions now if you happen to pick any data or mathematical number from the main action area or value fields and if you double click on that what you notice is it prepares it generates a new sheet in which the details are residing so for example since i clicked on this number which is falling under cdfd in the new sheet what i notice is all these employees with the list of which got generated are showing that they are all from cdfd so this is like a report on demand a drill down report so if you double click on any number in the main action area that's the value field you will get this in a new sheet the break up of the entire set of numbers so there you go this was another trick which allows you to get into deeper details of pivot table data for quite some time now we have been watching how column fields row fields and value fields have been behaving while working with data now we talk about report filter and once having discussed this i will talk about a particular trick which i'm sure you'll love it if you have not worked with that trick before so watch closely let's assume that i have picked up division in the row field and in fact let me put division later let me put uh rating rating in the row fields and let me put name in the main action area which we have referred to the specifically the value fields so what this tells me is there are 417 employees and how have they been distributed in different rating pools now when i give division to this report filter person or i just put the division in the filter section of this box it both means the same thing it acts like a master filter which whereby if i choose just one division in ad it gives me the report based on only that division a b so it acts like a master filter if you choose a particular option or a group of options it quickly filters the entire subsequent pivot table report based on that now this you would have figured it by now that's not the trick i'm referring to my trick is about to be discussed and discovered right now assume there were 20 divisions and you would have made a very elaborate report based on that and the 20 reports the 20 divisions have to be prepared based on individual sheets so for example i want 80 divisions data this to be copied a new sheet should be generated and the data should be pasted there and the sheet should be named as a d now that looks like quite a bit of work if you had multiple divisions that runs into tens and maybe even hundreds at times now see the trick assume that this is all that's the first thing i'm doing making sure all the options are selected now i go to the pivot table tools options tab it's called analyzing the later version of excel 2013 onwards but right now it's called options in 2010. now on the extreme left i see a drop down of options please note i am not clicking on the word options in fact i'm going to click on the drop down of options once i do that i get something called show report filter pages now once i look at it let me correlate with a certain logic in the report filter section you have given division correct so when i correlate division with report filter let me reread it it says show division pages again we read the same thing show division pages show division pages what does it tell you it tells you that there are n number of divisions and you want to see all of them in different pages as you press ok right now in few seconds you get these sheets generated which are in the name of the division and that has automatically been generated you don't have to do a single piece of copy and pasting so if ever you have made a pivot table and you like the format and that format needs to be replicated for a number of divisions or number of cost centers or number of countries this is a trick you are going to follow you ensure that something is existing inside this report filter that it needs to be made sure why because if you don't put anything in that report filter and then you go ahead and try to click on the drop down this option seems to be deactivated so this can potentially replicate hundreds and thousands of worksheets in no time and we'll see that as we take bigger projects as we progress this time we talk about slicer something which has been introduced new in excel version 2010 now what is slicer technically it is a filtering component a panel which allows you to filter the list items through a visually appealing dialog box let me show you live what is it and where it can be used assume that you're given a task whereby you are putting the division name and the salary in the main action area so as to show the sum of salary now typically what happens i use report filter pages to use as a master filter so that if i want the division ads data only ad can be chosen but the problem is if i put division in the report filter it gets away from the row fields list so in that case what do people do they generally go back to the original data they again copy the entire block of division copy and create another dummy column which is nothing but the same set of items which is division now in contrast to this let me show you a trick and especially in the context of slicers let me give back the same report to pivot table so division wise salary now this time i'm going to go to pivot table 2's options in 2013 version of excel it's called analyze so once i reach there there is a button called insert slicer i'm going to click on that as i click on that notice what comes up you get a list of field list names so i'm going to choose the same thing which is called divisions and if i press ok you get a very beautifully placed panel on your screen now if you choose ad notice the data has been displayed with respect to ad and you also have the facility of the name ad being displayed in the pivot table report this you could not have done so easily without slicer had you not inserted a dummy column near raw original data and in fact if you press control you can also choose some other divisions from the slicer list additionally if you click on this panel which says clear filter everything goes back to normal now all the data pertains to the different vision not only that if you don't want this slicer for any reason you may right click on this panel and simply say remove division why remove division because the slicer has been uh attached with division field so remove division and there you go you're back to normal so this was slicer hope you work with slicer especially when you require a dashboard based pivot table so after having a very good discussion on the different features and benefits of pivot table and the way they are achieved let's get down to real project this time we are going to work with the data which spans over 60 000 plus rows and number of columns almost more than a dozen now to be precise i think it's around 13 yeah i can see that 13 column so this data contains all the features text number and date which is normally the data's backbone and this primarily talks about a sales data sales of a sports equipment seller it's been selling those equipments to various retail stores across the globe so you'd find a column which is called region subregion rather and it is consisting of all the names of the country to which its products are being sent to so what does it sell let me further explore to the column heading category it says mountain bikes within that you'll also find socks and mountain bikes mountain frames and all these categories of products are actually falling under different umbrellas bikes to be one of the umbrellas then the clothing and components and further below you might also find something called accessories so primarily this company deals with four major divisions and each division has sub products those are available in different colors and different model numbers now it's been selling those quantities in the three years 2002 2003 and 2004 the quantity is given and the sales amount is also provided so the cells which are marked in green are the important ones which primarily tells you what kind of product has been sold to who and which country and what quantity and what amount now the kind of analysis that we'll be trying to do let's do this with pivot table first things first control a choosing the data insert pivot table once i do that i'm just looking at the data source it's simply pointing to a1 till m some row numbers which is 60920 in this case i'll press ok it will populate that pivot table grid in a new sheet we have seen in our earlier introductory sessions that i need to activate one setting a vintage setting classic setting so i go to the pivot table grid right click i go to pivot table options further and deep down i then go to display tab where i'll target the option saying classic pivot table layout once i switch this on the layout changes completely the one we were comfortable with now notice i'm going to pick one attribute which is based on date so i have sales date somewhere out here yep there you go i put that in the row fields so as expected that is going to give me sequential date we have seen in our earlier discussion that if i right click on any one date item and then i go to group within group if i choose months and years both the entire date gets split into two components one is year one is month so now if i populate any set of data which is based on number it's going to show the full trend just for example sake i'm going to put sales amount in the main action area although i would like to format this entire column so as to avoid the decimal level accuracy now what it shows me is the full trend starting from january 2002 till december of 2004 i know what you're thinking why not show this data in a form of chart well there is a separate section of charts in our video program but at this moment i am going to simply press f11 if i do that in few seconds on my screen you will be seeing pivot table chart there you go so the pivot table chart if i magnify further let's see how has the access been defined so within 2002 january december within 2003 same set of months and so on so full trend has been captured using the chart showing you the sales amount over the last three years 2002 till 2004 in let me go back to sheet1 so i want to get some more juice out of this report so what do i do i closely look at the year and i look at the month then thing strikes me that why don't i put years field which has been broken down into column layout if i do that the entire thing changes notice the year component has been given horizontally the vertical data talks about the month wise details and now i have a breakup year to year so i can also notice that in year 2002 i sold 24 million dollars worth of goods in the next year 32.5 million dollars and the next year 24.2 million dollars total 81 million dollars if i want to analyze from the point of view of uh let's say percentage let's see what the different combinations we can make use of which we had already discussed in our previous video sessions i pick up the cell which is reminding me of some kind of a mathematical calculation i double click i go to the second tab yes and then i pick up percentage of column now if we recall correctly percentage of column is going to uh make a calculation like this 700 plus thousand and then divide by 24.3 million dollars if i do that i get a trend a trend whereby i get to see that in the respective three years which month has been performing the best so in the 2002 i noticed november was a very very good month i can't say the same for the next year because in the next year the best month was that of august in the next year it was may so this is based on percentage of column if i double click and i change the show value as percentage of row row total okay now it tells me that if you consider all the sales of the three januaries january of 2002 2003 and 2004 the best january has been of 2004. if i talk about october which october has been the best well in this case the 2003 shows me highest number which means in 2003 the october sales were quite good and if i compare october to october to october of all the three years 2003 stands apart so this was a combination of techniques where you group the dates next you also saw that couple of variations of percentage in terms of column percentage of row you also split the date into row fields and column fields and to top it up somebody might tell you look i like this report how about you make this report one report for all of my regions let's say canada u.s uk france germany and so on well in that case i put sub-region yeah there's one field called sub-region i put that in the report filter if you remember closely from our previous video session there is an option which allows you to replicate the sheets into different slices so i go to pivot table tools analyze i'll click on the drop down and since report filter has already been containing subregion i click on show report filter pages hold on hold on tight if i click on this and i press enter in few seconds what i get is different sheets called australia canada france germany us and uk and so on so once the pivot table format has been finalized by you and if you want to replicate the similar format for a couple of divisions or countries or username you will be using this technique so this was just a practice exercise based on all the things we have learned about pivot table from our previous video sessions do go over this once again if you feel the need because once you have a good grip on this features you will be the favorite table master ninja i hope you loved the previous exercise where we had practice with the data spanning over 60 000 lines with more than a dozen columns well i'm using the same case study to ask another question we had the sales data working for us whereby in 2002 2003 and 2004 the three years how much sales this particular company had made to its various customers globally now let's say i want to find a trend whether amongst the seven days how have the sales been affected although this is a seven days running company i want to see whether the maximum sales has been dispatched on sunday or wednesday or thursday or friday now to be able to do that first you need to extract the day which is sunday monday tuesday wednesday now to be able to do that i will be taking help of one formula called text i'm going to give the heading as day but the formula that i'll be using is called text let me optimize the window before i start working on it there you go once i put the column in front of me clearly now i'm going to write a formula in the yellow cell it says equal to text it asks for the date value which i'm going to pick from the sales date comma and within a pair of double quotation i'm going to put four times dd and that's it double quotation close close the brackets enter there's a formula which has been discussed in much detail in another lecture but let me see the application in context of this data as i copy paste the entire set of formula in the column in i get the that this first of jan 2002 is actually falling on a tuesday now with this data in hand let me quickly prepare a pivot table control a insert pivot table okay and as usual i'm going to right click on the grid area activate one of the pivot table options which allows me to go back to the classic pivot table layout once having done so now let me put the day in the row field and thereafter let me put sales amount in the main action area now let me first optimize the format and what i get is more or less the trend is consistent so if you want to present this data in a very clear graphical format you would use something called sparkline something i've already discussed in one of another video lecture sessions so what i'm going to do is i'm going to place my cursor in any one cell okay and after having done so i go to insert tab then i go to the section which is called spark lines i'm going to click on line if i choose line and i choose the entire set of numbers please note i'm just choosing only seven numbers right now not the text not the total as i press ok you get a basic trend whereby the second month you have slightly higher sales compared to other sales of different days so this is a sparkline based inside cell chart so what we saw was using a formula text how to extract the day and then properly also using sparklines to visually present the data let's take up another practice session on pivot table i sincerely want that you have a very very good grip on pivot table and its application and hence here goes another case study what we have before us is closing stock information for a company so you'll notice on different dates certain material type has been purchased from their respective plants manufacturing plants and they are still lying unsold as on 31st march 2012. the quantity is 10 and the book value for that particular item is this 2768 indian rupees now i want to prepare certain mrs reports based on this data and already i have prepared some sample output for your particular reference i go to sample output let me start talking about the first report optimizing the window now what it says is within durgapur the material type that has been manufactured and brought from durgapur plant what is the quantity that still remains unsold and the corresponding book value and same for haldia jamshadpur these are all the cities of india eastern part of india now as i go to the next report it also shows me the aging that is from all the items that have come from durgapur plant which year of manufacturing do they pertain to probably will able to give me some insights into maybe two years of the six years were the years where this item was not manufactured properly there were a lot of rejections and lot of complaints and because of which it didn't get sold so that was second report third report where i may want to look at how much money has been blocked in the closing inventory in terms of money so in this current report what i get to see is that this is the aging for all the plants i have three items and majority of my money is blocked under the second item which is called rxt almost 60 percent tam the remaining 38 percent and arb happens to be just very minimal amount in terms of money so let's make these three reports i go to sheet 2 i choose the data control a i go to insert insert pivot table once i press ok it populates a pivot table in a new worksheet now i'm going to activate one setting which i've been doing so very often right click pivot table options display and classic pivot table layout okay now once having done so let's see what options do i have first i can give the plant in row feel perfect then i may want date purchased in the row field so either i place it just before this panel you notice there's some difference how the border has been shown to you in green right so i'm placing it here alternatively i may put date purchased below the plant out here in the row section now this is pure and raw dates i want to group them as per year or month whichever i want at this moment i'm going to right click on any one date right click and say group and only choose year for the time being years okay so now you notice the breakup in terms of plan wise year year-wise breakup now if i want i can put the material type in the column fields which will give me the entire three set of material type arb rxt in tan now i'm going to put the quantity in the main action area so now what i get is see is plant wise year wise what material are still lying unsold and this is very much similar to the report which we saw in the sample sheet whereby we had plant wise here wise material wise the quantity that is still lying unsold similarly if you want to just to slice the data on the pivot table in such a manner where you do not want the year you just want the material type you could have put first that's plant and then material type let me go back and do this for you so i'm going to take the date purchase out back to the pivot table field what i have is a two dimensional data whereby horizontally the material type exists and vertically the plan so what i'm going to do is material type from column i'm going to put that before in fact after row i can try with both options and see which report looks much more appropriate to my mis report so this was about quantity what about amount i want to find out which material has captured the maximum funds and it has blocked that much amount of funds so what i do i check off all the options away i'll start from beginning i put the date purchase in the row field that's already years i will put material type in the column fields then i put book value in the main action area which shows me under different years what amount has been blocked under different categories of product i'll take a moment to quickly format this using comma separator and reducing the decimal now i want an option which allows me to calculate percentage of total grand total so i'm going to choose the cell which i am going to mark as yellow there will double click and from the panel of value field settings we will go to show value as from there from the drop down we will choose percentage of grand total let's see what's going to happen okay so yes it tells me if the total book value of all the stock put together is hundred percent what is the breakup in terms of the different material types so 1.72 60 and 37.78 if you're getting different answers while you're practicing please note that you may have included some of quantity please note it's sum of book value right now in fact i just go one step ahead i'll double click on the yellow cell and try to see what happens if i change the setting to percentage of row total so now it tells me if every year whatever amount of goods the worth of goods that remain unsold what is the breakup for example in 2008 the goods that were manufactured and brought in 2008 out of that 100 percent of them belong to tam so again depends on what question you're trying to answer especially from the top management and that is how you're going to slice and dice the data based on pivot table and to end this all we are going to put the plant in the report filter and we're going to go to pivot table options further on the left hand side extreme left hand side you have the drop down of options i'm going to choose show report filter pages so yes i know you would have guessed it by now that what it's going to do if i click on it and i press ok in a seconds time i get three sheets one for durgapur one for haldia one for jump for firstly congratulations you have completed watching one hour of video learning all the top 30 pivot table tricks it takes a lot of effort and dedication hope you are able to use that in your work if you loved any one of these tricks maybe the top three that you loved the most do mention that in the comment and i will love to hear from you i'm rishabh from yoda learning academy and i'll see you in the next video you

Channel: Yoda Learning Academy

Views: 22,112

Keywords: 30 Pivot Table Tricks, Pivot Table Course, pivot tables, yoda learning academy, Pivot Table Basic to Advanced, Pivot Table, pivot table excel advanced, pivot table calculated field, pivot table multiple sheets, create pivot table, create a pivot table, what is pivot table, can pivot tables automatically update, can pivot table remove duplicates, can pivot table auto refresh, pivot table field name is not valid, pivot table from multiple sheets, pivot table formula

Id: 4r97UNr0gMU

Length: 67min 31sec (4051 seconds)

Published: Sun Mar 21 2021