Master Pivot Tables in 10 Minutes (Using Real Examples)

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

Channel: Kenji Explains

Views: 302,670

Keywords: pivot tables, learn pivot tables, master pivot tables, excel pivot tables, pivot tables excel, calculated fields excel, pivot table slicers, pivot table timeline, pivot chart, pivot table analysis, pivot chart analyse, grouping pivot tables, summarise values pivot tables, learn pivot tables in 10 minutes, analyse data using pivot tables, create a pivot table, pivot tables for beginners, pivot table tricks, learn pivot table analysis, pivot table tutorial

Id: MnPVfxOTvvk

Length: 11min 32sec (692 seconds)

Published: Sun Jan 29 2023