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.
