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