In this video, we're going to create a basic pivot table. So, first, what is a pivot table? Well, a pivot table is a feature in Excel that lets you
summarize large data sets, similar to this example that we have here,
and, it lets you summarize it in a way that'll be easier for you to conclude things from it and to be able to just really
get different information from the data set. And you'll see what I mean a minute. So, first we'll make sure everything is formatted correctly.
So, we have - all of our columns have headings on all of the headings are together.
What I mean by that, is let's say we have all of this information,
um, columns A through G and "Media Cost" is way over here,
well, that's not going to work with pivot tables. If you have empty columns or empty column headers, you'll
get on error we just need to fix that. So, we'll move...just drag that back over so that everything is together.
And we'll want to make sure that
our cursor is just anywhere on the dataset. So...it is. So we go to the Insert tab and choose Pivot Table.
And it automatically highlights what it perceives to be our dataset and, for this exercise, this is correct. If you have a different data set, then,
obviously, you would just adjust that where it says "Table Range"
and then we can also choose - do we want our pivot table in a new or an existing worksheet.
For this exercise, it'll be easier in a new worksheet. So is this good and we just hit OK.
And you can see where the skeleton of our pivot table, um, is now populated here in columns A through C. And then we also have
our field settings, which include all of the headings from our data set. And then, down at the bottom, we have boxes for Filters,
Columns, Rows and Values. So
to get started on this example, let's say we want to know how many of each product was sold by year. So we want to choose "Year"
and we'll put that here in the Rows and you'll see how 2015 and 2016 have populated in column A.
Then we want to add the Products,
because that's what we want to see. And you see now we have Blouses, Boots, Dress, Pants, etc, by year.
And then we want to know how many were sold. So we choose "Quantity Sold" and we move that
over to the "Values" box. Now, this Values box is where the calculations happen.
Notice how this automatically defaulted to SUM. If I click on it, I can choose "Value Field Settings" and I
can choose SUM, COUNT, AVERAGE, MAX they have several options to choose from, but for
this exercise, we just need to know the total quantity. So SUM is perfect.
But we do when to format it differently. So I'm going to choose Number format - Number, Add a comma
and choose OK. And you see now we can see that Quantity Sold is a little bit cleaner, um, cleaner to read with the commas. And let's see
what else do we want to see? Maybe we'll also want to know how much we spent in Media Costs? What did our advertising cost us?
So we can just drag "Media Cost" over to the same, um, Value Setting and we'll want to make that dollars. So
Number format again,
OK. And now we know in 2015 we spent about $307 million in media costs and then you can see, by Product, um,
what that media cost broke out to be. So let's say we want to...
we want to see what the media source was. Where was it that those dollars come from. Again, you just drag it down and now you can
see by Blouses in 2015 how much, um, how much was sold via e-mail and what did it costs for us to sell, um,
through the e-mail marketing channel. But you know what? Now there's just a lot of
information here, so let's move the Year to the Filter box. And you see how doing that
caused this Year field to move right above the pivot table information and it says "(All)"
In that drop-down, you can choose the Year
and let's say we just want to look at 2015.
So now the information in our pivot table is only for 2015.
But...
maybe there's another way to look at this. Let's say we want Products to be in Rows and we want to have Columns
be at the top or we want to have Media Source be at the Columns. Well, we just drag that over from Rows to Columns
and you can see how it changed the pivot table so now we see Quantity Sold, um,
by Media Source and then Media Cost by Product. So we can say $4 million dollars
e-mail, um, for blouses and so on.
And let's say, you know what? Maybe we don't want all of that information. It's just as simple as grabbing what you don't want from here and
moving it out. So, let's take Media Cost out and we just see Quantity Sold. And because we have the Media Source at the top,
we can now tell that we sold a grand total, in 2015, of 165,000 blouses. But we can now see by Media Source
how those sales happened. So, pivot tables let you move your data headers around / move your data
around and summarize it, um, in different ways so that you can get to
whatever information you're trying to deduce from your dataset. And, so I hope you enjoyed this pivot table exercise and happy pivoting!
And, if you have not yet subscribed to this channel, please do so we will be sharing additional videos very soon. Thanks! Buh-bye.