Pivot Tables: MUST-KNOW for Business & Finance Professionals - YouTube

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