DON'T Make These 5 Pivot Table Mistakes

hi i think you're doing your pivot tables wrong in this video let me show you five ways in which you could be making your pivot tables incorrectly or not using the best practice let's go [Music] here i have got some employee data for our awesome chocolates company you can see that there are several columns and this data is all neatly arranged you might think what mistake can we make now that everything looks nice and clean let me show that so i'll select all my data using my keyboard or mouse and then go to insert pivot table and go through the familiar screen of inserting the pivot table click ok and we will end up in the pivot table worksheet let's build a very very simple worksheet of how many people we have by department so i'll add my department to the row area and my employee id number to the values area so we will get a count of employee numbers by department i'm just going to expand everything so we have 247 people and this is how they are structured so far nothing wrong but because this is employee data every month we hire some people and we let go of some people so we change the data for the sake of simplicity i'm just going to introduce some employees so i'll paste the data that i have already copied now even though we have added eight people to the data when i go to my pivot table even after refresh i still see 247. the problem here is we have used a wrong practice to build the pivot table when we created the pivot table you can see that from pivot table analyze change data source we have kind of told excel that our data will always be from c4 to k250 so that's why my pivot table is kind of stuck up to row number 251 here and it's not paying attention to this new data and that is the mistake number one so what is the correct way of doing this pivot let's just start from scratch the correct way to do this pivot is before you even generate the pivot table first make your data into a tabular format this is very simple just select any cell and then go to insert and then click on this nice big table button excel is going to identify all your data right now it says c4 to k251 but if you add some data or change it's all going to work nicely so we'll click ok and this will be called table 1. it's not necessary but a good practice is to give your table a name so i'm going to name my table as staff and now that this is a table i can directly use the summarize with pivot table button here to generate a pivot from this table you could also go to insert and pivot table they both will do the same thing you can now see that the table name is staff so it's not attached to a physical range of c4 to k251 it is attached to the table whatever the table contains the pivot will be generated from that we'll click ok we'll get the same familiar screen again let's build department and employee id number we will see the same 247 number here notice what happens the magic when we add extra employees to add the employees you just go all the way to the employee table and then very last row select the next blank row and then paste your data automatically the data gets pasted and the table gets extended so now our table is not at 251 it goes all the way up to 259 at this point to see the updated employee count you just right click refresh boom we get 255 here without having to change the data source of my pivot so that's the number one mistake that most people make when they make the pivot tables but we are not done here i've got four more mistakes that you might be making so let's take a quick look at them the second mistake that i see people make with pivot tables is just using the default options you can customize every little aspect of pivot table even though it may not seem so so for example here my employee account says count of employee id number that doesn't look so pretty but i know what to call this this is my head count or staff count you can just select that cell and then type staff count there and that would work likewise here it says row labels unfortunately pivot table won't allow you to delete that word row label from there if i try to delete i'll get a big nice warning here instead what you could also do is you can press a space there hit enter so that that thing goes away likewise if you want you can also add alternative rows in a different color for that you just select any cell within the pivot and then go to the design ribbon and enable banded rows so that every other row gets highlighted this way it becomes easy to read especially if you have got a big pivot table there are many other customization options possible within the pivot tables i talk more about these towards the end of the video but now let's talk about the third mistake that people make with pivot tables and this one is a biggie making a static pivot table like this here this is nice and good but what if i want to know how many people we have got by gender or by the employee type one way to do this would be you can take the employee type and put this into the filters area and that will add a filter on the top from here right now we are looking at all employees i can select and i can pick fixed term click ok so we can see that this is how many people we have at a fixed term staff level but this requires multiple clicks there is a better way to do this you can delete the filter and instead right click on the employee type and add as slicer this will introduce a floating box of filter choices for us and i can click on any one item to see that staff type making the entire pivot table interactive so let's see our permanent staff fix its staff or temporary staff you can also multi-select items by holding down the control key so i can see permanent and temporary or if you use the clear filter button here you can look at everybody so don't make static pivot tables make the interactive ones using the slicer option let's move on to the fourth mistake that i see people make which is making the pivot table from just one data set right now i have got my employee data here in the staff table let's say we have got some budgets allocated by each department on how much salary they are supposed to pay here is that data and we can see how much is the salary budget for each department let's say i want to see against the budget how much is the actual salary we are paying and do some sort of variance analysis now this sort of a thing we normally resolve to using formulas or something but you can actually combine these two tables to make one pivot table and then look at the data to do this first step is to take this data and turn it into a table you can select the data or select any one cell and press ctrl t to insert the table i'm gonna name this table as my departments now that we have got both tables we can go to either one table it doesn't matter which table it is and then say summarize with pivot table and at this point make sure that you are enabling add this data to the data model option this is a powerful option that lets you combine data from two different tables so you need to enable this option and when you click ok excel will now let you work with multiple tables so you can here see that it has now shown staff table but if i go to all i have got my departments and staff table both tables are there there is a line between these two tables because excel things these two are two separate tables but we know that they are linked on the department field so let's connect these two to connect them you can use from the pivot table analyze ribbon the relationships button so let's set up a relationship here i will make a new relationship between my staff table department column and my departments table department column it doesn't matter which order you pick these tables the relationship will still work and when you click ok and close this you now see that the line is gone so excel thinks these two are one happy family so at this point i can go to my department put the department on rows and then put the salary budget here so that i can see what my budgeted salary is and then go to staff take the salary and put it here so that i can see what is the actual salary let's analyze this you can see that certain departments have more salary than the budget that is allocated to them whereas some other departments might be underspending like human resources here 1.7 million and 1.61 million let's add some currency formatting to all of this so that we can make sense of this data you can right click on the numbers and then just choose number format and apply the currency formatting we'll need to do it for this column as well so that we can see what is my salary here as a budget and what is the actual value this brings us nicely to the fifth mistake that you see people making all the time and that is not using the advanced calculation power of pivot tables let's say i'm looking at these two and i want to know what the variance is and print the variance figure here so that we can do some variance analysis with regular pivot tables adding such a column becomes a little cumbersome but now that we have set up the data model we can use the beautiful power pivot feature right inside excel so you can right click on the staff table add a measure and this measure would be my salary variance and here we have got two values already available one is my sum of salary budget and another is sum of salary so if you open square bracket you will be able to access the measures that are already available sum of salary budget so this is my first one minus sum of salary that is my variance definition budget minus actual and i can apply currency formatting on this and click ok and you will see that this fx salary variance is now added to the pivot table field list and i can put that there and i can see my variance like i mentioned many departments have negative variance that means they have spent more than their budget but certain departments like human resources and training are underspending on their salary budgets so now that we have understood how to make pivot tables correctly you may want to learn a little bit more about pivot tables here is a list of my advanced pivot table tricks do check that out and learn some advanced tricks if you want to learn the entire spectrum of excel not just pivot tables for your data analysis job then i highly recommend checking out my excel school program this comprehensive course will cover entire spectrum of excel right from how to get the data to how to write formulas how to use pivot tables how to make interactive charts and dashboards and finally how to use excel productively do check out the course as well the link for that is on the screen as well as in the description below i'll catch you in one of these two places bye you

Channel: Chandoo

Views: 157,599

Keywords: chandoo, chandoo.org, Excel, spreadsheets, excel pivot table mistakes, pivot tables, pivot table errors, pivot table issues, pivot table interview questions, pivot tables for beginners, excel pivot table tutorial intermediate, advanced pivot table tricks, mistakes with pivot tables

Id: 6A3e3s9YBgo

Length: 12min 20sec (740 seconds)

Published: Tue May 17 2022