Lessons

Lesson Info

Segment 24 - Excel - Pivot Tables

I'm gonna move on to drum roll tables oh yes, the tables now this is something again I think people are very daunted by and scared off there's no reason to say no they're actually kind of fun yeah literally literally twisted kind of way people are really intimidated by pivot tables because there's not really much instruction by them about dems and so I probably get more questions about oh my god, I need to learn pivot tables here we're gonna be able to teach me that and the remarkably easy so pivot tables are a tool that's used for analyzing lists that look like this and what they allow you to do is literally dragged them around and twist them and move them. And so it allows you to see this lis data in a lot of different ways because when you have a list it's kind of hard to do all the statistics on all these prices are all that how many sales I made and all of the calculations that I can do here. So what I'm going to do is I'm now on the data ribbon right here, so find the data ribbon...

and when you're on the data ribbon and actually I take it back there's also that same button is on the tables ribbon as well, so there's actually two ways to get to the pivots on the table ribbon it says summarize with pivot table and on the data ribbon it says pivot table either way I'll go ahead and click on it and it went ahead and now did it do that for everybody make a table that's already filled in yeah okay usually it comes up blank so I'm kind of curious to know exactly why it did that this time maybe it was from the table riven versus the data ribbon we're going I should clear out everything that's on this on this pivot table we're going to start completely from scratch you can see just to see what it did is it pulled my column labels with the different cities that my customers were from and the date that they made their purchase and so this is tell me how many how many customers I had on different days well that's nice for this not actually that useful to me so let's clear it all out the pivot table I'm gonna close this little window right there with that explanation but now we have a pivot table builder window we're going toe a little move that over to the right and we're gonna work in that extensively so I'm going to turn off all the check marks here I want this to be completely blank so in the field name list go ahead and just uncheck everything there is and so if when you click that button and came up blank now where with you there are four parts to a pivot table, and first I'm going to show you when the pain and then I'll show you on the spreadsheet itself there's a report filter column labels, row labels and the values and where that is is the report filters up here in row one the row labels are in column a the column labels are in row three and the values area is all the statistics that we have now before we get started with it just it's an organizational thing noticed down at the bottom that it made a new sheet, so if you were going well, where'd all my data go your data is still here on this sheet one now let's double click on that tab and the words sheet one turn blue and I'm going to call this sales so I know that this is my sales tab and I'm going to double click on the new sheet one and I'm going to call this spit it table and then that way we know which one is which now if you want to re arrange these tabs, you can just pick a pivot table and drag it after sales. Now sales is your first cheat them to the table is your second sheet, which to me makes a lot of sense you can also color code these tabs if I right click and if you are on a track pad, you can click with two fingers if you don't have a track pad, you can hold your control key down and click on it and that's for mac and there's a tab color option on that little pop up list and when I choose tab color it now wants to know what color I want to make my pivot table you pivot table tab and so you can color it anything you want I made that one red I'm going to go back to sales and I'm going to do the same thing I'm going to the tab color window is still up and I click on sales and because this is christmas I'm going to make my sales green and my pivot table read so now you can color code the tabs and that gives a little visual clue for which one you're on so far so good the first thing that I'm going to do with my pivot table is I want tio analyze my sales and how I did at each of these event because that's what I'm most curious about is which ones are good and which ones do I not want to dio again in the future yes way had to buy the table but filled with fields what do you make it to me surgically go over to the the box on the right hand side the task pain and uncheck all of the fields on the right hand side just run down the list and take all your check marks off. Thank you. All right, so now the check marks that we do want put a check mark in front of event and on mind it defaulted to putting the events down the list on down the rows that works great. Now scroll down a little further and click on item purchased and then scroll down a little further and put a check mark in front of price paid. Now what it came up with still isn't quite useful to me, so this is where it gets really interesting. This is where we start pivoting the table, so I like that it put my events across here no down here so I can see my sales from the christmas bizarre, the holiday blitz in in the festival of the last minute. I want to make this a little wider because these are hard to read, so I'm going to hold my cursor between the a and to be up in the column header appear at the top, and I can tell them in the right place because they get this double headed arrow and I'm going to go ahead and dragged that a little wider so that everything's on one or two lines just make it a little easier to read. Now what I'm going to do it is pivot this what I would like to see is what items I actually sold, so I'm going to look down and I see here account of something with an eye and these air a little small, and I can make the box wider if I want, so I don't want to count how many items were purchased. I want to see what items were purchased, so I'm going to pick that up and I'm going to drag it from values box up to the column labels box and look what it did it now changing from whatever gibberish that actually wass tio, here are all the different items that I sold, and if I want, I can wide in my columns to make him fit a little better, but now I can see what price is, how much I made on each of these different items at each of the different shows that I did see how that works so I can see at the christmas bazaar I had, I made seventy four, ninety five and health bracelets, and actually that was my grand total for all of the shows my horoscope necklace at the holiday blitz, and I did really well with those and, in fact, that's one of my best sellers for the whole for the whole week. Let's continue to pit it so you can see really how these this information can shift around. I'm going toe pick up item purchased and I'm going to drag it down from column labels down into the row labels square, so I'm just picking up that field and dragging and dropping it, and so now I put it below the event so now in row labels I see event and item purchased, so now I have a whole different chart I can see at the christmas bazaar. Here are the products I sold and here's how much money I made at each one, including a grand total let's. Flip it around, pick up item purchased from the row labels and drag it up above event, and if you need to try dragging event down below item purchased, that might work a little better. Now. My sub totals are all the jewelry, and then I can see broken down below how much I made it each show, and so you can keep manipulating these just by dragging them around and around. Now that I have item purchased in the rose, maybe all drag event up to the columns, and so now you can see why I called this, you know, twisted or twisty because you can keep dragging things around until you get the exact lay out that is the most helpful to you when I look at the value so now maybe this is nice to know how much money I made money I made on the products now I wantto see how my sales were, how many of everything that I sold so let's look back in the values box over on the right hand side and it says some of the price paid and if I click on the question mark next to it, I get a pivot table field and it wants to know what statistic I want to analyze or to summarize by and I want to count how many of each piece so I'm going to change this to count and it almost doesn't matter what the price paid was I'm going to click ok and now it's telling me how many sales I had at each of the shows so I can see at the christmas bazaar ahead eleven sales of the holiday blitz and I had fourteen sales at the portland festival of the last minute I had four sales and I can spend and see what those sales were made up of now as I've been doing this demo, the view that I liked best was when I had the two of these things switched around, so I'm going toe switch what I have in the row labels and the column labels, so I'm gonna pick up of item purchased to move it back to rose and picked up event and I'm sorry pickup item purchased moving back to columns and move event back to the rose that I find this one really helpful and like I said you're not locked into the fields that you see here you can do statistics based on color maybe I want to know with all of these items that I had what colors sold so I'm going to put a check mark in front of color and it came in as a column label right here where it says count of and I don't want that to be a count I'm going to drop that down I actually want to know what the colors were so I'm going to pick up that color and I'm going to drag it back up to a column label instead and now I can see for each of the items for example it's looking the eighteen inch necklace area and I'm going to make these columns wider because this is a little hard to read right now so I'm going to click on column eh and I'm going to scroll all the way out to a k and hold my shift key and click on a k and then put my cursor over any of the column headers and double click I should that didn't work let's try it again instead of doing the doubleclick maybe I'll do a drag between the columns and that will spread things out a little bit so what that allowed me to see here is my harsco eighteen inch necklace I can now see that I sold one blue one clear one purple, one red and one teal and if I switch in the other way I'll pick up item purchased and drag it down below color now I can see all the things I sold that were black I sold these three items and here's my blue and I sold these items in blue so it's neat to see how you can really change these around this would actually be easier to see in columns and we'll do one more switch and then we'll go on to something else I'm going tio move color from column down to roll able and put it under event and the item purchased under the color and I'm gonna pick event up and move it back to the column labels and again all why didn't he is out and so now I've got what I sold by um organized by color or again switch them around color below item purchased and now I can see each item by color and it's it's easy and it's fun I mean don't get lost in which do I have to put over here? Wait you did that too fast this should this one be above this wonder said this would be a low that one that part almost doesn't matter the take away is that column labels go this way ro labels go this way you can pile of them one under the other to group them and you can change the statistic for what's happening in the middle by going down to the values clicking on the I and changing it from some toe average to count whatever it is that you want to say and in particular nallet I switch this back over to prices it's one thing to see which product had the most sales but if I switch it back by color I can now see which color had the most sales and purple no surprise there huh s so I've got my purple was my biggest seller and clear and read where my next best sellers question from laurie she's lost a pivot table bill the window how do you reopen it once you lose what back on the pivot table again okay so you're clicked off of the pivot table it disappears if you click on the pivot table they will come back simple computer lady said thank you you did I'll try a question already oh good well done thank you okay so that's pivot tables in a nutshell again I think we need to be frightened off no, they're very useful yeah and they're fun to holly did you figure out your challenge no e I just wanted to know because this is the kind of things that's going to happen when I'm at home alone exactly it's scary to be home alone we'll have the video and although step by step will be fine and that I'm you know, like I've said before that I'm available for support calls so when you buy the course, you get that free appointment with me and you know my phone numbers public information I'm not going to say it right now but it's public information and if you're stuck you make you give me a call and we do found support and screen share support as well. All right, so we just made a lot of changes to this file you can either just to a save and replace your old one, but for those of you who are working on the original files, if you do a say that's right now then you'll have the original file just as it was when you downloaded it and if I do a save as then I can keep this final copy so that I can see both the original and my final so I'm going to call this customer purchases final and click save and now I have the original and one that with mitt at the tables the question in the chat alicia computer lady is asking can you add the data bars to pivot tables? Thanks let's go ahead to my grand total and what she's referring to when she says data bars is this on back on the home ribbon, the conditional formatting right here and I'll go to data bars, and I'm going to use this green, radiant phil. And when I did that, I included the grand total, so that actually threw off my calculation. So I'm going to undo that. I actually want to see I want to take out just because right now what it's going to do is it's going to make all of the some headers bigger than the other ones? I'm going to take out the actual item purchased from this so uncheck that so that now I'm seeing my colors. And now, yes, we can put in the conditional formatting on those cells and see get that instant visualization of which ones were the the best sellers, or if I turn off color and I go backto item purchased and move that item purchased back to my role labels, I couldn't do that same thing. Conditional formatting data bars and now aiken analyze my best sellers that way, too. Conditional formatting is definitely one of people's favorite techniques.