Advanced Excel - Creating Pivot Tables in Excel

in this tutorial we're gonna learn how to use pivot tables in Excel and for some reason pivot tables have this reputation as being kind of hard to use and at first it probably will seem that way to you but after watching this video I hope that you'll see them as being not too complicated to use and for this tutorial I've created a spreadsheet that is an inventory of synth-pop CDs let's say that I own a CD store that sells exclusively the best kind of music ever made which is synth-pop I don't have a store like that maybe I should but I don't but what I've done here is I've created this spreadsheet that lists some of the important information that I want to track for my hypothetical synth-pop CD store I have listed here several bands some of these no doubt you've heard of like Depeche Mode or perhaps erasure OMD but there's also some here that are a little bit lesser-known but I think are really excellent next we have albums and these are their most recent albums from these bands these are albums that I've recently picked up myself and listen to and really enjoy next we have a column for genre it's all really synth pop but for some reason some of these get labeled as rock for example The Killers and they do have a mix of rock and synth pop and new wave kind of blend it all together but anyway we have the genre listed an item number which is more of an internal number just for my hypothetical store to use we have the price of the CD and this incidentally is the current actual price of the CD on Amazon next we have the quarter and so I'm tracking each quarter of the year and tracking how each of these CDs does during that quarter next I have the number of copies sold for each album in each of these quarters and then I have a formula here to calculate the total number of sales in other words the amount of money brought in and it's a simple formula number of copies sold multiplied by the price so this is a nice useful spreadsheet to help me track my small business and what are the big moneymakers for my business the problem is with all of this data it can be kind of hard for me to drill down and to really see certain information like for example how did I do in the first quarter all together with all of these CDs and their sales how did my business do in the first quarter well that's a little bit difficult I would have to maybe copy-paste each of these first quarter sales numbers into another spreadsheet or another part of this spreadsheet and then I'd have to do a formula to calculate that number or another example what if I wanted to know specifically how well did the darkwave music that I sold how well did it do or what if I was selling more than just one CD by Depeche Mode what if I was selling two different CDs or three different CDs by them what if I wanted to calculate the total number of copies sold of Depeche Mode CDs regardless of what the album is could I calculate that on my own yes I could I could create a report basically just by highlighting copy/paste but honestly that could take quite some time to do and it might be likely that I would make a mistake and it's just a lot of work and effort but fortunately a pivot table can really help me in this situation just to give you a quick definition of a pivot table a pivot table is an excel tool that allows you to reorganize and summarize certain data in the spreadsheet and specifically in selected columns and rows of data and it not only reorganizes and summarizes that data but it produces a report a report that is going to be helpful to you and one important thing to recognize about pivot tables is that they don't really change any of your data when I create this pivot table in just a minute it's not going to change the data in my spreadsheet this is all going to stay intact nothing's gonna be changed at all it just helps me to look at this data in a new way so let's get started now the first thing to consider when you're about to create a pivot table is that it's very important that your data be organized well it really does need to be a list you need to have columns with headings or titles so that's what I have Band album genre etc and then a list of items and as you can see they can be repeated items that's fine but it needs to be a vertical list also it's important that you not have any blank rows sometimes for whatever reason people end up with a blank row in their spreadsheet and that's not good if you want to use a pivot table so before you use the pivot table tool make sure that your data is good that there's no blanks in the data so I'm going to delete that row to get it back into a condition that will work well also you need to be careful about extra data so for example in this spreadsheet what if I had over to the right side just some notes written here like maybe need to update maybe that's just a note to myself that I need to update these numbers or maybe I have down here the word total and then I've put in a formula that adds everything up okay you don't want to have extra data like that either to the side or underneath your data you need to have a nice data set that doesn't have any extra unnecessary information around it okay so the next recommendation that I have before you use the pivot table tool you don't have to do this but I highly recommend it and that is you need to format your data as a table so to do that all I have to do is click somewhere inside the data so I'm gonna click here on the word material and then here on the Home tab home ribbon in the Styles group I'm gonna click on format as table I can pick any of these styles to format my data as a table I'm gonna go with this one then it wants me to double check that I'm getting all of the data for the table and it looks pretty good to me but you could change these numbers if you needed to yes my table has headers that's these column titles across the top I'll click OK and look it formatted my data as a table I like how that looks so even though you don't have to do this when you're using pivot tables it is a good idea to format your data as a table and the reason why is because that way now that it's a table let's say I add another CD to my inventory okay let's say Brandon Flowers the desired effect as I add in this information notice that it's adding it directly to the table it recognizes that it's part of this data set and it formats it along with the rest of the information and not only that but when you use the pivot table the information in the pit table will be updated when you add additional in this case CDs to the table okay so let's create a pivot table for this table full of amazing CDs to do this all I have to do is go up to insert and choose pivot table and right away Excel wants me to give it some information about the pivot table and notice that the first thing that's asking is if the data is a table or a range or if I would like to use an external data source in this case I want to use a table it is a table and it guessed that I wanted to use table three and that is this table Table three if it guessed wrong you could pick a different one but in this case it worked well it guessed the right table most likely because that's where my mouse was next I'm supposed to choose where I want the pivot table report to be placed somewhere in this existing worksheet if so I'm gonna have to specify the location for me I almost always choose new worksheet that way it just gives me another sheet and it puts the pivot table there on the sheet it's just cleaner that way okay so I'm ready to create the pivot table I just click OK and look it created another sheet for me down here and it gives me a little bit of instructions to build a report choose fields from the pivot table field list and that pivot table field list is over here at the right you can see that a panel opened up on the right and this is the pivot table fields panel or pane and what we have here is a list of the column headings or column titles that I had typed in this original spreadsheet so band album genre etc band album genre and then down below I have these four areas filters columns rows and values and what this is for is Excel is basically asking me in this pivot table report that I'm about to make what do I want to be arranged in rows what do I want to be arranged in columns and what values do I care about in this report and then finally what filters if any do I want to apply to this report so let's say I want to know which bands were my bestsellers in the first quarter of the year well the column for bands is going to be important so I'm gonna go up here and click on bands and I'll just drag that and I'm gonna put that in the Rose box as soon as I did that look what happened I got a list of all of the bands now what data do I care about well I want to know the total money brought in so I click on sales and I drop it down in the values box down here so now I can see for each band how much money was brought into my hypothetical small business now maybe I decide no that's a mistake I don't necessarily want to know the amount of money I just want to know how many units I sold so copies sold would be the one to drag down there and now that changes my pivot table it's showing me different information now honestly though I think I would rather see the sales so I'm gonna put that one back but remember in my example I wasn't interested in the total sales I was interested in the total sales for each band in the first quarter so quarter is also important to me and I'm gonna drag quarter this time into the columns box so now I can see each band and each quarter how much money was brought in for selling their CDs now if I had dragged quarter down two rows instead of columns what would that have looked like I'm gonna remove that field so that you can see I'll drag it down two rows underneath band and so now the data is still the same it's just arranged differently on the screen I have the first band here black audio and it's in a row but I also put quarter in a row so it listed the four quarters that are associated with black audio right underneath black audio and so Excel is really smart about this it figures out that these quarters in the original spreadsheet are obviously referring to quarters for black audio and so it keeps that data together and then covenant the sales quarters for covenant are listed there Depeche Mode the killers on and on so I just wanted you to see that that the same data can be illustrated in different ways based on the box that you drag the column name into alright now the last box that we need to think about is filters so I'm gonna drag Quarter down to filters as well now as soon as I do that look what happened the column title Quarter can't be in both of these boxes and so it disappeared out of the columns box and it moved to the filters box also look what it did to my data it's no longer spread out by quarter but that's okay I've basically applied a filter here here at the top it says quarter all but if I click on this drop-down arrow instead of all I could try selecting one and now it shows the first quarter sales for each band it looks like The Killers was the best-selling band in first quarter for my hypothetical company their new album wonderful--wonderful is pretty wonderful I think and you should check it out if you haven't already but anyway I hope that you can see how useful this is now of course these pivot table reports can get pretty complicated if I wanted to I could include the album down here in the rows I could include the genre in the filters so I can filter out let's say all of the music except for synth pop okay so that reduced it down dramatically I could put price in the columns if I wanted to and so you can really get some complicated pivot table reports going here in a future tutorial I'll show you another way to create pivot tables and it's kind of a shortcut a lot of people find it to be easier but I do think it's important if you're gonna use pivot tables to learn how to do it the right way the old-fashioned way I guess of manually selecting everything that you want and organizing your pivot table report the way you want it to be using this pivot table fields panel so please watch for that future video I hope that you found this video to be helpful if you did please click the like button below and consider connecting with me on my social media websites like Facebook Twitter and Pinterest and definitely do subscribe to my youtube channel for more videos about technology for teachers and students and watch for another video from me at least every Monday if you're interested in learning more about any of these bands like I said at the beginning of the video these are the most recent CDs by each of these bands and I think it's great music so if you want to check it out look in the video description below and you'll find links to each of these CDs

Channel: Technology for Teachers and Students

Views: 2,043,481

Keywords: excel pivottables, excel pivot tables, using pivot tables, excel tutorial, advanced excel, using pivottables, using pivot tables in excel, using excel pivot tables, how to use pivot tables in excel, how to use excel pivot tables, advanced excel tutorial, creating reports in excel, making excel reports, creating pivot tables, creating pivot tables in excel, how to create an excel pivot table, pivot tables for dummies, pivot tables for beginners

Id: BkmxrvIfDGA

Length: 13min 11sec (791 seconds)

Published: Mon Jan 22 2018