🎉Create a Basic Pivot Table in Excel – Essential Data Analysis Skill☝️

Data Translates
219 followers
I had a request underneath one of my last videos to run through the basics of pivot tables.
So your wish is my command. This is that video. So let's dive in and take a look at one of the
most useful things in Excel. So a pivot table for the uninitiated, if you're brand new to them,
is basically just a dynamic table. And what we can do with a pivot table is we can
analyze our data and view it in lots of different ways without a lot of stress.
And pivot tables are amazing for gaining a greater insight into your data and then from there
producing things like pivot charts and dashboards. Now to create a pivot table, we first need a data
set. So this is the data set that we're going to be using. Again, it's just some basic sales data
for different regions, different countries, so on and so forth. So these look to be orders that have
come in for different stores in different regions. And currently looking at this data, there is quite
a lot of it. It's very difficult for me to gain any meaningful insights. For example, maybe my boss
comes to me and says, I want to know what the total revenue has been for the Asia region. Now
simply looking at this table, I really can't tell what the total is. I could go away and maybe do
some formulas, but the easiest way is going to be to create a pivot table. So let me show you how
simple pivot tables are and remove all of the mystery. Now the first thing that's really
important that you do before you create a pivot table is you put your actual source data into an
Excel table. And the reason why this is important is that if you have your data in an Excel table,
if we were to start adding records onto the bottom, this data set might grow. Then the pivot
tables and pivot charts that we've created based off this data are going to be really, really easy
to update. So always put your source data into a table. Control T. Yes, my table has headers. Let's
click on OK. Now that I've done that, I get the table design ribbon. I'm going to give my table
a name just to keep things nice and organized. So in the properties group, it currently has table one,
which is just the generic default name. I'm going to call this sales underscore data. Worth pointing
out, you can't have any gap spaces or anything like that in your table names. Let's hit enter.
So now that it's in a table, we can create the actual pivot table. You can do this from a couple
of places. If you're already on the table design ribbon, you can click on summarize with pivot table
or alternatively, you can go to insert and you have the pivot table button just here.
So let's click pivot table. What data am I using? Well, it's picked up the correct data because I'm
clicked in it. It's the sales data range. And where do I want to place this pivot table?
Now, I always like to put mine on a new worksheet just to keep it separate from my source data so
that things don't get confused. So I'm going to choose new worksheet and click on OK. Now,
what happens here is that you get a blank pivot table reports and then over on the right hand
side, and I'm just going to pick this up and drag it over so it's a bit closer. Let's just drag this
down. Over on the right hand side, you get your pivot table fields. And this is really where we
go in and start to build our pivot table to display the metrics that we're interested in.
Now, in the top half, you're going to see all of those column headings from your source data. So
region, country, item type, so on and so forth. And then underneath, we have four different areas.
And all we need to do is drag and drop these fields into these areas to build a pivot table.
And where you drag and drop these two really depends on how you want your data laid out
and the information that you want to see. So if we're doing just a really basic pivot table,
maybe I want to let my boss know what the total revenue is for the Asia region.
I can grab the region field and I can drag and drop that down into rows. You can see as soon
as I do that, it starts to build my pivot table. I now have those regions in the rows.
I can then go through and look for, I think I have a revenue. Yes, I do. Total revenue. I'm
going to drop that into values. And now I've basically produced within a couple of seconds
the information that my manager was asking for. So it's broken it down, sum of total revenue
by the region. Currently, these numbers don't have any formatting on them. We can quickly solve that
by right clicking, going to number format. Let's make these look a little bit nicer. I'm going to
give them accounting format. I'm going to take the decimal places down to zero. And that looks a
little bit better. So what I can then do is I can add more fields or I can move these fields around.
So if I drop sum of total revenue into columns, it's going to display that in a completely
different way. And that doesn't look very good. So let's put that back down here.
What about if I want to sort this by order date? Well, I could drag and drop this down
into the rows as well. And now I can see it broken down by the order date. Now, when you drag and
drop date fields down, what generally tends to happen is that Excel will try and split it up
for you. So you then get more fields for quarters, years, so on and so forth. If you want to remove
fields, you simply drag and drop them out to get rid of them. Maybe I want to grab the country field
and drop that down into region. And now I have a different way of summarizing my data. I could move
country above region and it looks different again. I can even put country up into the filters area
and now I can choose the country that I'm interested in, click on OK, and it's going to show
me those results. Let's drag country out to filters. Maybe I want to put sales channel in columns.
And now I'm seeing it broken down by the region, but also if those sales came in online or offline.
Now, of course, there is so much more that we can do with our pivot tables. We've really just
scratched the surface here. But that is the basics of how you create a pivot table. I'll be delving
into pivot tables in a bit more detail in later lessons. But for those of you who are brand new,
that's the basics covered. If you enjoyed this video, then smash that old like button,
give me a little follow and I will see you next time.
