Advanced Excel - Creating Pivot Tables in Excel

In this tutorial we're going to 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. 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 listened 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 blended 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 money makers 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 going to 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 going to click here on the word material, and then here on the Home tab, Home ribbon, in the Styles group, I'm going to click on Format as Table. I can pick any of these styles to format my data as a table. I'm going to 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 pivot 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 it'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 going to 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 best sellers in the first quarter of the year. Well, the column for bands is going to be important. So I'm going to go up here and click on bands, and I'll just drag that down, and I'm going to put that in the rows 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 going to 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 going to 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 to rows instead of columns, what would that have looked like? I'm going to remove that field so that you can see. I'll drag it down to 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. All right, now the last box that we need to think about is filters. So I'm going to 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 going to 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.