In this screencast, I'm going to introduce you to these really nice tables in Excel known as Pivot Tables. I'll be working with this file called Cookies. The first thing I'm going to do is I'm going to convert this to a table. This actually is not required for pivot tables but I'm just going to go ahead I like to do this, so I'm formatting this as a table. And then I'm going to add a Sales column here. The Sales column is just the quantity times the price, so we've done that before. And then I'm also going to just format this to be Accounting. What a pivot table allows us to do is to both filter but also compare and sort of cross-reference different things in this table. So let's just make a quick pivot table. You can go up here to Insert > Pivot Table and we can select the range. That's the range that we just made with our table. I'm actually going to put this on the existing worksheet because I want to be able to compare this side by side. I'm going to click on Cell H2 and click OK. Now over here on the right you have all these different options for PivotTable Fields. So in order to start building a pivot table we need to drag and drop these different fields. The fields on the lower right here are Filters, Columns, Rows and Values. Let's just start out simple. I'm going to go ahead and drag Seller down to the Rows. And you see here in my pivot table I've just got all the sellers in the Rows. So it's going through our table and looking for unique sellers. Now this is not really showing much, so we have to kind of decide what we want to output. The first thing I'm going to do is just Cookie and I'm going to put that down in the Sum Values field down here. This is actually just summing column C for each of the sellers. So you see here that Charlie has made three total sales. So essentially Count of Cookie is the number of transactions of each of those sellers. Let's go ahead and remove this Cookies. You can either drag it over or you can deselect the boxes here. And the next thing I'm going to do is I'm going to add in the quantity. So I'm going to put in Quantity you see down here in that field, it's the Sum of the Quantity. So now what we have here in our pivot table is the total sum of the quantity, so this is the total boxes that each of the sellers has sold. So a pivot table is really nice to kind of summarize and to visualize data. This is very similar to using COUNTIF and SUMIF functions that we've done in some previous screencasts. So now I'm going to remove Quantity here. I'm actually going to add in Sales. We can select that by default it goes down here into the Sum Values, and now this is total sales. And you see up here it's the total sum of the Sales column, and so for each of the sellers you can determine total sales. Now what if we wanted to itemize by cookie type? I can go over here and I can drag down Cookie into the Columns field now and now what it's doing is putting columns, the different types of cookies in columns. We have our sellers in the Rows and then it's itemizing the sales. So now down here is the sales for each of those cookies for each of the sellers. And I can remove Sum of Sales and maybe we wanted quantity instead, so I can drag down Quantity. And now this is the total boxes that each seller has sold of those different cookies. Now, let's talk about what this fourth upper left field does the Filters. So I'm going to drag Cookies from Columns over to Filters. You notice it's removed that but now what I have is at the top I have a cookie filter. Right now it's showing All. But if I wanted to just filter by Nutty Butter, for example, I can select that and now this is saying for each of our sellers how many boxes of Nutty Butter have we sold. The filter is nice, but we can also move that filter down to the Rows, and we can put that below Seller. What that does is it sort of has sub categories for each seller showing Chocolate Chip, Nutty Butter, Snicky Doodles for each of the sellers and kind of an itemized quantities of each of those boxes here. Now keep in mind that's really the same as if I put Cookie back in the Columns, it's just a different way to visualize it. So here we have the cookies in separate columns but now if I drag Cookies back to Rows we have it sort of as sub categories. So depending upon how you want to visualize your data and present it you can move these different fields to the different areas. I'm going to go ahead and remove Cookies and what I'm going to do now is I'm going to drag Buyer into the Columns. Now what this tells us is how many boxes of cookies were sold from each of the sellers to each of the buyers, so we can sort of look that up in this table here. We can also add the Cookies to the Rows and that tells us then an itemized list how many Chocolate Chip boxes went to each of the buyers and so on. Or we could pull that Cookie filled back up into Filters and now if we wanted to just itemize by cookie type we can select it from the Filter. I'm going to remove Cookie. This is total sales, so I'm going to remove Quantity, and we're going to drop down Sales. But now we're going to swap Seller and Buyer because we want to know on the kind of in the Rows how much each of the buyers owes each of the sellers. All right, so this would be the total sales then, the total money that each of the buyers owes the sellers. And finally once the cookies come in we can determine how distribute them to the various buyers. So I'm going to remove Sellers from Columns and I'm going to put the Cookies in the Columns. And this is not the Sales but this is going to be the Quantity. Now when we set up this pivot table it's telling us how many of the boxes of each cookie goes to each of the buyers over here. And this will then allow us to easily distribute those cookies, the various boxes to the buyers. So hopefully this screencast gave you a better idea of how we can use pivot tables in Excel to visualize data.
