Hi, this is Wayne again with a topic “Advanced Excel – Creating Pivot Tables in Excel”.
In this tutorial, we’re gon na 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 gon na 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 gon na click here on the word material and then here on the Home, tab home ribbon in the Styles group, I’m gon na click on format as table. I can pick any of these styles to format my data as a table.
I’M gon na 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 gon na 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 gon na go up here and click on bands and I’ll just drag that and I’m gon na 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 gon na 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 gon na 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 gon na 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 gon na 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 gon na 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 .