10 pivot table problems and easy fixes

hey Dave here from XL jet so in these last couple of videos I've given you a short introduction to the power of pivot tables and the first video I talked about how fast they are and how you can using pivot tables build reports way faster than even an advanced user building the same reports with formulas and the second video we looked at how good pivot tables are at answering questions about data and how you can use pivot tables a little bit like a food processor to slice and dice data any way you want to answer questions and reveal key information but although pivot tables are easy to set up they're a little bit slippery and I get a lot of questions about pivot tables so in this video I want to take a minute and show you how to handle some common questions that come up when you're working with pivot tables let's go take a look okay so the first problem we're going to look at is that you've got a pivot table and some data and you add more data but when you refresh that data doesn't show up in your pivot table so for example here we've got a pivot table and it's based on this data here and I'm going to take this data over here and I'll add it at the bottom and come back to the pivot table and refresh and you can see we still have just 16 properties so it's not included and one way to solve that problem is to go to pivot table options and change data source and then I can just set the source to include all the new data and when I hit OK we'll get 26 properties so that's the new set of data I'm going to undo that undo undo undo and let's look at another way to do it so a better way to do it is to take this data and convert it to an Excel table so ctrl T will do that and when I click OK we'll get the table and now I'm going to take this same data and paste it below and you can see it picks up the formatting and when we come over to the pivot table and click refresh then we get 26 property so converting your data to a table first is the best way to solve this problem okay in this example we have a pivot table and the problem is that the pivot table contains blanks and this happens most commonly because the source data just doesn't contain an entry for that particular subtotal so in this case like in the top row alpert doesn't have any sales in September which is why it's blank and the most common way to handle this is to ask the pivot table to display a value usually 0 for blank entries and you can do that by just going to pivot table options and where it says for empty cells just add a 0 now this respects the settings for number formatting so if I change this field to accounting then you'll see that we get the accounting number format and where there were zeros we see the single - which is standard for the accounting number format okay here's some sales data we've looked at already and the problem in this case is that we can see that we have about $3,700 of orders for bacon chocolate but we want to know that data that makes up that number and we could start adding more information we could add customer or maybe City and so on but this is going to get really complicated fast so and undo that if you just want to know the data behind a subtotal any subtotal in a pivot table you just have to double click that subtotal and Excel will give you a full set of data you can hand that out to somebody else or use it any way you like okay so in this example we have a large set of sales data and if we want to break this down by product I've got a pivot table here that's easy to do just put in product as a row label and sales as a value and the problem in this case is that we don't like that name sum of sales Excel will automatically name your value fields and that names just not very intuitive so if you want you can just rename it to anything you want and if you want to use the same name as in your source data in this case sales Excel won't like that and will complain but to solve that problem just add one space at the end of that and you're good to go okay in this example we have some employees we've got 300 employees each in a department and we have a pivot table that shows a breakdown by Department so the problem in this case is we want to show both account but also with a percentage so the easiest way to do that is just to add the same field again and then change the second instance to show a percent of total in this case we'll use percent of grand total and now we have both account and a percent of total okay so here's a pivot table we looked at earlier and we have again 300 employees broken down into by Department so in this case the problem is we don't want the datasheet we we just don't want this data sheet to be in this worksheet maybe we want to save file size or we just don't want the clutter so because pivot tables have their own cache that stores a copy of the data I can just delete this worksheet and the pivot table still is completely operational and if for some reason you want to get back the full set of data you can just double click the grand total and the pivot table will give you a full set of data again okay in this example we have some sales numbers and we have a pivot table here that's grouped by quarter and month and if I copy this pivot table and paste it over here then the two pivot tables will share the same pivot cash and one consequence of that is that if I do certain operations on this pivot table for example if i ungroup this pivot table then that will affect the first pivot table because they share the same pivot cash someone undo that if you want to avoid that problem you can force the two pivottables to use a different cache and one way to do that is just to cut one of the tables and make a new worksheet paste it in there refresh it and then I'm going to copy it and come back over here paste it and now I can ungroup this and you notice that it doesn't affect the first table because now both pivot tables have their own pivot cache they both still use the same data but they each have their own pivot cache alright so in this example we have some sales data broken down by product and we're going to look at the problem of a field losing number formatting so if I format these numbers here with a shortcut and get rid of those decimals they look fine notice I forgot to do the grand total and if I change this pivot table now I break it down by say region or category notice that my number formatting is completely lost so a better way to apply number formatting I'm going to undo this back to where we started is to just set at the field setting level the number formatting there so here I'll just do currency and no decimal places and now no matter how I break this down we'll get the currency number format retained okay in this example we have some sales data and the problem is that we have a simple title at the top with a year and if we refresh this table we it changes our column width and even if I set this back if i refresh again then it changes it again or for example by sort same thing okay so to fix this problem what you want to do is go to pivot table options and just turn off the auto fit setting and now we can sort or fix this first refresh and the column widths are not affected in this example we've got some sales data and I can build a report easily that shows the quarterly breakdown the data in this case has already been grouped by quarter but you notice that we get these kind of strange labels at the top column labels and row labels they really don't make any sense they take up space and make the column wider and there's two ways you can handle this one of them is to go to pivot table options and they're called field headers just turn those things off completely the other way to handle them is to go to design and switch in this case we're on compact you can't see that but we're on the compact layout switch to outline or tabular so if we switch to outline then we'll get more meaningful field headers okay so I hope you found those tips on how to overcome some basic problems that you might run into and working with pivot tables useful and if you're still with me at this point I think you probably agree that pivot tables are a powerful and useful tool in fact if you're using data at all in Excel then I highly recommend that you learn pivot tables they'll save you a ton of time and aggravation and in a world that's now overflowing with data they'll give you a valuable skill that will pay you back again and again so the question is how can you take the next step how can you go from conceptually understanding pivot tables to using pivot tables comfortably with your own data and I think a good way to do that is to go through an accelerated step-by-step program that offers guided practice and we offer that program in a course called core pivot which consists of bite-sized videos organized in a logical sequence with a full set of practice worksheets and I'll tell you more about that course in the next video so you can decide if it's a good fit for you and also give you a chance to get the course at a special price if you're interested and in the meantime if you have any questions at all about pivot tables fire away in the comment area below and I'll do my best to get back to you as soon as possible I'll see you soon in the next video

Channel: Exceljet

Views: 459,640

Rating: 4.9052954 out of 5

Keywords: excel training, excel lesson, excel tutorial, excel tips, excel, Microsoft excel, Pivot tables, Pivot Table

Id: P_0_zuvGYyU

Length: 11min 1sec (661 seconds)

Published: Tue Apr 28 2015