馃攳
Excel Pivot Tables EXPLAINED in 10 Minutes (Productivity tips included!) - YouTube
Channel: Leila Gharani
[0]
Why use Excel Pivot Tables?
[3]
If you want to get insights from your data
[5]
or create reports really fast,
[8]
you're going to need Pivot Tables.
[10]
Let's say you receive this data set,
[12]
you need to figure out
the total sales by product
[15]
and get them in order
[16]
so you can see which products
generate the most sales.
[20]
You also want to figure out
[21]
which customer accounts
for the highest percentage
[24]
of total sales.
[25]
We can get this done faster
[27]
than it takes to make a cup of coffee.
[29]
Let's get started.
[30]
(upbeat music)
[35]
In this example,
[36]
we have sales and quantity data
[39]
by product, customer and company.
[42]
Now, the first thing you need to do
[43]
before you insert a Pivot Table
[45]
is to make sure that your data
[47]
is organized in a proper format.
[49]
This means it should be
in a tabular data format
[52]
like you see here.
[53]
Each column has a header.
[55]
You shouldn't have empty
columns in the middle
[58]
and you also shouldn't have empty rows
[60]
in the middle of your data set.
[61]
And I do think you shouldn't have
[63]
all total values
[64]
in the middle of this data set.
[66]
Once you can check all these boxes,
[68]
you're ready to insert your Pivot Table.
[71]
To do that, you can go to Insert,
[73]
and select PivotTable from here.
[75]
But check this out
[77]
for the Range is hard coding it
[79]
to SAS3 SKS108.
[83]
This is until where I have data.
[85]
If I add more lines to this,
[87]
they will not be considered
in my end report,
[91]
I will have to update my range
[93]
and I'd rather avoid this step.
[95]
So what I need to do
[97]
before I insert a Pivot Table
[100]
is to turn my data set into
an official Excel table.
[104]
This has the added advantage
[106]
that whenever I get new data added,
[108]
my Pivot Tables will
automatically reflect that
[112]
the moment I press Refresh,.
[114]
And all I have to do
[115]
is click on this button here
[117]
that's called Table
[118]
or use the shortcut key Ctrl+T.
[121]
Excel figures out that
this table has headers,
[124]
you can go with OK.
[125]
And if there's automatic table layer
[127]
that Excel gives you bothers
you like it bothers me,
[131]
you can go and remove it,
[133]
just select None.
[134]
But what I also prefer to do
[136]
is to give my table a name
[138]
so that I can recognize it later on.
[141]
You can do it from this
Table Name box here.
[144]
Now that we have a table,
[146]
we can automatically
summarize with a Pivot Table,
[149]
or go back to Insert
and click on PivotTable.
[152]
Check what happens now,
[154]
we get the name of our table.
[156]
As our table expands with new data
[158]
they will automatically be
included in our Pivot Table.
[162]
But before I do this,
[163]
I want to show you even an easier way
[166]
of inserting a Pivot Table,
[168]
and that's to click on
Recommended PivotTables.
[171]
These are actually quite smart,
[173]
they might just give you
exactly what you're looking for,
[176]
or they might help you get a faster start.
[179]
So for example, here I
have the sum of sales USD
[182]
by customer name,
[183]
one of the reports I want to
create has sales USD in it.
[187]
So I'm going to go with that,
[189]
just select it and click on OK,
[191]
you get your Pivot Table
inserted on a new sheet.
[195]
This is the result of my table,
[197]
it does all the aggregations for me,
[200]
I didn't have to write a
single formula to do this.
[204]
Now on the side here we
get the Pivot Table fields.
[207]
But you can see
[208]
I have them organized where
the fields are on one side,
[211]
and the boxes that define
the layout of my Pivot Table
[214]
on the other side.
[215]
That's an option you can select here.
[217]
The default is the first one,
[219]
Fields Section that Stacked.
[221]
Now, if you want to have more
space for your fields here,
[225]
you can go and select Fields
Section and Area Section,
[228]
side by side.
[230]
Now, what you see here are all
the headers of your dataset.
[234]
That's why each column
needs to have a header.
[238]
What Excel went ahead and did,
[240]
when you inserted your Pivot Table,
[242]
is that it put the sales
USD in the value section,
[246]
and customer name in the row section.
[248]
Now, if for some reason,
[250]
you don't want to see
the sum of sales USD,
[252]
but instead you'd rather
see something else,
[255]
for example, the average,
[257]
you can click on this drop down,
[259]
go to Value Field Settings
[261]
and change your selection from here.
[263]
So I go with Average and OK.
[266]
Now, you can also change that from here,
[269]
right mouse click is your
best friend in a Pivot Table.
[272]
If I right mouse click,
[273]
I can also select Value
Field Settings from here,
[277]
I'm going to put this to Sum
[278]
and go with OK.
[280]
Now, another thing you can get
to with a right mouse click
[283]
is the Number Format.
[284]
So let's say I want to add 1000 separator
[287]
to my numbers here,
[288]
I can select Number Format.
[289]
So don't select Format Cells
[292]
because that just formats
the underlying cell,
[294]
Number Formats stays with your
Pivot Table as it expands.
[298]
So I'm going to go with Number,
[299]
use 1000 Separator and
zero decimal places.
[304]
Now, you can adjust and
update this Pivot Table
[306]
as you see fit.
[308]
So for example,
[309]
if I wanted to look at
the different regions,
[311]
I could decide to add
the region in the filter.
[314]
And then when I click on this drop down,
[316]
I can select a specific region.
[319]
Or I can activate multi select,
[321]
which in this case
doesn't really make sense,
[323]
because I just have two items,
[325]
and you can also leave it on All.
[328]
Now, if you decide that you
don't want to have Region
[330]
in the filter,
[332]
you can bring it to the column section.
[334]
And now we have customer
name by region, here.
[338]
We also automatically get the
grand totals on this side.
[342]
Now, if you have another level,
[345]
so let's say we're going to
bring product description
[348]
to the rows here,
[350]
we also get subtotals.
[353]
Now, here's where you might want to update
[355]
the design of your Pivot Table.
[358]
So for example,
[359]
under Grand Totals, you
might decide to deactivate it
[363]
for either to rows or the columns
[365]
or for everything if you
don't want to see it at all.
[368]
So if I go with Off,
[369]
it completely removes my grand totals.
[372]
If I just activate it for Columns Only,
[376]
I get the grand total on the bottom here.
[379]
You might also want to do
the same thing for subtotals.
[382]
If I select Do Not Show Subtotals,
[384]
it's going to remove them from here.
[387]
Now, as for the layout of your report,
[390]
the layout that I personally prefer
[392]
is to show in a tabular form.
[395]
This gives me column headers
for each of the fields here.
[399]
Now, another thing you might want to do
[401]
is to fill in the gaps here.
[403]
Under report layout,
[404]
you could select Repeat All Item Labels,
[408]
and that repeats everything
and fills in the gaps.
[412]
Now I'm just going to remove that.
[415]
And now let's take a
look at the button here.
[417]
So you have the ability to collapse fields
[420]
or expand fields.
[421]
If you want to take away this ability,
[424]
you can go to PivotTable Analyze,
[426]
and under Show
[428]
click on this toggle here,
[429]
and it takes away the Buttons,
[431]
if you click again, it brings them back.
[434]
Now, the field list, this one here,
[436]
if for some reason you close that,
[439]
and you want to get it back,
[440]
you can do that by going back to Show
[443]
and selecting the fields list from here.
[446]
Now, I'm just going to put
everything back to the way I had it.
[449]
So I don't want region.
[450]
Whenever you don't want something,
[452]
you can just kick it out,
[453]
just drag and drop it here
somewhere in the fields list.
[457]
I also don't want the product description.
[459]
So I just want customer name by sales USD.
[463]
One other thing you might want to do
[464]
is to update the header here.
[466]
So I don't want anyone
to see sum of sales USD,
[470]
let's say I just want
them to see sales USD.
[473]
When I press Enter
[474]
Excel doesn't like it
[476]
if a header here is identical
to a field name here.
[480]
So if you want it to be identical,
[483]
what you need to do is either
add a space after the name
[486]
or before the name,
[488]
or you can completely change the name.
[490]
So we could just call this Sales
[492]
instead of Sales USD.
[494]
Also for Grand Total,
[496]
I can just call this Total.
[499]
Okay, so let's also update
the design of this Pivot Table
[503]
by selecting a different style.
[505]
So you have a lot of options here
[507]
and you even have the ability
[509]
to create your own Pivot Table style,
[512]
if you'd like.
[514]
Now, one thing I'd like to do here
[515]
is to sort this,
[517]
so just right mouse click,
Sort, Largest to Smallest.
[521]
Now, notice that the Pivot
Table columns collapse,
[524]
there is an option for this,
[526]
you can actually remove Autofit
[528]
if you don't want this to happen.
[530]
So right mouse click,
[531]
go to Pivot Table options,
[534]
on the bottom here you have
Autofit column with an update,
[537]
let's take away that checkmark
[539]
and go with OK.
[541]
Now, one thing I originally wanted
[543]
was to get the sales
percentage by customer
[546]
as compared with the total.
[548]
And let's say I also want to see
[551]
the complete sales values here.
[553]
Well, what I can do
[555]
is to bring in the Sales USD a second time
[558]
into my Values field.
[559]
But now check this out,
[560]
I'm going to right mouse click,
[562]
Show Values As
[564]
and select % of Grand Total.
[567]
But check out all the other
options that you have.
[569]
If you get a chance,
[571]
go ahead and try these
[573]
because they can come in handy
when you're creating reports.
[577]
Now, I'm going to call this Sales %
[580]
Okay, so our first report is done.
[583]
Before we test whether
this updates properly
[586]
if we add new data to our table,
[588]
let's go ahead and create
our second Pivot Table.
[591]
The way I personally prefer to do that
[593]
is to copy an existing Pivot Table,
[596]
go to the side and paste it in
[598]
and then adjust what I need.
[600]
This gives me a head start
[602]
because the Pivot Table
formatting comes with.
[605]
So in this second report,
[606]
I don't need the sales percentage,
[609]
what I want is the product by sales
[613]
and I want it sorted.
[614]
So let's right mouse click,
[615]
Sort, Largest to Smallest.
[619]
Okay, so my Pivot Tables
are actually done.
[622]
But I just want to show you
one thing before we wrap up.
[625]
And that's how easy it
is to add Slicer buttons
[629]
to your Pivot Table.
[631]
So for example, let's say for region,
[633]
instead of having it in the filter here
[635]
and selecting from drop down,
[637]
what we could do is add it as a slicer.
[640]
So while it's in the field list,
[642]
you can right mouse
click and Add a Slicer.
[646]
And it adds these buttons here
[648]
that you can organize anywhere you want.
[651]
And when you click on it,
[652]
it filters your report
[654]
to show the values in your slicer.
[656]
Now, for your slicer
[658]
you also get slicer options here
[660]
so you can update the design for this,
[663]
you can also update how you want it shown
[666]
by adjusting the number of columns,
[668]
the height and the width of your slicer.
[670]
Now, if you insert a slicer,
[673]
and you want to connect
it to another Pivot Table,
[676]
you just have to go to the Pivot Table
[677]
that doesn't have that slicer,
[679]
go to PivotTable Analyze,
[681]
under Filter Connections
[683]
place a checkmark for the slicer.
[686]
This is our region slicer,
[687]
so when I click on OK,
[689]
this one is also connected
[692]
to my new slicer.
[694]
In case you want to select all regions,
[696]
you can activate multi select here,
[698]
or you just hold down Control
[700]
while you make your selection.
[702]
Okay, so as a last step,
[703]
we're just going to make sure that
[705]
everything updates automatically
[707]
the moment we get new data in here.
[710]
So I'm just going to expand this,
[712]
let's add a new product,
[715]
let's also add a new customer.
[717]
Now let's go back to
our Pivot Table report
[720]
and all we have to do is pick
any of these Pivot Tables,
[723]
right mouse click and Refresh.
[726]
Because they have the same pivot cache,
[729]
they're all going to update
automatically together.
[733]
We can see the new customer added here
[735]
and our new product added in the middle,
[738]
because we have our sales data
sorted in descending order.
[742]
Okay, so this is how easy it is
[744]
to create Pivot Tables in Excel.
[747]
That was a quick
introduction to Pivot Tables,
[750]
but with all the explaining,
[751]
I was a bit slower than it
takes to make a cup of coffee.
[754]
But I was probably just in time for you
[757]
to enjoy coffee.
[759]
Before I sign off,
[760]
let's summarize the
benefits of Pivot Tables.
[763]
Pivot Tables help you get
answers from your data,
[766]
without you having to
write complex formulas.
[769]
It's very fast
[771]
to create a Pivot Table
[773]
and it's really easy to use one.
[775]
It helps you find relationships
[777]
between your data
[779]
and you can also visualize
these have with a pivot chart.
[782]
But that's a topic for another video.
[784]
Thank you for tuning in.
[786]
Make sure you subscribe,
[787]
hit that notification bell,
[789]
and I'm going to see
you in the next video.
[791]
(upbeat music)
Most Recent Videos:
You can go back to the homepage right here: Homepage