Pivot Tables: MUST-KNOW for Business & Finance Professionals

what's up everyone it's kenji here and today i 
thought i'd share how to analyze a large dataset   like this one using pivot tables and having them 
update dynamically using the slicers on the side   and you can download the file i'll be working 
with down in the description so let's get into   it let's suppose that we're working at apple and 
this is the data set that our manager has given us   over here as you can see if you go control down 
arrow you get to the bottom it's about 100 rows   long go control back up over here and it's 
got the breakdown by the region by the type   of product that they're selling the price for 
it the cost per unit the quantity the revenue   the expenses and lastly we've got the sales method 
so this is basically hey are the sales coming in   through apple's physical store maybe their online 
store there's also third parties which could be   things like a best buy or a walmart say amazon etc 
as well as referrals so this is people that get   special conditions like what could be employees 
product reviewers and other people like that and   our manager is asking for a series of questions 
to do with this data set firstly our manager   wants to find out the quantity sold in 2021 by 
region so for that we could go do something like   some mips over here and go one by one and try to 
make it like that maybe even answer the chart but   that would be quite a tedious process so instead 
we're gonna use pivot tables now for that we want   to go under the insert tab and over here you'll 
find the pivot table option just click on that one   and you can see that it selects a range and it's 
basically got our entire table with a data set   which is what we want and we want to put it in a 
new worksheet that's fine for us and then press ok   so over here on the right hand side you'll 
notice that there's the pivot table fields   so these are all the conditions that we can tweak 
and put them in different places so in our case   what we we're looking for is the quantity sold 
and so we want that as the summation as the values   that we want out of it so we'll put it down over 
here on top of that we've got the the region and   the column the rows and the columns sorry so over 
on the rows let's say we put something like the   different regions which he's asking for so let's 
put that over there and you can see that the table   starts to auto populate over here on top of that 
we also need the the breakdown by ear because he's   only looking for 20 21.

