How to Create Excel Pivot Tables & Pivot Charts - Beginner's Guide

hi welcome to teachers Tech my name  is Jamie and it's great to have you   here today I want to show you how  to create a pivot table and pivot   charts along the way I'm going to  give you some tips and tricks as well if you would like to follow along with  today's tutorial I have the data for you just   check Below in the description you'll see the link  that you can download the data that I'm working   with then you can go through it with me step by  step so we're starting with how to create a pivot

table and this is the data that we're using here  now to do an insert uh it's very simple you just   go up to the menu and hit insert and below in the  ribbon you're going to see pivot tables right away   as I go through these pivot tables I'm going to  give you some tips and tricks also and I'm going   to start with a tip right away and the first tip  is to select inside anywhere in the range or the   table if you're using a table uh before you go  and click on pivot table because what it does it   automatically finds the r range for you so here's  an example if I go up to Pivot tables notice that   they have some recommended pivot tables here  I'm going to cancel and click out and I click   on recommend it again and it doesn't know now  I could still select my table or range from   here and it'll be fine but just as a quick tip if  you select inside of it and pick pivot table or   recommended pivot tables it's going to know where  the data is coming from now I just want to mention   a little bit about the recommended pivot tables  uh this is a quick way to uh to create them uh   usually they're pretty accurate with probably  the main ones that you would want but you can   see where it goes some of unit cost by region some  item it does some recommendations   based on the data and the titles that they think  that you would want so uh you can always start   with recommended pivot tables and choose one  of them uh but we're going to be going from   pivot table on it so uh and after we go through  pivot table you'll understand after you insert   the recommended pivot tables you can still make  the same changes so I'm just going to go ahead   click pivot table and you can see that right away  since I'm clicked inside of the uh range here that   it has the sheet and the range from A1 to g44  here now uh where do you want this pivot table   to go so this is important do you want a new  worksheet or do you want it to be on the same   worksheet uh so I'm going to go new worksheet uh  and so it's going to open up another tab you can   choose what you want to do on that or you can set  a different location here so you can also choose   whether you want to analyze multiple tables and  I'm not doing that in this case but I'm going to   go ahead and hit okay and what you get if I look  down at the tab at the bottom you can see I have a   new sheet right here uh and now they're asking me  to start building my uh my report by selecting uh   my different fields to put onto it so now we  have to be selecting our pivot table fields and

if we look over on the right and it can be done  very quickly so if I go ahead and I'll just click   on sales rep and what you notice is it gets put  into rows and now the names are going to go down   this column and then the data as I select it would  be put into rows you can quickly change this so I   could D drag it into columns and now the names are  going to go across the top and uh we are going to   have our information below it in the columns now  we can uh quickly turn this off and on the sales   rep so if I click off of it goes away we can  drag these down too so if I drag this into a spot   I could dragged around into columns you can see  how easy it is to manipulate so I have another tip   here that I want to give you uh so this is kind  of the default setup here with the fields down   below where you're drag into if you go up to the  gear here and tools and drop down you can pick   a different way to view it so I prefer this one  right here uh I'm just going to select it and we   get these uh on the side here with our different  fields that we can just drag across now that's   up to you what you prefer and the other thing I  want to point out with if we go back to it you   can sort your Fields A to Z uh so if you go ahead  and select it you can see how it changes it uh now   this if you had a lot of different fields this  would just make it easier to find them so just   remember those couple tips right there okay let's  add some more things to this now so we're going to   have our sales rep here and I want this in columns  and I'm going to place it here so you can see uh   some of the ones are hiding right now and the  other thing I want to add so let's go ahead and   add our uh totals and you can see as I click on  different ones how quickly it starts to change so   I can click on and off uh I'm going to go ahead  and click on units it shows all my sums of units   here but what I want is to have items so  when I click my items now I do want this in rows   it places them down here so now if I pick my units  it fills in so I can see each person so if I look   at Dwight I can look at per binder per desk and  all the different items and it gives me the grand   total there so these are just some different ways  you can get some quick uh your report being built   by selecting the fields so did you notice if you  click out of the data that the pivot table Fields

