Hi everyone, Kevin here. Today I want to show you how you can create pivot tables in Excel. Here I have lots and lots of data that I need to analyze. Now I could build a table over on the side using formulas and functions to make sense of it, but that’s going to take a long time. Instead, we can use something called pivot tables to analyze this data in just seconds, and best of all, you don’t have to know any formulas or functions. It’s all just drag and drop. To follow along, I’ve included a sample workbook down below in the description. All right let’s check this out. Here I am in Excel, and once again, if you want to follow along, I’ve included a sample workbook down below in the description. Here I have all of this sales data for the Kevin Cookie Company, and I want to answer some basic questions. For example, how much revenue did we earn from each one of our customers or how many orders did each one of these customers place with us. Over on the right-hand side, here I pulled together a table that answers some of those questions. So, here customer one, we made about a little over half a million dollars over 92 different orders. To build this table, I just used formulas and functions, so here when I click into this cell, here we can see the function that I used. I used SUMIFS, and here for the number of orders, here too, I also entered in a function. Now of course this works just for a few specific questions, but it takes a bit of time to pull together something like this and maybe I have other questions, so maybe I want to know well how much revenue did we earn for each customer and for each product or how many orders did we get in a specific month. Now of course, I could build out this table to answer some of those questions, but that’s going to take a long time. Instead, we can use something called pivot tables. Before we can insert a pivot table, we need to prep the data. Over here on the left-hand side, I have all of my data in a tabular format, so here I have my headers and then I have the associated data underneath. Second, you’ll also want to make sure that all of your column headers are descriptive of the data underneath. Here as an example, I say rev, but I could probably be more specific and here I’ll type in Revenue. Next to that, I say our cost, but I could probably be a little more concise. Here I’ll type in cost. If we look over here, I have an empty column D and I also have some empty rows. This will make it hard for Excel to know what data to analyze with the pivot table. Here I’ll right click on this column and then go down to delete. Here I’ll select row 11, then I’ll press the control key, select row 17, right click, and then delete those blank rows. Lastly, it also makes it easier if we turn all this data here into an official Excel table and the reason why is if we add additional sales in the future, those will be included as part of the pivot table. To turn this into a table, go up to the insert tab up on top, and here’s the option to insert a table. When I hover over this, you’ll see that the shortcut key is Ctrl + T. Here I’ll press Ctrl + T and here it automatically identifies all the data that should be included as part of this table. That all looks pretty good, and right here, it also asks me if the table has headers, and my table does have headers. I’ll make sure that box is checked and then click on OK, and this is now an official Excel table. On the table design tab, over on the left-hand side, I can give this table a name. I’ll call it Sales Data and then hit enter, so this table now has a name. We are now ready to insert a pivot table. Up here under table design, I can summarize with a pivot table, or alternatively, I can click on insert and here I can click on pivot table. When I click on that, it asks me what I would like to include as part of my pivot table, and we already created a table called Sales Data and so that’s the table that I want to include. Right down below, I can decide whether I want it to be a new worksheet or an existing worksheet. Now I think it’s easier just to make it a new worksheet, so I’ll select that. Next, let’s click on OK. This now drops me onto a new worksheet called sheet1, and check that out, we now have a pivot table. If I click on the pivot table, over on the right-hand side, this exposes something called pivot table fields, and we’re going to use this pane over on the right-hand side to pull together our pivot table. Over here under pivot table fields, we have all of these different field names, so we have things like the customer, the order ID, the product, and you might recognize some of these from the previous sheet. Here I’ll click into sales data, and here we have the customer, the order ID, the product, so these column headers, in pivot table terminology, these are referred to as Fields. Over on the right-hand side, here I have that table that I pulled together on my own using formulas and functions and I want to re-create this using a pivot table but just by dragging and dropping. First, I have the customer ID, and then I have the ID number, and they’re spread out over rows. Here I’ll click back into sheet1 and let’s see how I can start building this. Here I see I have the customer and that contains the customer ID. I can click on this field and drag it down. Now remember, these were rows, so I’ll drag it down into this category called rows down below, and over on the left-hand side, here I can see my pivot table start coming together. Here I see all of those customer IDs. Next, I want to add the revenue next to the customer. I’ll click back into sales data and here I have the total revenue. This is the sum of the revenue by each individual customer. Let’s go back to the pivot table, and over here, I have a field called revenue. I’ll click on that and this time I’ll drag it down to values. You drag something down into values if you want to calculate something, so if you want a sum, if you want a count, if you want an average. When I release, here you see that it automatically gave me the sum of revenue, so here for customer one, I have a little over half a million dollars, so here $521,000, and if I click back into sales data, here you see that that matches exactly. Of course, the formatting is a little different. Here I could right click on this cell, go down to number format, and here I can set it to currency. I’ll remove decimal places and then click on OK, and that looks pretty good now. Let’s now click back into sales data and the last column is the number of orders. Let’s go back to the pivot table, and when I look through all the fields, here I have one called order ID. Once again, I want to calculate something, and I want a count. I’ll take the order ID and here I’ll drag it down to values, and this adds another column to my pivot table, but this doesn’t match what I have on sales data. Here I had 92 orders for customer one, but here I have a massive number. What happened? Well, if we look down at values, this gave me the sum of all of the order IDs, so it summed up these order IDs. Now that doesn’t make any sense. I’ll go back to the pivot table and here I can right click on this, and I can go down to summarize values by, and currently it’s set to sum, but here I can change it to count. I could also change it to average, max, min, product, so I have all of these different options. I even have more options down below. I’ll select count and here now I have a count of the orders, and if I go back to sales data, that now matches up exactly. So here I was able to build the exact same view simply by dragging and dropping my mouse. I didn’t have to enter a single formula or function in. Once again, pivot tables are so easy to use. Now that I have my pivot table in place, it’s so easy to analyze your data and to look at different views of your data. Let’s say maybe I no longer want to see the count of order IDs, here I simply click on that and I could drag it out and that updates my pivot table. Now I just see the revenue. Maybe I just want to see the total revenue. I don’t want to see it broken out by customer. Here I can see the total revenue was just under $5,000,000. Here I can drag the customer out. You see that X appear, and when I release, now I just see the sum of the total revenue. Now to pull the customer back in, once again, I click here, drag it down to rows, and there I can see it broken down by customer again. So, it’s so easy to switch up your view depending on how you want to look at your data. When I look over at my table on the left-hand side, currently it’s sorted by the customer number, and not the revenue, but I would rather see well which customer drove the most revenue. Here I can click into this column and then right click, and here’s the option to sort, and here I can sort from largest to smallest. Here I see that customer three drove the most revenue for us. I can also go into this column and here I can right click and let’s say I want to sort by the customer ID again. Here I can sort from smallest to largest and that brings me back to that original view. I can also custom sort this list too. Let’s say maybe I want to see customer four at the top for whatever reason. Here I could right click, and all the way down here, there’s the option to move, and here I can move 4 to the beginning, but here I could choose the position. I’ll move 4 to the beginning, and we start with 4, followed by 1, 2, 3, and 5. I can also press control + X to cut this, and I’ll go down here, and press paste and that pastes in customer 4 back down here. So, I can define the order in which all of these items appear in my pivot table. Along with sorting a list, I can also filter this list. So, let’s say I just want to see customers 1, 2, and 3 to see how much revenue they earned. I can click on this dropdown, and right here, I can uncheck select all, and here I’ll select 1, 2, and 3 and then click on OK, and here I just see the revenue for those 3 customers. Here I’ll click on this, and I can now clear out the filter, and I see all five of my customers again. Now let’s say I want to see the revenue just for chocolate chip cookies, so I don’t want to see the total revenue. Over on the right-hand side, there’s a field called product, and if I click back into sales data, product includes the type of cookie. I’ll click back into my pivot table and here I’ll select product and I can drag it down to filters. When I release that, over here on the left-hand side, there’s a new section to my pivot table where I can now filter. I can click on this drop down and here I can select chocolate chip. Down below, I also have the option to select multiple items, but I just want to see chocolate chip. I’ll click on OK, and this updates all of the revenue just for chocolate chip. To remove the filter, over on the right-hand side, I can select product and drag that out and now I see the revenue across all of my products once again. Looking up above, here I see the revenue that the customer generated, but I would also like to know the percent of the total revenue that that customer contributed. Over on the right-hand side, here I’ll select revenue and drag it down to values again. So here I placed in revenue twice and currently it’s exactly the same. I have the revenue in this table two times. Here I’ll right click on this and there’s the option to show values as and here I have all of these different options depending on how I want to view this data, and here I want to see the percent of the column total, because I want to know the percent revenue contribution, I’ll select this, and here I can see that customer 3, they drove $1.4 million of revenue and that’s about 30% of the total amount of revenue that we earned here at the Kevin Cookie Company. This data is really good, but these headers right up above, they just don’t really make much sense. Sum of revenue2, that doesn’t really tell me what this is. I can click on this cell and I can type in a custom header. Here I’ll type in % of revenue and now that makes a lot more sense. Over here it says sum of revenue. Maybe I just type in total revenue, just so that column makes more sense as well. That’s looking pretty good. Along with just seeing the revenue by customer, maybe I also want to see the revenue by product. Over on the right-hand side in all the fields, here’s the product, and I can press on it and drag it down, and here I’ll place it in rows under customer. Here I see the customer and then I see the product under that customer. If I click on this icon right here, I could collapse the group and here I can expand it. I can also change the order. Over here, I can place product on top of customer. So here I see chocolate chip and then I see all of the customers who sold that product. In the bottom right-hand corner, just to simplify things, I’ll remove percent of revenue from values. So here, once again, we see the product and all of the customers and the revenue. Now I can also take product and I’ll move that over to columns, so here we see the customer as the row and here the product as the columns and the value we see the total revenue, and one thing you might have noticed, this area down below, this maps to what we see in the pivot table. So, here I have customer over here in rows and then we see customer over in rows. Product shows up as the columns and here we see product as the column, and then values appear right here, and here we see the values in the pivot table. It’s really easy to manipulate the data that appears in the pivot table, and to look at your data in different ways, but you can also change the design of the pivot table. With your mouse within the pivot table, up on top let’s click on the design tab, and here we have a bunch of different options. We can change the pivot table style, so here I could go with red, or maybe yellow, or I could go back to that blue color. I could also show do I want banded rows or banded columns. I could turn that on or off. Over here, I could also decide, do I want to show subtotals? Should we show grand totals? Here I could turn that off and there you see that the grand total disappeared, or right here, maybe I want to turn that back on. Here I’ll turn it on and here I see the grand total once again. I can also insert a chart to visually represent the pivot table. Up on the top tabs, I can click on insert, and here I can choose one of these charts, or alternatively, I can press Alt + F1 on my keyboard. That’s the shortcut key to insert a chart, and here I see a chart that represents my pivot table. Let’s say maybe I no longer want to see product. Here I can drag that out and you’ll see that the chart automatically updated to reflect what’s in the pivot table, so these two are tied together. To make it easy to analyze data, up above, I’ll click on pivot chart analyze, and there’s an option to insert a slicer. I’ll select that, and here maybe I want to very quickly evaluate how much did different types of products earn. I’ll check that box and then click on OK. So here I see all of my different products and let’s say I want to see revenue by chocolate chip. I can click on that and here it updates the table and the chart to show me revenue just for chocolate chip. I can press the control key if I want to select multiple items, so maybe I also want to see sugar, and maybe I select oatmeal raisin, and there you see the table and the chart both update to reflect my selections in the slicer. I’ll click back into the pivot table and then go up to pivot table analyze. Here I also have the option to insert a timeline. I’ll click on that, then check date, and then click on OK, and this inserts a timeline. Here I can now select a time period and it’ll automatically update my table and my pivot chart to reflect this date period that I selected. Here I can click on this icon on the timeline and this icon on the slicer and that’ll return it to the original state. This analysis is great. I have a view of my customers and the revenue, but I also want to do some additional analysis, but I don’t want to get rid of this original view. Here I can copy the pivot table and I could scroll down the sheet and paste it and that’ll give me another copy of this pivot table. For this new pivot table, I want to see the profit margin. Over on the right-hand side, I already have the revenue and here I can drag in the cost. That gives me the sum of the cost, but I don’t have a field for margin. Here I’ll remove cost. So how can I see the margin here? I need a formula. I need to take the revenue minus the cost, and that’ll give me the profit. With my mouse in the pivot table, I’ll go up to the top tabs and select pivot table analyze. Right here, there’s the option for fields, items, and sets. I’ll select that and there’s something called a calculated field. I’ll select that. This opens up the calculated fields dialog and this allows me to enter in different formulas. For the name, I’ll type in profit margin. Right down here, I can type in the formula, and the formula for the profit margin is the revenue minus the cost. I’ll click on add and then click on OK, and over here in the fields, you’ll see that there’s now a new field called profit margin, and it automatically adds it to my pivot table. Here I could remove the revenue, and now I can see my profit by customer. I’m now satisfied with all of these different pivot tables, but I want to make sure that when I add new data that these views update. I’ll click back into sales data, and I’ll go to the very bottom of the table, and here I’ll add a new sale for a new customer, customer #6. I’ve now entered all the details for the new customer and the order. I’ll go back to sheet1 and here I have my pivot table, but it doesn’t yet show me customer #6. With the pivot table selected, I’ll go up to pivot table analyze and here’s the option to refresh. I’ll click on that and here we see customer 6 appears now in this table. If at the very beginning we hadn’t created a table, I’d have to go up to change data source, and I’d have to select the area of the data that we want to analyze, but because we made that a table, all I need to do is add that new data, go up to pivot table analyze, and then I can refresh the view. One of the beautiful things about using pivot tables is it doesn’t at all affect your original source data. So, here’s an example. I can delete this pivot table altogether and here when I go back to my original data, it’s untouched. Throughout this entire video, we’ve been looking at how you can create a pivot table from scratch, but you can also have Excel recommend a pivot table to you. Up on the top tabs, let’s click on insert, and here’s the option for recommended pivot tables. When I click on that, that opens up a pane over on the right-hand side, and here we see all of these different interesting views of the data. If I like any of these pivot tables, I could add it as a new sheet or over here I could select an existing sheet. Back on the home tab, all the way over on the right-hand side, there’s also the option for analyze data. When I click that, that will look through all of my data in this table, and here too, it’ll also find different interesting insights, and here it automatically generates a pivot table. Here I could insert it, and as I scroll down, you’ll also see that it automatically generates pivot charts, and one thing that’s really neat is at the very top, I can even ask questions about my data. Here are some different examples of questions I could ask, like how many different products do we even sell here? I’ll select that as an example, and here it says that we sell six different types of cookies. If I like this view, here I can click on insert, so you don’t even need to know how to create a pivot table from scratch. As long as you have the right question, you can ask your question here and Excel will do all the heavy lifting for you. I figured I’d tell you about this one at the very end, otherwise you might not have watched the full video of how you can create a pivot table on your own. All right, well that’s how quick and easy it is to analyze data in Excel using pivot tables. To learn even more about Excel, be sure to check out my Excel for Beginners course. We run through things like pivot tables and many other topics. By the end of the course, you’ll know all the fundamentals of using Excel. To watch more videos like this one, please consider subscribing, and I’ll see you in the next video.