So for that let's go ahead 
and select the years and just drag that down over   on the under the columns now you can see the 
different types the quantity sold for 2021   down over here and where we can easily interpret 
that hey north america has the highest sales   um quantity at least next to next to that there is 
latam and so on and so forth so it's really that   simple let's say that we report these findings to 
our manager and now he wants to know the average   sale price by region and year so for that 
if we go back to the pivot table over here   we were fine with the regions being under the 
rows and the years on being under the columns   the only thing we want to change is 
instead of the sum of quantities sold   we can just drag that out and you'll notice there 
is an x sign that means i will take it out so   click there and that should be out of there 
and what we want to put inside there instead   is the price per unit so we want to find the 
average price so we'll go ahead and select that   but you'll notice that now it's the sum of 
price per unit which is not quite right so   we'll click over here under the drop down 
and you want to go under value field setting   and here we want to change that to the average 
and as you can see there's a bunch of different   things that you can change it to the minimums 
the products etc in our case we're fine with the   average so we'll hit ok and now let's go ahead and 
reformat this as it's not looking too good so ctrl   shift down arrow ctrl shift right arrow from there 
press ctrl 1 and now you want to go under under   the number tab here we don't really want decimals 
we don't need them and then use the separator   press ok and so now we can see that for 2021 it 
seems like the the highest average sale price has   to do with the apac region um followed by emea and 
so on and so forth so it's interesting to see here   that asia asia pacific actually has the highest 
sale price so they're probably buying more of   the iphones the imacs the more expensive stuff 
like that while the lowest region here is the   latam region and maybe they're only buying say eye 
watches and airpods other things of that nature   and because we're good employees and we want to 
go that extra mile we tell our manager that just   looking at the average sale price doesn't really 
tell us the whole picture we're not really looking   at things like profitability so he says ditch the 
three years and instead just do the revenues minus   the expenses and find out the profitability 
by region so let's go ahead and do that   now he said to this to three years so we're just 
going to take out the years column over here   and instead we're just going to have the regions 
and we don't want the average price per unit so   we'll remove that instead we want the revenues so 
just drag that in we also want to get the expenses   so now we've we've got the revenues and the 
expenses but we don't actually have the the   profitability and that's not actually a line on 
the pivot on the data set that we've got over here   so we're going to have to create it for that you 
can just go under the pivot table analyze tab here   then we're going to go to fields and we want 
to calculate a field so we're going to create   basically a new field that's going to be up in 
here and we want to call that something like   say profit and the formula is just going to be the 
the revenues that we've got here insert the field   and then put a minus sign and we want to also add 
the expenses and insert that field then press ok   now you can see that we've got the the profit over 
here which is just basically the revenues minus   the expenses as it should be and from this we can 
also see that it seems like the apac region has   the highest profit probably got to do with the 
fact that they also have the average sale price   so we send our manager these findings and 
he says to put it in some sort of a chart   because it doesn't really look all that good in 
this table so for that we can just go under the   pivot table analyze and you'll notice there is 
a pivot chart button here so let's click on that   from there we're fine with a cluster column press 
ok and now that's looking slightly more like it   one thing you'll notice is that we got all of 
these boxes which really don't look that good and   we don't really need them so we'll go under pivot 
table analyze again and click on this button here   under fields button and that's going to remove 
them for us so that's looking slightly cleaner   also you'll notice that on the legends we've 
got the sum of revenues some of the expenses   we don't really need the sum of so we can just 
have revenues expenses etc for that we're going   to have to go under pivot chart fields sorry under 
the values once we press the drop down we want to   go under value field settings and then here we 
just want to type revenues instead of the sum of   delete that and press enter but you'll notice 
it says this name already exists and so what   we want to do instead is just put a space right 
in front of the revenues press ok and now we can   do that same thing with the expenses value 
field settings and we'll just put expenses   and put a space in front press ok and lastly 
for the profit we'll do the exact same thing   value field settings profit press ok one really 
cool feature about this type of chart is that   it's fully dynamic so for example if we want to 
remove the profits and just have the revenues and   the expenses we can take that out and you can see 
that the chart automatically updates and if you're   enjoying this video on pivot tables we do have 
an excel for business and finance course where we   teach everything we know about excel specifically 
for people either looking to break into a business   or a finance role or those in it trying to level 
up their excel game unlike most theoretical   courses we try to make this one as practical as 
possible based on our real experiences working at   companies like tesla or amazon so aside from the 
typical lessons on formatting formulas and charts   we have case studies that much like this video 
replicate the type of work you might be assigned   in your day-to-day ranging from financial modeling 
to cleaning a data set and presenting some visual   insights so if you're interested in checking 
it out you can go to a link in the description   below now let's move on to a more advanced pivot 
table like the one we saw at the beginning of the   video and let's say that our manager wants to 
find out the quantity breakdown depending on   the sales method by product and depending 
on the year as well so basically he wants   to see if hey if you buy the product in store it 
doesn't make a difference to the type of product   that you might buy same thing with online 
maybe you feel more trustworthy when you go   in store as opposed to buying it online so all 
of these trends you want to try find out so for   that let's go ahead and create a pivot table 
again so go under the insert pivot table okay   and what we want to put here is the quantity sold 
that's going to be the summation over here then   we've got all of the different the sales methods 
that's going to be under the rows for us and we   also want to put a subcategory in there which 
is going to be the the product so we're going   to have the breakdown like you can see here for 
the in-store depending on the types of products   that there are and this is a drop down so you can 
close it and open it depending on what you want   we also want to put the uh the years under the 
column over here so let's go ahead and select that   and drag it over now that's looking slightly 
more like it also let's add another column   up here so we'll do control control space and 
then control plus ctrl shift plus sorry for the   decimals we don't really need them so we're gonna 
select everything ctrl shift down arrow and then   ctrl shift right arrow and go to alt h9 based 
on this data over here we can see that third   party sales are the ones that are um selling the 
most and followed by that we've got in store in   particular it seems to be the airpods that are the 
best-selling products now from there we show our   manager this and he says that he really likes it 
so he wants to send it to the management team but   you know that the management team isn't all that 
professional with pivot tables and so we want to   create some sort of a slicer which is going to be 
a drop down for them to be able to move things and   change things around a bit more easily than using 
the pivot table fields which they're probably not   familiar with so for that go under the pivot 
table analyze and we want to go under insert   slicer and here's the different slicers that we 
can insert which are basically all of the fields   now let's put one for a region so they can select 
the different regions for here as well as that's   probably something interesting for them to know 
and we can also create a third one by sales method   hit the ok key now we've got these three different 
slicers let's say we put them over here move   them around a bit let me just reformat them and 
fast forward this so you don't have to watch it nice so now we've got three different slicers 
over here so let's say we only want 2019   you'll see that the table updates dynamically 
2021 same thing over here and you'll notice   that you can actually select two anymore for 
that you're going to have to click over here   and that's going to allow us to multi-select and 
then this last thing over here that you've got on   the side is to remove the filter if you click 
on that it's just going to select everything   so if we put email here and then we click back on 
this it's just going to select everything for us   now it's a lot easier for people to interpret this 
one thing though they might not really know what a   slicer is and maybe they haven't used one before 
so for that we can just go under the slicer tab   here and we're going to change the the slicer 
caption from here to something like select here   here below so that's a bit more explanatory for 
them and they probably get a better idea of it   let's not work on the design of this as it's 
not looking too good to send so firstly let's   remove the grid lines alt w vg is going to be the 
shortcut for that also we want to make this a bit   bigger so you can see that the columns are a bit 
too tight so let's select them by pressing the   control space and then shift right arrow to select 
the the last four here and what you want to press   is the alt how and from there for the column width 
let's put something like 12 press enter now that's   looking a bit more spaced out which is probably 
nicer if you want to you can change the design   over here where you've got the different color 
ranges and so on you can also do it more manually   so for instance we can select this top area over 
here and from there just go to alt h and we could   select say a dark blue over here and change the 
colors of the actual letters into white so alt hfc   and we'll pick white press enter now that's 
looking a lot more presentable to send to   the management team if you want to check out 
our excel course and go to this link over here   if you want to learn how to become faster on 
excel using formulas check out this link over here   or if you want to learn some cool shortcuts 
check out this other link over here that's all   for this one hit that like hit that subscribe 
if you liked it i'll catch you in the next one

test attribution text

Add Comment