go away so we C click back in and they reopen here  now I want to point out just if you're adding more   Fields let's try uh I'm going to add a region here  so if I click on region you can see it added it   to the rows at first and then it's going to be  breaking down this way it breaks down the binder   and based on the Central north and south where  where it was selling being sold in different   places now if I bring this over to columns you  can see now that it's going to go across the top   it's kind of breaking uh it between the different  uh salese so in the case of Someone Like eron You   can see that she sold in the Central and North  while Dwight only here so   go ahead try different things try adding uh you  know by selecting different ones uh selecting uh   seeing how it works to get the different reports  on it and then moving from rows to column to see   the ways you can adjust it now another way you can  adjust it is to sort and if I look over here you

can see that we have a drop down and this is going  to help me sort so in this case this is going to   sort my column labels which are the uh sales reps  here and if I drop down I can go ahead and select   individual ones so if I said okay let's deselect  and I'm going to go and let's compare Dwight and   I'm going to compare Jim and hit okay and you can  see that we have the two right here and I can look   side by side to see their totals of their items  being sold if I wanted to see total of amounts   in different ones I could pick a different uh  thing to add from over here to add it and you   can see how quickly it can adjust so if you drop  down again I could select everything hit okay and   everything goes back on I can select this way too  so if I wanted to sort a little bit more uh if I   didn't want to see all the different items maybe  I just wanted to see paper I could hit okay and   now I'm just seeing paper and I see the sales  Pur so it makes it easy uh to look at that way   so go and try a few different ways to sort now  I want to show you uh how you can actually build   a individual report and it will create a new tab  down below for each sales rep or depending on what   you're creating and it's done only with only few  clicks so before I show you how to create a tab

for each salesperson I wanted to point out the use  of filters so let's say in this uh data with the   units I just want to see uh by region also and I  like to filter through there so if I go and take   region and put it in filters you can see up top  I can simply now drop down and I could be looking   for a certain region or zone so if I wanted to see  in central I hit okay and I just get those results   back you can also see that you can select multiple  so if you want it more than one or switch it back   uh it's easy to add more than one filter so or  even move from different ones I could uh take   this sales rep here and move it up and now I can  sort by region and sales rep so if I was looking   for uh let's say in the north and I'm going to  also choose a sales rep I'll choose Pam hit okay   I can see in the North she sold this many if I  was going to go back and check out a different   region uh at this point you can see now she hasn't  had got anything in the South so I just wanted to   point out uh the use of filters but now I want to  show you how you can use the filters to create a   tab for each salesman person uh in this example  so I'm going to go ahead and deselect the items

that I have here and I'm going to build another  report and remember I want a new tab for each   one for each salesperson so I do need a total and  this is going to go into the values here I do want   the item there so I can see uh the item beside the  total here and we're going to need the sales wrap   and I'm going to actually add this to the filter  so I'm just going to drag it into the filter like   this now uh something else I want to point out  out is to format your numbers here so if I go and   just select in here I can rightclick and number  format so maybe you want it in accounting uh or   currency you can see the different examples that  goes through so if I wanted two decimal places   and it to look like this I'm going to go ahead  and hit okay uh and you can see how it formats   it you can choose the format that uh you would  like uh the other thing I want to show you is if   I rightclick on this you can sort this so maybe  I wanted from smallest to largest or largest or   smallest or some more I'll just go largest to  smallest just like this so some things that you   can do uh to it now what I want to do is generate  the individual reports on each tab if I go ahead   and drop down and go to options right here and  you can see show report filter pages so when I   select this we're going to get show report uh  filter Pages if I go ahead and hit okay now uh   it's going to go ahead and if I look at the bottom  so if I look there's Andy there's Dwight's there's   Aaron I'm going to get an individual report for  each uh for each salesperson and you could adjust   it depending on what you want so I wanted to make  sure you understood how you could do that uh very   simply inside the pivot table Fields so I'm just  going to go back uh you can see as I go through

