Pivot Tables in Excel 1 Pivot Table Student Averages On Bb see L2 Pivot Table Student Average xls L2 Pivot Table Tutorial doc Let s do the tutorial short 2 Pivot Table Student Averages Month Subject Student Score Elisa Elisa Elisa Elisa Elisa Elisa Elisa Elisa Elisa 87 65 58 89 81 62 51 72 89 January English January Maths January Science January Art January History January French February English February Maths February Science Data Pivot Table Student Elisa Art Sum of Score Subject Month January February March Grand Total 89 83 92 264 English French History 87 51 41 179 62 57 56 175 81 84 91 256 3 Pivot table Grocery Example We have 900 rows of data on grocery sales see in Bb Pivot Table Grocery Example You would like to see a breakdown of sales During each year Each product group Product at each store Your tool is pivot table click Insert PivotTable Pivot Table on Ribbon 4 Pivot table We must have headings in the first row of data for our Pivot Table to work properly Our data has headings in row 2 year month store etc If you click on Insert Pivot Table Pivot Table with your cursor inside your data somewhere Excel will make a guess at your data range often right 5 Pivot Table Field List Fields dragged here will be listed across the top of the Pivot Table in the order in which they are dropped Fields dragged here will be summarized mathematically in the table NOTE MUST be numbers Fields dragged here will be listed on the left of the Pivot Table in the order in which they are dropped 6 3rd dimension Pivot Table Compact Form 7 Pivot Table Outline Form 8 Pivot Table Tabular Form 9 Pivoting the whole point store pivoted to column from row 10 Pivot Again year pivoted to column within store 11 Expand Collapse Fields Years 2005 2006 collapsed 12 Sorting To sort a column or row Click on any cell in that column row Right click and choose sort or Select Sort Filter from Ribbon 13 Filtering Can also filter values Click on down arrow on heading Select Filter 14 Filtering Example Revenue Show top 25 of Products by Sum of 15 Result of New Filter Products for which Sum of Units is greater than 50 000 sold 16 Subset Filtering You can select certain subsets of the various fields Here we show results for only Jan March 17 Pivot Table Salesman Product See L2 Pivot Table SalesmanProduct on Bb Excel Pivot Tables Tutorial Month Salesman Region Product No Customers Jan 07 Joseph North FastCar Jan 07 Joseph North RapidZoo Jan 07 Joseph West SuperGlue Jan 07 Joseph West FastCar Jan 07 Joseph West RapidZoo Jan 07 Joseph Middle SuperGlue Jan 07 Joseph Middle FastCar Jan 07 Joseph Middle RapidZoo Net Sales 1 592 1 088 1 680 2 133 1 610 1 540 1 316 1 799 Profit Loss 563 397 753 923 579 570 428 709 8 8 8 9 7 7 10 10 18
View Full Document
Unlocking...