hey do you have a large data set that you need to analyze on Excel well one of the best ways to
do that is using pivot tables so in this video we'll go over eight awesome pivot table tricks
for data analysis so let's get into it suppose we work as analysts for a Distribution Company
and our manager is asking us some questions about this data set as you can see over here it's got
some information on some of the retailers that we're selling to the region the products that
we're Distributing and the financials for them and you can download this exact same Excel file
in the video description if you want to follow along so first up our manager is asking us how
many states do we distribute to in this data set for this all we need to do is Click inside of the
data set and then all the way to the side under the home ribbon you'll find this analyze data
button so just click on that this basically uses artificial intelligence inside of excel
within here you're going to see a ton of different breakdown suggestions but in our case we just want
to ask it a question just saying number of states and hit enter there and it's going to find that
it has 50 distinct States similarly we could have to get something else like say sales in New York
and it's gonna find the sales breakdown for New York in this case at a million so
it's really quite a handy tool for some quick analysis next up in number two our manager is
asking us for a sales breakdown by region so for this we can insert a pivot table so we're just
gonna go over to insert and to the side you'll find pivot table so let's just click on that
we're okay with this default range and just hit on OK for a new worksheet here we simply need to take
the sales and put those over under values and we want to take the region and put those on the rows
so just drag and drop there as well and here's the data but our manager is saying that it's not very
easy to see maybe it would be good to get the proportions so for that we need to just select
the sales again and drag and drop it on the same place so we now have it duplicated and then here
we just want to go to right click then show values as and we want it as a percentage of grand total
now you can see that we can see the proportions and the West seems to be the highest
moving to number three and our manager would like to know what the most popular price range is for
us so for this let's first remove all of these we're just gonna drag and drop them out like so
and from here we want to get the price per unit let's go ahead and put that under the rows and
we want the unit sold under the values but you'll notice here that we have so many different units
sold that we should probably go ahead and group them luckily pivot tables have a feature for it
so we can just right click go over to group and here we can specify how we want to group it so
let's say we start at zero we ended let's say 120 and we get groups by 20 indifference so let's go
ahead and hit on ok now you can see it sorted that out from 0 to 120 at 20 increments for each group
continuing on to number four let's suppose that our manager is asking us to create some kind of
a visual for this table so we can go ahead and first click on that table and then go over to
pivot table analyze and to the side you'll find the pivot chart which is the default chart type
will go for this one and just hit on OK there now we have a dynamic chart meaning that if we go
ahead and say change the price per unit here let's suppose we go on this drop down let's say that we
don't want this uh lower groups so we don't want anything from zero to twenty dollars showing we
can just hit on OK you'll see that the chart will update and so will our data set and based on this
it seems like the 40 to 60 dollar range is our sweet spot and if you're liking this content and
you want to learn more you can consider checking out our range of courses which include Excel power
bi finance and valuation and more as well as a range of bundle packages and what makes our
courses different is that they're all applied to the real world so aside from teaching the theory
our lessons also offer case studies that simulate the type of work you might be assigned in your
day-to-day ranging from creating a financial model from scratch on Excel to creating a p
l dashboard on power bi all the way to making a professional pitch Tech presentation on PowerPoint
and if you get stuck along the way you can always ask us the instructors any questions
in the discussions Forum so if you're interested in checking it out head over to the link in the
description below alright back to the video next up in number five our manager is asking
if it's possible to filter by certain dates we can do that we're going over to pivot
table analyze once we have the table selected here and then head over to insert timeline
we'll go we're okay with the date there and just hit on ok now we have this Dynamic filter by date
and as you can see right now it's by year but we can change that to say by quarter you can change
it to by month day Etc so let's say we stick with years and you can see that we just have to Simply
drag it and it's going to dynamically update over here we're at 2021 you can see there is nothing at
0 to 20 what if we go over to 2022 it does update and now we have this new bucket moving to number
six and our manager is asking if we could add some kind of a filter by region as well there's a few
ways to do that one is simply by dragging region and putting it under filters which we'll actually
see later but another way to do this is going to pivot table analyze and hitting on insert slicer
from here we want to select the region because that's what we're interested in and then hit on OK
and you can see that it's going to create this type of the table to the side it simply means
that if we select one everything is going to filter into that for that region only same thing
with this one down below and it looks like in the midwest generally the prices are slightly lower
while in the Northeast they tend to be slightly higher if you want to select multiple regions
just need to click on the stick sign here and now we can select multiple like so and if you want to
select all just click on this other button over here to remove the filter number seven our manager
says that he would like to send these reports to each of the regional managers so over here is
the table we're working with which simply has the groups that we did before we've got the unit sold
here and we just have the proportions which we learned how to do earlier so first let's reformat
these numbers we can simply select on all of them so Ctrl shift down then right click we'll go to
number format under number here let's go ahead and use a separator and put zero decimals in there
and we'll just hit on OK then we can also change the headers here let's call this one quantity
and we can call this other one a percentage from here we first need to add the filter by region so
we'll select the regions and put it under filter and we can use this special trick that's going
to basically duplicate this for all of the regions that we have available so for that we just need to
go over to pivot table analyze under options over here click on that drop down and go to show report
filter pages so we'll just click on that and we want to do this by region so we'll just go on OK
and you'll notice that it's going to create in this case five different sheets for each
of the regions that we have and each one has been filtered and renamed as well so we've got Midwest
Northeast and so on now we're ready to send this over to each of our regional managers finally in
number eight our manager wants us to find out the top five states by region so for that we simply
need to select on the states there put put it on the rows and we want to go over to the sales
and put that under values but instead of having a table with 50 states here we want to go ahead
and just sort it by the top five so we can do that by selecting on any of these states here go over
to right click then go over to filter and you'll find the top 10 this is the one that we want to
click on and instead of it being a top 10 we can do a top five we can also change this to a bottom
if we wanted to but we're okay like this for now we'll hit on okay there and there you have the top
five but you'll notice that they're actually not in uh enlarges the smallest order so we can change
that by right clicking and going to sort and sort largest to smallest now that's more like it for
more on Excel check out the newest Excel formulas in this video over here or go to this link over
here to take our Excel course hit the like and not subscribe and I'll catch you in the next one