excel intro to pivot tables - excel 2007 2010 2013 2016

in this video we're going to introduce you to a concept that's called a pivot table a pivot table is a very powerful tool in Microsoft Excel it really has a weird name like what is a pivot table well a pivot table allows you to analyze your data in many different ways and make some really great reports out of your data let's say for example we have all these individual transactions uh and let's say your boss asked for a report that would be a summary by office by day or by office by month or by destination by quarter or something like that that's exactly what a pivot table can produce for you a pivot table can really cross reference any two Fields I can even do a cross reference by office by destination but a lot of people use it like in the way that we're going to display it which would be maybe by office by month or by destination by month now notice how the um the first row of your data contains your field need and then each row after that is called would be a record each column is a different field so this type of data is called a um a list or a table or a database all right and that this is the the best kind of data that's going to work with the pivot table pivot tables in Excel have kind of a really bad reputation people think that they're too difficult or they're not going to give it the results that you're looking for I'm going to show you that they're not difficult and they're going to give you many many results so so the first thing you you're going to do is you're going to click on one of the cells that's inside uh the data inside the table pick on a cell that's not blank now on previous versions the uh pivot table used to be under the data menu now it's going to be under the insert menu for the 2007 and 2010 versions we're going to pick on insert we're going to pick on pivot table now uh there's a pivot table and a pivot chart I almost always make my pivot table first because the pivot chart is going to come from the pivot table so I'll usually pick on pivot table here then you're going to get this screen it wants to know where the data is coming from and actually it it usually does a pretty good job of picking where the data is going to come from in this case it says from A1 to i23 which is which is exactly right usually it guesses that right now you could pick a different range if you wanted to but usually you don't have to change that here it says use an external data source if you're going to use uh an access database or a SQL database or maybe Oracle or some kind of external data as your source for the pivot table you would pick that however if the data is in Microsoft Excel you would just choose this information over here now the pivot table location will be uh usually in a new worksheet but you could put it in existing uh in an existing worksheet if you wanted to but many times I'll put that in a new worksheet as you can see so many times you don't have to change anything on this particular screen this is not the screen where people get confused I'm going to click on okay now this is the screen where people get confused okay so let's try to figure this out it says drop page Fields here drop column Fields here what is it talking about well um this is where you build your pivot table now notice over here you have your Fields the fields come from the first row of the other sheet by the way notice how we're in a different sheet our data is in sheet one and the pivot table is going to be on sheet four that was the next available sheet now when I learned pivot tables a long time ago they only had this section over here but people found that confusing so then on on the the newer versions of excel they made these sections over here and they made the pivot table a lot more accessible you have to think about what you want here so let's say we want a report that's going to be by uh by office by month so that kind of gives you some Clues as to what to put into your row Fields your column fields and the uh the data we're not going to use the page Shields right now we'll use that in a different video uh right now we're just going to do a basic pivot table so watch what I'm going to do I'm going to pick up the office and drag it to where it says row labels now the row labels over here are the same as the the row Fields over here notice how it filled in each row with a different um office if you want to do something by month or by by year or something like that then you would use a date field for your column later you don't have to use a date field you can do a cross reference on any two Fields however if you wanted to buy month or buy quarter then you would pick a date field so I'm going to drag the date field down to the column label and now notice how each different uh column is a different day now we have a grid that's by office by day now um the data item or over here where it's is called values it's the same thing you would usually use a number number field there some kind of field that you're going to calculate so in this case we'll use the amount field I'm going to pick up amount or actually we'll pick on the total field we'll pick up total and drag it right into the value section and notice how the numberers filled in and this is actually a pivot table so let's see what it's trying to show us here if there was more than one transaction for brainer for January 3rd then all the transactions would be summed up right there so this is really showing you a summary by office by indiv ual day so look it took all the individual transactions and now we have a summary by office by day if we scroll down to the bottom of the column for each day I have a column total if I scroll over to the right and this this has a lot of columns so I'm going to scroll way way over here and now we have a row total for each uh for each office and then we even have a grand total over here now at this point I want to do something that's called freeze paint because when I scrolled over I didn't see which which office that I was in here's how I can freeze that on the screen I'm going to pick on Cell B5 and then from here I'm going to pick on The View menu and then under view I'm going to pick on freeze pains and I'm going to say freeze panes again now what that does is everything above the current cell will be Frozen and everything to the left of the current cell will be Frozen that's pixel B5 so watch this time if I scroll down see I can still see which which day that I'm talking about but now I can see the the the total for that day down here and then if I scroll over to the right notice how column A is still on the screen so when I get to the road total I can see which which uh office that we're talking about so that's why you would want to freeze the paints but look at what we're seeing here now we have the totals by day by by office and that's what a pivot table is really great for uh let me just show you a couple Basics here and then we'll we'll go beyond the basics in different videos let's say instead of a sum I wanted that to be a count I wanted that to be the number of transactions watch what we're going to do I'm going to right click where it says sum now you're going to pick on something that's called value field settings and then notice how we have all these different calculations we can a sum a count a minimum a maximum a standard deviation a variance so let's do a count instead of a sum we'll pick on okay and notice how it changed all the uh sums to counts now we can see how many transactions that they had on each day and you can see that some of them had more than one and now it changed all the subtotals as well so watch that one again let's go back to a sum we're going to right click where it says count we're going to pick pick on value field settings and then we'll pick on a sum rather than a count and just like that the numbers changed okay now what you can also do is you can limit your records if I click on this pull that next to office then I can pick and choose which offices I want to see so let's say I didn't want to see I only want to see Bloomington Brooklyn and Adena when I click on okay now it's only showing those those three items and it changed the sub totals accordingly going to click on that pull down and we'll go back to select all now you can do the same thing with the co with the um column headings as well they say you want to see all those different dates we can pick and choose there so that helps you limit your records now let me show you how we're going to make this into a monthly report instead of a quarterly report or instead of a daily report watch what I'm going to do I'm going to click on the first date we're going to rightclick on that date and we're going to pick on group notice first of all we can put a date range in there so maybe you only want to show the report for last month or last quarter you can put a date rage in there whatever you wanted to but look what we can do um we can group it by month quarter or year because it's a date field if it was a Time Field we can also Group by second minute or by hour all right month is already chosen for us so we're going to click on okay and look what we have now we have monthly totals by office this is the power of the pivot table it can really give you these outstanding reports watch that one again let's say we want to get quarterly totals I'm going to right click where it says gen I'm going to pick on group this time I'm going to uncheck month and then we'll do quarter instead we're going to click on okay now we have it by quarters now we have totals by quarters rather than for each month think about what we're looking at we we took all these transactions on sheet one now on sheet four just after a few minutes a couple different techniques now we have a Cordy summary by office that's a pretty powerful report that's what the pivot table does for you let me show you one other step here let's say you wanted to see where this number is coming from where where is this 2429 coming from so notice that that's for Brooklyn Center and for quarter 1 what you can do is you can doubleclick on any number I'm going to double click then it shows you all the transactions that make up that number so notice I have the office brookly Center they're all from the first quarter and uh here's how I would verify that number if I if I highlight the totals here notice how it says 2429 and that's the number that we had double clicked on so um notice how I put this in a different sheet our pivot table still in sheet 4 this time I'm going to double click on the number 414 3 where is that coming from it says manato for for uh quarter 2 it's 4143 so I'm going to double click on that shows you all the manado transactions if I highlight the totals notice how it says 4133 there so we just verify that number it's a really powerful addition to the pivot table so even what I just showed you we just made a powerful report out of these transactions we went into our pivot table and um you should watch this video a couple times and even with what I just showed you that gives you a great introduction into pivot tables we'll have more videos about the pivot tables as well