Learn the components of the Pivot Table. The video teaches how to breakdown the components until' you get to the root and teaches how to edit and debug problems using the Pivot Table in MS Excel.


More DIY videos at 5min.com

Video Transcription

The great thing about pivot tables is that it allows you to take a sea of data and create a very focused information report. I find that as soon as I am able to produce this report, people start to see anomalies. They start to say, “Well wait a second, we do not believe that cool juries is applied by anything from the north region, those records must be wrong.” A great feature that we have in pivot tables is that if you question any number in the pivot table, you can very easily drill down to see the records behind that number. All you need to do is double click the number. Someone is questioning the 30,695 in cell C9 for cool juries apply in the north, I will double click that field. You will notice that Excel has given me a brand new sheet. My data was originally on the pivot table sheet. My pivot tables on sheet one, I now have a brand new sheet called sheet two that shows me the records that make up that number. This drill capability works for any number in the pivot table. If I wanted to see all the records for a particular customer, I could click the total in column E. I know have a new sheet called sheet three that shows me all the records that make up that total. Now, if you have discovered that is wrong, you do not want to fix this on sheet two or sheet three. You want to go back to your original data set, the pivot table sheet and fix the records there. One very important thing that you need to understand about pivot tables, usually in Excel if we change any cell, all of the formulas and all the calculations instantly update. But, the reason that pivot tables are so fast is that Microsoft actually takes all the data on our worksheet and loads it into a special area in memory called pivot cache. When you change date in the worksheet, it is not changing data in the pivot cache, let me show you what I mean. I am going to randomly select a cell here and add a million dollars to this cell. I can hear all the auditors out there just cringing, relax. If I go to sheet one even though I have changed the original underlying data, my pivot table has not recalculated. Let me scroll down so we can see the total. We are still in $13.2 million. In order to refresh this data, I have to go to the options tab and then click the large refresh button. If you were using pivot tables in the old version of Excel, the refresh button was a red exclamation point on which makes no sense at all. I really appreciate that they have made it look like the refresh button in internet explorer. Kind of helps me understand that I need to refresh this table. When I click Refresh, Excel goes back to the pivot table worksheet, reloads all that data in the cache and regenerates the pivot table. The $13.2 million just changed to $14.2 million. We now have the latest and greatest data.