Today, we’re going to learn how to make a pivot table in Excel. Let’s dive in. Here I am in Microsoft Excel. If you’d like to follow along, I’ve included a link to this workbook down below in the description. Before we can make a pivot table, first we need to prepare the data. You’ll need to make sure that your data is in a tabular format. So, what do I mean by that? Well, you need columns with a header up on top and all of the associated data down below. Next, I also recommend turning your data into a table. Now this is optional. It’s not mandatory, but if you plan on adding additional data or removing data, it’ll be a lot easier to refresh your pivot table. To insert a table, let’s go up on top, click on insert, and here we see the option for table. Click on that. It should automatically identify all of your data. Here my table has headers, then let’s click on okay. Now that we’ve inserted a table, we’re now all ready to insert a pivot table. Up on the top tabs, let’s click on insert and all the way over on the left-hand side, we see the option for pivot table. Let’s click on that and here it’s now identified the table with all of this data. Right over here, we can insert as a new worksheet or an existing. I’ll select new and then down below, let’s click on okay. This now drops us into a brand-new worksheet and over on the right-hand side, you’ll see that we have a new pane that appears called pivot table fields. And on the worksheet itself, we have this area that’s called pivot table. We can now start building our pivot table. Now one thing to note, if you click outside of the pivot table, you’ll notice that pane disappears. To get it to reappear, simply click into the pivot table and that’ll bring the pane back. Right up on top, we have all the different pivot table fields, the date, the salesperson, the region, the product, the sales amount, and the quantity sold. All of these different fields correspond with the column headers on the previous worksheet. So here you’ll see all the different headers and they align exactly with all these different pivot table fields. I’d like to know which salesperson here at the Kevin Cookie Company generated the most revenue and using a pivot table, that’s really easy to answer. Right here, I have a field called salesperson. I’ll simply press and hold on that and I could drag it down to this area down below that says rows and then I’ll release. Over on the left-hand side, you’ll see that all the different rows are now filled up with names of people from the sales team. Next, I want to know who generated the most revenue and here we have a field called sales amount, or this is basically the revenue. Now I want to calculate something. I want to know the total amount of revenue and anytime you want to calculate something, you take a field and you bring it down to this area called values. Here I’ll release and over on the left-hand side, we can now see who generated the most revenue. It looks like it was Alice with $14,000 generated. This shows me the sum of sales by all the different salespeople, but you can also summarize the data in different ways. Here I’ll right click on this and we have the option to summarize values by. By default, its sets to sum, but you could also pull together a count, an average, a max, a min, or product. So, let’s try max. If I click on that, here I can see the max order size for Alice. It looks like her largest sale was for $971. Not bad. Here I could right click on that again. Let’s go to summarize values by and I’ll switch it back to sum so I could see the total or the aggregate of all of her revenue generated. This view is currently showing me the highest sales to the lowest sales, but I can also sort this differently. Here I’ll right click and right here we have the option to sort. So, I could also sort from smallest to largest. It looks like Diana only had about 11,000, although to be fair, it’s not too far behind Alice. Right here, I can right click again, go to sort and we can switch back to largest to smallest and there I see Alice on top. Along with knowing how much revenue each salesperson generated, I’d also like to know what region those sales originated from. Over on the right-hand side, in the pivot table fields, I have another field for region. Now I can view this in many different ways. I’ll click on region and let’s first try bringing it over to columns. Here I’ll release it and over on the left-hand side, you’ll notice that it’s now added columns with all of the different regions. So here for Alice, I could see how much revenue she generated in each one of these regions along with a grand total. Now I may like this view, but maybe I want to try a different view. Over here, I could take region and I could place it in rows under salesperson. And when I do that, look at how the view over on the left-hand side adjusted. Now I have all of the regions as rows under Alice. Now if we look over here, we have the salesperson as the top level and then the region as the second level. And just like we see it over here, this view on the left-hand side matches that. Maybe I’d actually like to see the region on top of the salesperson. So, over here, I’ll take the region, move it up above the salesperson and now we see the region first followed by the salesperson. Now on second thought, I don’t know if I need the region and I’d like to remove that field. It’s also really easy to remove a field. I’ll press on region and I could simply pull it over to the side, release and that removes it and that brings us back to the original view that we had. You could view your data in all sorts of different ways, whatever way you prefer. Next, I’d like to see how much revenue was generated just for chocolate chip cookies. Over on the right hand side in the fields, there’s a field for product. I’ll take this and then drag it down to filters. And when I release, over on the left-hand side, there’s now a filter based on product. When I click on this dropdown, here I can see all the different products we offer here at the Kevin Cookie Company. I’ll click on chocolate chip. Down at the bottom, you can also select multiple items, but I just need one, so I’ll select this and then click on okay. And there I see the revenue filtered just to chocolate chip cookies. And it looks like actually Eve sold the most. To remove this filter, up above, I’ll click here and I can return it to all and then click on okay, and this again shows me the total revenue. To remove the filter, over here, just like we saw with Rose, I could drag it out and release and that removes that filter. Over on the left-hand side in my pivot table, I can see how much revenue they generated, but what I’d really like to see is the percent of total revenue that each salesperson generated. To pull that view together, over on the right-hand side, here I have sales amount and I’ll press on this again and then drag it under values. This adds a second column with the sum. And right now, I see the same number, but I want to visualize this data differently. I’ll click into this column and then I can right click and within this menu, down here, there’s the option to show values as, and I have all of these different options to show my data. Now I want to know the percent of the total revenue. So right here, there’s the option for percent of column total. I’ll click on this and here I can see the percent. So, it looks like Alice drove about a quarter of our revenue. That’s pretty good. Right up on top, I have these column headers and it doesn’t really make that much sense. It says sum of sales amount 2. Luckily, it’s really easy to rename these. I could simply click into that and then I could type in percent of total revenue and there that now describes what’s in this column. You can also adjust the look and feel of the pivot table. Up on top, let’s click on the design tab and over here we could adjust the coloring. You could also add banded rows or here banded columns and down below you’ll notice that we have a grand total at the bottom. Here, I could click into grand totals and I could turn that off or I could also bring that back on. Pivot tables are great, but sometimes it’s easier to visualize the data as a chart. Right up on top, let’s click on insert and over here you could insert any type of chart. So here I’ll insert a column chart. Let’s select this one right here and this is now tied to my pivot table. So over here, let’s say I remove some of these fields. Say I remove this and remove that. You’ll notice the chart automatically updates. Let me pull the sales amount back in and there it appears on my pivot chart. Now one of the really neat things that you can do, you can also add slicers. So, with the pivot chart selected, up on pivot chart analyze, there’s the option to insert a slicer. Let’s insert that and here I could select what I want to slice my data on. Let’s say I want to see it by region. I’ll select that and then click on okay. This now adds a slicer for region. So, let’s say I just want to see the total revenue for Los Angeles. Here, I’ll click on that and look at that. The chart automatically updates based on that region. Over here, I could turn that off and that brings back all the revenue for all the different regions. One thing to call out before we wrap up, if you bring new data into your pivot table, it won’t automatically update. So over here, I’ll click into the pivot table and when you click in, up on top, let’s click into pivot table analyze and here in the center, you have the option to refresh. So, as you add new data to your table, simply click on refresh and that’ll update the view that you see here. All right, you now have an incredibly powerful tool to analyze your data. If this video helped you, please like and consider subscribing.