these different ones I'm just going to go back  to the uh original P table that I was working on   here and I just want to show you a couple other  things and some more tips now let's say I was   curious uh where this 23514 number uh came from  if you double click on a number so I'm just going   to double click on this number it opens this up  and shows uh how that number was created by all   of this information so that was by a double click  it opened up a new sheet when I did it you can see   there's a sheet 16 uh beside it now so just try  double clicking on a number and it's going to show   you how it's even broken down more now some other  things I want to show you uh with formatting and   usually things can be done in a few different ways  I'm going to just show you if I go down to values   here right here and just drop down uh you can see  that there's value field settings you can also get   to Value uh field settings if you right click on  them too you can see value field setting settings   right here so if I go ahead and choose uh value uh  this settings here value field settings let's say   I could you know I could say an average account  uh if I was doing a count hit okay well the   dollars don't make any sense anymore but it's just  counting up uh here would be1 15 rather than $15   so depending on what you uh were uh trying to show  in your pivot tables remember that we have value   settings and you can change uh what you want right  through here so I'm going to leave this as sum now   another thing I want to show you when you're right  clicking uh is if you right click you can show   values as so I find this is a cool way to show  percentage so do we want H you know you try a few   different one so if I say percentage of row total  column total let say column total I can see that   the binders make up 48% uh the pens will make up  21% uh and it just shows you an a different way so   all these different reports can be generated and  remember you're right clicking uh to get to more   of these here so I just go back to no calculation  and it's gone so now I want to show you how you

can apply some conditional formatting to your  pivot table numbers here before we get into the   graphing and charting uh so let's say if we want  to this to be like a data bar through here so   what I need to do is go back up to the home tab  up top here and look under conditional formatting   if I drop down uh you can see we can apply all  these different things but we're looking at the   datab bars and if I hover over notice  it's just doing the cell that I'm selected in and   I want it to be applying to all of them uh in this  case just go to more rules right here and you can   see apply rule to only that cell no we wanted to  apply to that uh a cell showing the sum of total   values here so if I select this one here uh that  will do the trick now the other thing do you want   the numbers to be shown so right now it show both  or do you want to be just the bar only so I can   go and pick a color now and it will preview down  below so maybe I pick an orange just like that   uh and I'm going to go ahead and hit okay and now  I have a data bar applying to uh these different   ones so just different way to show uh you know  show your reports that you can add conditional   formatting to them okay let's move over to  graphing or charting our pivot tables now so let's

say you want to chart your pivot table report just  to make it look a little bit better a little bit   easier for people to just take that quick glance  and know what's happening uh so I'm just going   to select inside of this here and I'm going to go  up to insert and take a look we have pivot chart   right here we do have the drop down I'm just  clicking on it here and right away so we're   I'm it defaulted to the call I can pick I just  get an idea maybe of different ones how it looks   here just by going through I'm going to keep this  simple I'm going to create a pie chart here uh you   can see up top if I wanted to see a different view  I can hover over to get the view uh to try any of   these different ones I'm going to just keep it to  the pie right now okay so now just hit okay on it   and I have this in here I can move it around I can  size it uh if I want to differently if you wanted   it larger here now I want to point out this is  dynamic so I can take a look at different things   so if I wanted to take the look at the sales rep  here uh maybe I wanted to compare different ones I   could I select multiple maybe I just wanted to  pick one so if I pick Michael Scott I can see   just the one person's here so it's going through  finding the information and breaking it down for   me uh you can see the items are over here uh so if  I go back I can go and select all and it puts it   back again so you can sort using the chart to give  those quick looks I can dropped out here also and   if I wanted to uh maybe deselect if I just wanted  two things here so if I had paper and desk H okay   and it just compares the two different ones so  you can do all these different things within the   chart still just like we were doing uh just with  the data before so but you can apply this uh to   the Chart so now what I wanted to show you  too you can uh make this customize this look if

you just double click in it if this wasn't open  this format chart area uh just double click on   it and it will open it up uh you can see I can  quickly start to change things whether I want   it to be maybe a gradient fill uh you can see how  the background changes I can adjust my colors uh   down here if I want to add different ones if I  wanted a pattern fill you can play with   all these different options we have our Tech  options uh also uh here so if you wanted to   change different fonts and colors you can adjust  all those things so if I go ahead and select uh   maybe I wanted this to be differently I can even  drop down uh so that's the chart title or if I   wanted maybe the legend here you can see how it  selects different things so I could go ahead maybe   I'll add a I can change the fonts like this if I  wanted the legend to be positioned differently I   can start to change things all around so all these  different things that you can play with uh to get   the chart looking how you want uh you can move  these inside too so if I wanted to place these   in different places again you can size your chart  differently to uh get it the way you want I hope   this pivot table and pivot chart tutorial has  helped you out to make you feel more confident   when using it let me know what else you'd like  to learn Down Below in the comments thanks   for watching this time on teacher Tech I'll see  you next time with more Tech tips and tutorials