MS Excel - Pivot Table Example 1 Video Tutorials

hi in this video we'll be looking for pivot tables let's see how if you see on the data on the screen I have some data that state products region name unit sales and see oh geez now what I want is I want to create a pivot table for this data but first of all I will just try to tell you what is the difference between normal table and pivot table normal tables are fixed pivot tables are flexible normal tables gives you entire data whereas pivot table gives you a summarized data or you can say subtotals of each record or you can says each distinguished record you can see now let's see how to go for normal people table is I'll just keep my cursor in the data now make sure when you create a pivot table there for the data the data should not have any blank column neither any blank record so if you see I have enter there is no blank column okay and there's no blank record in this entire data I'll just keep my cursor in the data and I'll just go to the insert tab and go to pivot table remember piwo table can be created in just five seconds that's very simple and easy now when you click on this pivot table it will ask you two questions where is your source data so if you see my dancing ends here this my data is already selected with this dancing ends so source data is selected second question if you see it is selected already second question it will ask where do you want to paste in the new worksheet or in the existing worksheet I'll say I want to paste on the existing worksheet exactly next to it so you say existing worksheet where exactly in the existing worksheet I'll click on the cell that is my existing worksheet click on okay so if I just move around now see it's a blank pivot table which is empty there's nothing right now but if you observe I have got something which is piwo table fields now what exactly fields means is these are the names of the column headings now if you observe here you have this date product region name units so these are the column headings in this pivot table field list I have date product region name and all so now it has divided this columns it wants to divide into four partitions that is rows values columns and filters let's see those what are the this options I'll select this region and I'll drag down into this row so what happened was this region have become the row headings now if you become this name and drag it down in this column this have become the column headings this name have become the column headings these are the row headings because I have put in the rows similarly if I put the sales and CEO G's in this values only number fields can be entered either sales or CEO geez so what I lose I'll pick up the sales and I will drag down in the values so this way I've got the sales if you if you don't want the sales you can just bring it back remove it back to this place and you can bring this CEO geez and this values so these are Co G's right so I've got the names here in the column headings region in the row headings and the CEO G's in this middle data now if you do not like this kind of pattern what you want is you want to exchange it the reason it might be if you see it is getting long to the right hand side whereas the rows are very less if you want to interchange what you can do it's very simple we can just pick up this name drop in the rows pick up this region and drop in the columns like this just click on it drag it and drop it similarly region you can drag it and drop it here so now if you see that be what table has become flexible as I said be what is flexible normal tables you cannot do it it's very much flexible I have got this some of see oh geez now what is this exactly this is the summarize data which I said second one it is not the detail data it is a summarized data if you want to understand John has done a sales in the East region s 1605 let's see how if I just if I want to know the details what I'll do is I'll double click on this one 6:05 once I click on this I will get a new sheet before this sheet that is the data sheet and will tell me how I got the details let's see how I'll just double click on it once I double click you see before this data sheet I have got a sheet number five four I've already created and deleted I have got sheet number five it says in the East region and the name John the sales was what the CEO G's was one six zero five how come I'll just select this and this way I'll select yeah I have selected now let's see what is the sum of C oh jeez if you see at the bottom that is saying as one six zero five so you understand if you use a normal table you have to add two filters first for the region second for the name and then you have to take a subtotal or total whatever it might be but now it is very easy I'll come back to the data this is how you can get it now so this is for the columns and rows and some of that is for the values you can keep on inserting different values if you do not like it if you want to vanish it or finish it you can just select it drop it back select it dropping back select end drop it back right yeah it has stopped yeah one second it will take it now suppose if you do not want this kind of option you want count of region you already want count of name or anything so it's a very simple method you don't have to use the formula count if some if I'll give you a best option how you can use with help of pivot table so I'll just drag it here it came back so I'll remove this again yeah I'll just drop it back again yeah suppose you want count of name so what you have to do is very simple thing bring name into the rows and bring them into the values so I'll just bring this name into the rows and similarly I will bring this name into the values so if you see what it has done is these are the row labels where the names are given and similarly I have count of name so let's see it says Nik has repeated ten times whereas Luke has been repeated ten times now if you want to have the detail how come ten times give me the details so what you have to do is just double click on that number which you have a doubt if you have a doubt on this let's say Neeta so what you can do is double click on that Neeta let's say double click a separate sheet will be created before this datasheet you see this it says Neeta is repeated how many times so I'll just click on this total row a table will be created click on this total row I'll click on this and if you see count so I've got Neeta eleven times so this way you can solve your doubts how it is so I'll go to data tab and this is the option given now one more example if you do not want count of names you want count of region similarly are just example I'll just first remove this if you want count of region you just pick up the region like this drag it in the rows similarly pick up the region and drag in the values so if you see here region that is east is repeated 30 times so if you want to understand just double click on that 30 and you'll get the data so this way you can get it count of all the options at the simplest method you can get it now if let's go a bit detail into it you have the region that is east repeated 30 times but if you want to understand which salesperson has gone to that region and sent it so what you can do is I'm just going a big detail into it so I'll be bring this region that is the name into this columns and I'll bring it from the right-hand side remember East region in the previous example was repeated how many times 30 times but now how come 30 Dems who went how many times so you can understand Rita went five times Nita went three times Joseph two times and this Simon went four times so total of this 30 times these people have gone there and you get the answer so this way you can you know you can get it into detail about this pivot table whereas if you do not use a pivot table I will tell you you have to use countifs formula not also count neither count if but count is formula which is very advanced and detail formula but you can avoid using formulas and multiple options using this place right so that's all about here now if you see I have something which is not properly seen that is row labels column labels I do not understand what did exactly this I want to understand this is the region and this is for the names so once you click inside the table you get a pivot table tools but if you click outside the pivot table tools goes away the pivot table field list goes away once you come and click inside this table you get this one also and you also get this pivot table tools now what I was talking about the column labels and row labels if you go to this design tab you get loads of options coloring options you get this check box but now if I go to this report layout by default this is in the compact form you do not want in the compact form you want in some other form let's say you want it the outline form so once you click on this outline form so you see the names have come back that is the region and the name that has come back so it makes something sensible name which you can understand my next is if you see I have a grand total total at the bottom and grand total on the row and right if you do not want the grand total so you can remove or you can bring it back so I have the options as grand total if you if you do not want you can just click on this off for rows and columns so this and this both will go away if you want to bring it back the same go to grand totals on for rows and columns you have got it back if you want either for columns or rows you can just go to the grand total and you can either select for rows and columns only so I'll select for rows only so I've got four rows if you want four columns you can select four columns so this way you have got it right next we have what it can do is if you want to change the color right now it is a blue color if you want to change the color of this table design tab you can just click on this drop-down choose the format which you want for your table right so you can select right now a design let's say black color this way so black color comes if you want some other color you can choose the other curve similarly if you want to you know create your own style so you can just click on this new pivots table style but right now not required you can choose the from here itself similarly I'll just go for the black one because it's easy to understand then yeah similarly if you see there's something row headers column headers so if I click on row headers you see what is happening row headers is nothing column headers yeah so if you see column headers this column headers right now it is in black color it goes away whereas if you see the banded rows the alternate rows have been banded the ultimate of alternate rows have been colored if you select for the banded columns you see alternate columns have been colored here I'll select banded rows so alternate rows have been banded under red columns have been also banded so I not keep it as remove all the options here now I'll go for one more example that is B word one okay I hope you have understood few examples few which are left will go for the next topic here there is a pivot one so now I have the similar data which is a date product region purposely have taken different type of data that is date text and also numbers now what I want is I want to create this kind of pivot table right I have written using some FN county formula I want this kind of pivot table so how do I do it I want the names in the rows and the region in the column headings so it's very simple hardly five seconds to create a pivot table keep your cursor in the data like this and then you can go to the insert tab and click on this pivot table it will ask you two questions what is your source data and what is your destination source data is by default selected because I kept the cursor in the data and it says what you want D where is the location you want I want in the existing worksheet so what I'll do is I will keep the data exactly next to this so it makes my work easy to understand where I want the data and I'll click on ok so this is the blank pivot table empty I want to create a pivot table like this one so what I'll do is I'll bring here the name in the rows and I'll bring something which is the region in this column headings this way and I want these sales in this middle part so sales have got it so let's see whether verify the answers correct or not three zero three zero one seven that is properly given here whereas the three six four eight which is given you absolutely right now again if I there are given as row labels and column labels I'll go and change it into a sensible name go to design and I'll see s report layout showing outline form so I've got in the outline form next one so very simple thing if you want to change from dollar sign to this sinus what you can do is you can just go to this pivot table field list click on this sum of sales and you see the last option value field settings in the value field settings what you can do is you have this format right now it is the sum given here the name of that this is the sum which is given here sum of sales you see here it is on the right hand side left hand side column I'll remove this sum of sales and write as sales final right sales final Sony name will change but right now I want to change the number format for this number format I'll just click on this number format and I'll go to currency in this currency I want a dollar sign with zero decimals so I'll select the dollar sign right now I'll select any dollar sign from here let's say Canada English and decimals I want zero decimals I want zero so I will make it as zero yeah and click on okay so that's all so in just click on okay so if you see that has been changed to currency so somewhat similar or you can say this one so it's three zero one seven dollar three zero one seven dollars it's working fine so remember if you want to change the formats of the currency or the numbers in today to text or some sorry dates and you can say as decimals you can go to this values and you can go to this value field settings you have this option as number format and you can choose it so now suppose if you do not want here it is given as sum so if you do not once um if you want count of sales how do you find out if you want count of sales you could just go to this place and you can see as value field settings now by default the function which is selected is sum so you can go back and you can select as count so you see it has changed to count of sales this sales final will be changed to count of sales and I'll click on okay so the reason it is showing is dollar five because the currency format is there so you can go and see in the currency format so I'll go back value field settings number format and I'll make it number number and click on okay so it says it says it is five times submitted John in the East region now if you have still a doubt how come five times John has been repeated in East region you just double click on that and it says this is the John and East region so it has repeated five times in that region let's come back to the sheep that is a P what one so this way you can get some average minimum maximum count all these options into this P word table it saves a lot of time just button click earlier we used to type the formulas and functions and get the answers so I think you have understood how to create pivot tables how to create in just five seconds and go to the menu bar we have seen how to go for the report layout we have seen how to use the different functions and change the number formatting that's all for this video

Channel: Tutorials Point (India) Ltd.

Views: 3,435,983

Rating: 4.9114299 out of 5

Keywords: Pivot Table, pivottable, what is a pivot table, how to create a pivot table, how to use pivot tables, what is pivot table, how to make a pivot table, how to create pivot table, pivot table tutorial, how to use pivot table, create pivot table, what are pivot tables, how to do a pivot table, pivot table for dummies, how to make pivot table, create a pivot table, creating a pivot table

Id: 4PWVFBiFVVU

Length: 15min 5sec (905 seconds)

Published: Mon Jan 15 2018