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

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 CashNews.co on pivot tables we do have an excel for business and

href="https://cashnews.co/finance" style="font-weight: bold; color: #1a73e8; text-decoration: none;">Finance course where we teach everything we know about excel specifically for people either looking to break into a business or a

style="font-weight: bold; color: #1a73e8; text-decoration: none;">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 CashNews.co 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 CashNews.co 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