Download File: http://people.highline.edu/mgirvin/excelisfun.htm Learn all about PivotTables from A to Z, including the New Excel 2013 Power Query and the ability to access build relationships and reduce file size with the Columnar Database (Data Model). Topics in this video: (00:15) How to Download file (00:37) Overview of topics in the video (01:19) Essence of what a PivotTable does: Calculations with Criteria/Conditions (03:11) First Example of building PivotTables with just a few clicks (04:55) What a proper Data Set is and why it is required for PivotTables (07:18) Visualize your Report FIRST, before you build your PivotTable (09:15) Basics of PivotTables, including Report Layout to show Field Names, Number Formatting to display numbers appropriately, and how to change the aggregate calculation (Change function from SUM to AVERAGE) using the Show Values As feature. (13:36) Keyboard to create a PivotTable (15:04) Create a cross tabulated report. See how to “Pivot” your PivotTable. (18:23) Use Slicers to Filter the Report (21:55) Human Resource Report example to count how many employees in each department and how many total hours worked for the year in each department. See how to make calculations on Text Fields. See how Text Fields default to the COUNT calculation. (26:02) Create Yearly Sales Report to show annual amount, Change for each year and percentage change for each year. (27:02) Group the Date Field with daily dates into Year and Month using PivotTable Grouping feature. (29:45) Show Values As PivotTable feature to show “Difference From” and “% Difference From”. (32:35) Create 15 Reports from a single PivotTable using the “Show Report Filter Pages “ feature: Analyze Ribbon Tab, PivotTable group, Options drop-down arrow, Show Report Filter Pages. (34:28) Create a Histogram Chart using a PivotTable and the Grouping feature to group Numbers (Decimals). (42:05) Create PivotTable from Two Tables using the Data Model in Excel 2013 (Columnar Database). This feature allows you to connect two tables with a Relationship and avoid using VLOOKUP in a helper column. (48:18) Power Query to Unpivot a data set that is not a Proper Data Set. We need a proper Data Set to create a report to show the error rates for each year. Power Query is an amazing new Add-in feature that helps us to transform bad data into a proper data set that we can use in a PivotTable. (52:15) Show Values As PivotTable feature to make the calculation to “% of Column Totals”. This will allow us to create a yearly report to look at Error Rates for each year. (53:48) Add new data to Excel Table and see that Power Query and the resultant PivotTable update perfectly! (55:20) Power Query to Clean Data using the Split Column fPower Query feature in order to create a Regional Report. (58:52) Use Power Query and Excel 2013 Data Model feature to import 20 Megabytes of data and see that this process will compress the data set down to 2 Megabytes. We will then use the PivotTable dialog box to access the Data Model data set.

• published: 31 Aug 2015
• views: 136