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
