Advanced Pivot Table Tricks ONLY Experts Know

in this video we'll go over advanced pivot table  tips and tricks and to make it more realistic   we'll be using this data set and pretend  we're working as a business analyst at nike   with our manager asking us a set of questions  and these are going to get progressively harder   as we move along so stick around until the end  first up looking at the data set which you can   download in the description below you can see  over here that we've got nike's retail partners   and so here's all their names alongside their  region the product that they're currently selling   and their financials over here to the side if you  want to see how long this data sets for just press   control down arrow you'll see it's got about  four thousand rows control up arrow to get back   up and our manager who's not very good on excel  is asking us to find out what our sales by region   look like for this we're simply going to go inside  the database and go to insert pivot table and   we're okay with this range hit ok there and that's  going to open up a new sheet for us so within this   sheet we said we wanted the region so we'll put  that as a row over here and our manager also said   that he wanted the total total sales so let's say  we put that over here to the side so now we've got   the sales by region let's go ahead and reformat  this a bit so right click number format and say we   put it in dollar signs just so it's a bit easier  to see with a few commas in there so accounting   let's say we go for zero decimal places and hit  ok so we show our manager these findings but he's   not entirely happy as he says the numbers are  very big and so they're hard to read and instead   he wants them as a proportion so a percentage  essentially out of the total so let's go ahead   and try that on the pivot table so we'll go back  to the pivot table fields go to total sales again   and we'll drag that one inside of values again  so you see that you have a total sales too   and so that's gonna be this one over here which  we want to change to a percentage so we'll go   to right click there go to show values as and from  here we're gonna go as a percentage of grand total   and now you can see that you've got all of  the different proportions if you drag all of   them across that should give you 100 as you  can see down over here and so now it's very   very easy to tell that the west seems to be  the highest seller followed by the northeast   so we show him these proportions as well but he's  still not entirely happy and we'd actually like to   see date ranges as of now we just have the total  so within that whole date range but instead he'd   like to be able to filter through different dates  like what happened in a specific quarter or in a   specific month now to do so luckily it's  got an easy fix and we're simply gonna go   under over here under pivot table fields and  under the invoice date which is gonna show us   all of the dates that we have we're just going to  right click inside it and go to add as timeline   and so over here you'll find this  timeline let me go ahead and reformat it using this timeline our manager is going to be  able to change all his date ranges so you can   go ahead and change them like so as you can see  or even better you can also go through this drop   down and so here it says by months you can filter  that by quarter so suppose we want only the first   two quarters and everything is going to change  dynamically with it as well and if you're finding   this pivot tables video slightly too complex for  you you can check out this other one somewhere up   here which goes over more of the basic functions  now going back to the data set and suppose our   manager wants to find out what are the top 10  states in terms of sales so for this we'll go   ahead and create another pivot table so we'll  go under insert again pivot table hit ok there   that should generate it for us and so we mentioned  we want the state so we'll put the state as the   row over here and we're going to put the the total  sales over as the values but the problem is now   that we now we have about 50 different states  here which is a bit too much for us as we really   only want the top 10 of those now for this you're  going to go under this drop down for row labels   and inside it under value filters you're gonna  be able to find the top 10 which is what we're   looking for if you click inside it you'll have a  ton of different options not just the top 10 but   the bottom maybe it's not 10 maybe it's the top  15 you want in our case we're fine with this so   we'll hit okay there that's exactly what we get if  we come to all of these we have exactly 10 in here   but the problem is that they're not in order right  now they're actually in in a random order so we're   just gonna have to sort these we can go ahead and  right click then let's say we go for sort we're   going to sort largest to smallest and let's also  change this to accounting format so we'll go right   click number format and then in here we're going  to go to accounting no decimal places and hit ok   and now you can see that new york and  california seem to be the top sellers for us   and if you didn't know some of these  tricks and you want to learn more   you can consider taking our 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 so aside  from the typical lessons on formatting formulas   charts and other theory 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 modelling to cleaning a data set   and presenting some visual insights we also have  two real-life excel interview tests to help you in   the recruitment process so if you're interested  in checking it out you can go to the link   in the description below all right back to the  video given the high inflation we're currently   experiencing suppose our manager wants to find  out how many units sold we have at certain price   ranges so at the 50 dollar mark what about at the  100 mark and is there some kind of improvement   we can make to analyze that we'll simply go  back to the data set we'll go to insert again   pivot table hit ok there and from here what  we're going to want to have is the price per unit   let's say we put that under the rows and we're  also going to put the units sold under the values   so that's actually the quantity here but you can  see that we have a ton of different labels really   way too many to be able to interpret this so we're  going to do here is we're going to group them into   categories so we'll just click on any of these  go to right click and we're going to go to group   now to make this a bit more realistic and probably  better for us to understand let's say we start at   0 and we're going to end it at something like 120  and we want the increments at something like every   every twenty dollars so that's going to be the  different groups hit okay there now you can see   that we've got a ton of different labels so from  0 to 20 from 20 to 40 etc so it's a lot easier for   us to read from here we can create some kind of  hrc we can go to pivot table analyze for that then   we're going to go to pivot chart hit ok there and  now you can see that it's a lot more clear that   currently under the 40 to 60 range is where the  majority of our sales are and because we're great   employees and we want to help out our manager it'd  also be useful to find out with different products   if this is going to change at all so suppose we're  selling women's apparel is that going to be any   different to men's apparel now to find out about  that let's go under the product which is going to   be under the pivot chart fields we'll right click  on it from here you're going to go to add a slicer   and so we're going to put this slicer for  it on the side now you can clearly see if   i select men's apparel this is kind of where the  trends at so it's primarily on the 40 to 60 range   whilst if i go to women's apparel then all of a  sudden that kind of changes quite a bit where the   60 to 80 range is a bit better so perhaps for nike  it'd be better to prioritize the 60 to 90 range   as it's reasonable to assume that they have better  margins there to work on the formatting of this   chart a bit one thing you can do is remove these  boxes which really we don't need right now just by   going to pivot chart analyze and deselecting that  fields button that's gonna remove them for you   same thing with the legend we don't really need  it and for the products you'll notice that you   can only select one now if you want to select  more than one you can click over here to this   tick sign and that's going to allow you to select  multiple similarly if you want to select all of   them just go to the unfilter over here that's  going to do it for you now let's say our regional   sales managers are all asking for a sales report  to see how they're performing now to do so we'll   go back to the data set at the start we'll go  to insert again pivot table and hit ok there   to make this useful for them let's say we put the  total sales as the values and we're also going   to put the operating profit so they can see what  that's like and then over on the rows we're going   to want the different dates so we're just going  to go to the invoice date and now we're gonna get   the month so let's remove the invoice date from  there and we're just gonna keep the month like so   but the problem here is that we still haven't  accounted for the different regions now to do so   we'll go under the region and we're gonna filter  this instead so up top over here where you can   see you're just just gonna go to a drop down and  you can select a specific region that you want   now from there here's gonna be the cool part  where you can duplicate this action for all the   different regions that we have so we'll go under  the options drop down under pivot table analyze   click on that and inside here you're going to go  to show report filter pages click on that once   we want that by region hit ok there and now it's  going to create all of these different tabs for   each region so if you go control page down you can  see that it's got the northeast the south south   east west etc and so that's basically the idea  here where you've been able to filter everything   all in once and so suppose you have maybe 50  different states that you need to send this to   you can do this all in one go for more on pivot  tables check out this link over here to create an   interactive budget dashboard using pivot tables or  this other link over here to learn about our excel   courses hit the like and that subscribe button if  you liked it and i'll catch you in the next one

Channel: Kenji Explains

Views: 366,584

Keywords: pivot tables, excel pivot tables, advanced pivot tables, pivot table, pivot table tricks, excel pivot table, pivot table slicer, pivot table timeline, pivot table groups, pivot table grouping, pivot table percentages, pivot table custom fields, pivot chart, pivot table analysis, create a pivot table dashboard, pivot table top 10, pivot table sorting, pivot table filtering, pivot table chart, advanced pivot table tricks, pivot table number formatting, report filter pages

Id: b-K1jUY3jDs

Length: 9min 39sec (579 seconds)

Published: Sun Jul 31 2022