in this video we'll go over everything you need to know about pivot tables in 10 minutes first we'll
cover the basics of analyzing data using pivot tables then we'll move on to pivot table visuals
like slicers timelines and charts and finally we'll get into advanced pivot table features like
grouping calculated fields and more so let's get into it and thank you to HubSpot for sponsoring
this video more on them later here's the Excel file we're working with which you can download
for free in the video description so as you can see we're selling Apple products and some of the
things based on this data that we would like to know is what is our total revenue what products
are selling best and a bunch of other things that would help us make good business decisions to do
this analysis first we'll turn it into a regular table just by hitting Ctrl T and hit OK there as
that's typically best practice from here we're gonna turn it into a pivot table so we're gonna
go under the insert tab up over here and then just click on pivot table then this pop-up should show
up it basically says to put in a new worksheet we're okay with that and we'll hit OK from here
you're gonna find all of the pivot table fields on one side so all of this area is basically all
of the column headers that we had before so all of these are going to be pasted over here and
then below that is where we'll be able to do our analysis if you want different viewpoints
you can click on this Cog here and change it to something like a vertical view like this if you
wanted or just go back to what it was by going back here from the Cog similarly you can drag
this across wherever you want for now I'm just gonna leave it here to the side so for example
if we wanted to find the revenue for each Apple product we would just go ahead and select the
revenues that would be our values meaning that's what we're summing as you can see the table is
going to start to Auto populate so that's just our total revenue but we want to break that down
by product so we'll put that under the rows as you can see that's gonna start showing over here
let's suppose that we want to change the format of this because it's kind of hard to read this
many numbers we can just go to right click under number format here and then we're just going to
change this to say a number and we're going to use the comma separator just so it's easier for
us to read and remove the decimals and hit ok now it's very easy to tell that it's 36 million
similar to the rows we've got the columns like for example we could add the region as a second
variable you can see what that looks like over here if you ever want to remove one it's just as
simple as dragging it out like so now what if we want this table to be ordered from highest
to lowest well for this it's just as simple as right clicking and from here going to sort and
we'll sort from largest to smallest just so it's a bit easier for us to interpret similarly what
if we want the proportions so the percentage of the total we can also right click for that and go
to show values as and here you're going to have a ton of different options for us it's a percentage
of grand total maybe this works well for a pie chart for instance what if we also want to find
out what products are selling at the highest price so basically the average sale price per product
for this We're Not Gonna want the revenues anymore so we'll remove that from the values here and
instead what we're going to add is the price per unit under values but as you can see we're getting
the sum which isn't very useful instead we want the average so we can just right click there and
from here go to summarize values by instead of the sum you can see we've got other options we'll
go through the average so now we can easily tell that the MacBooks seem to have the highest average
price now moving on to Part 2 which is visuals and here let's suppose that we want to see the product
sales by date now for this we can just go ahead back to pivot table and we're going to remove the
average so that's not what we want here and their values and instead we're going to put the revenues
and on top of that we also want to put the dates like we said so we're just going to put that under
the columns this is one way to do it but as you can see it's just not very easy to interpret or
to read so we're just going to remove that remove this date again as well one thing we can do here
is put it under filters so if you go to the date we'll drag it over to the filters and now this
is going to allow you to filter specifically by a date like let's say I select the first
of January and hit OK that's one way to do it but it's still not ideal in my opinion so
we'll get out of that like so and instead what we're gonna do is go under pivot table analyze
we're gonna insert a timeline we're gonna select it for dates and hit ok now let me just rearrange
that basically this is going to allow us to select specific time periods that we want like a specific
month or similarly instead of by month we can change this into by quarter we can select what
quarter we would like as well and on the topic of filtering another great tool for this is using a
slicer so for example let's say we want to add another variable which is going to be the region
now for this we could add it in here as a column for example or instead we can go ahead under pivot
table analyze and insert a slicer that's going to be for the region so we'll hit OK on that
and this is basically another way to filter This Time Around by region you can see how that works
when I select one everything is going to change I can select multiple with stick sign like so
and similarly I can select all of them just by hitting on this button here one final part of
pivot table visuals has to do with charts so first let's remove both of these by hitting delete we'll
hit delete on this one as well from here again to pivot table analyze I will hit on pivot chart
hit OK there as a default one and basically what's nice about this chart is that it's going
to be dynamic to whatever we add here for example let's say we also want the original breakdown we
can go ahead and put that under the legend you can see how that updates dynamically similarly
I can go ahead and filter like remove the apoc region and hit OK you can see how everything is
gonna dynamically change as well and speaking of data analysis a great way to visualize data fast
is using templates like the ones HubSpot the sponsor of this video is kindly providing us
using the link in the description below you can get multiple Excel chart templates completely
for free in the download you'll find an Excel file with the instructions on using the template
alongside all of the chart types you might need to visualize your data from here you can easily
modify the data and the charts will change these templates can have either one column of data or
multiple depending on your needs I personally find this template most useful for deciding what kind
of chart showcases my data best as it's very rare to have templates that have multiple chart options
showing live so if you want to check this out go to the link in the description below to get some
completely free templates from HubSpot to level up your Excel game alright back to the video
now moving on to step 3 which is Advanced pivot table features so over here you can
see that we have a clean pivot table and let's suppose that we want to find out the profit by
product that being said if you notice under our data we don't actually have a profit area
so there's no profit in in this list so we're gonna have to make one using calculated fields
for this we're just gonna go under pivot table to analyze then under the field items here we're
gonna click on calculated field and for the name we're just going to call it profit and the formula
here is simply going to be our Revenue minus our expenses so we'll double click on Revenue
minus and we'll double click on expenses and then we're just going to hit add and hit OK
now you can see that we've got the sum of profit let's suppose that we want a breakdown by my
product you can see what that looks like there if you ever have some doubts about a specific number
like maybe the airport profit here doesn't seem right to you you can always double click on it
and you're gonna see exactly where it's arriving the whole data from in a new sheet we'll go to
control page down to go back to the pivot table Tab and from here let's suppose that instead of
the product we want to find out by date so the profit by date we'll just drag that over here
like so but let's suppose that instead of by month like this we want to have it by quarter to do this
luckily we can use what's known as grouping so we'll right click here then we'll go over to group
and from here you can see that we have quite a lot of grouping options and more specifically
we said we wanted the quarters we don't really want the daily breakdown just quarters a month
is good so we'll hit OK there you can see exactly what that looks like once grouped
you can always expand or collapse the stable just by right clicking and going to expand or collapse
let's say we collapse it for now finally let's see what happens if you add new data to the original
sheet so we'll go to control page down here to get to the bottom just go to control down arrow
and so we're at the very bottom here let's just copy this whole this whole row so Ctrl shift right
Ctrl C and then Ctrl B down below let's say I change this to a new product that's been launched
so you call it ikanji hit enter there we just want to see if this is going to update over here
so let's let's go ahead and remove the dates and let's just put the products so we're gonna select
the products and just drag it across but you can see that the eye candy is not here the reason for
this is because we haven't refreshed the data so we'll go to pivot table analyze hit refresh
there and now you'll see that ikanji is currently showing so it's very important to refresh whenever
you have new data what we've learned so far is the conventional way to go about pivot tables but
there is one more tool that most people don't know about so we'll go to control page down to the data
sheet let's go back up by hitting Ctrl up Arrow and from here if you go all the way to the side
you'll notice there's under the Home tab this analyze data button so just go ahead and click
on that and it basically allows you to interpret the data using artificial intelligence so you can
see over here that it's discovered some insights like for example the expenses by region and sales
method and there's a ton of other information now if you don't find it very useful you can still ask
it questions much like a human like for example we can ask it to find the revenue for eye candy
hit enter there you can see that it's exactly found the revenue for product ikanji overall
it's a pretty powerful tool so feel free to play around with it for yourself for
more on pivot tables check out this video over here or this link over here to check
out our Excel course hit the like and that subscribe and I'll catch you in the next one