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