Excel Statistical Analysis 04: PivotTable & Power Query to Build Frequency Distributions
ESA #4: PivotTable Grouping to Build Frequency Distributions & Power Query to Import CSV Data
Download Excel File: https://excelisfun.net/files/Ch01-ESA.xlsm
Download CSV File: https://excelisfun.net/files/Ch01-ESA-AutoData.csv
Learn about how to use Power Query to import and transform CSV File data and use the PivotTable to create Frequency Distributions for Categorical and Quantitative Data.
Topics:
1. (00:00) Introduction to topics in video
2. (00:53) Tabular Reporting goals for this video: 4 Different Frequency Distributions
3. (01:31) PivotTable to create Frequency Distribution from Nominal Data that is located in the worksheet
4. (02:52) What is the PivotTable Cache?
5. (03:19) Introduction to PivotTable User Interface
6. (05:07) Change calculation in PivotTable with: Summarize Values By and Shows Values As
7. (06:07) Alt Keyboard Efficiency Tricks to create PivotTable and other features
8. (09:48) Power Query to Import & Transform CSV file with table of data
9. (11:30) What is a CSV File? What is a Delimiter?
10. (12:38) Where is the Power Query tool in Excel?
11. (14:30) What are Data Types in Power Query? And why are they Important!
12. (15:40) Merge two fields to create one element field
13. (16:44) Load Power Query Table to Excel Worksheet
14. (17:57) Edit Query after it has been loaded
15. (19:30) Terms to describe the table, fields and data.
16. (20:07) Frequency Distributions for different types of data
17. (20:34) Frequency Distributions defined
18. (20:47) Frequency Distribution for Nominal Data, using a PivotTable.
19. (21:23) Frequency Distribution for Discrete Quantitative Data, using a PivotTable and the Grouping Feature
20. (22:47) How to Group in a PivotTable and create Upper and Lower Limits for Counting Categories.
21. (23:49) Use Field Settings to show categories without data.
22. (25:05) Change Grouping Categories
23. (25:36) Grouping Categories Created for Discrete Quantitative Data
24. (26:08) Frequency Distribution for Continuous Quantitative Data, using a PivotTable and the Grouping Feature
25. (27:11) Grouping Categories Created for Continuous Quantitative Data
26. (28:14) What happens to PivotTable when Source Data Changes?
27. (28:32) Refreshing a PivotTable to allow new data into Report
28. (29:04) Fixing Ambiguous Grouping Categories in a PivotTable with Decimal Number Data. Three Methods.
29. (31:30) How Grouping works with PivotTable Cache.
30. (32:33) How to create Multiple Grouping Categories from a Single Data Source using PivotTable Wizard QAT Button or Keyboard: Alt, D, P.
31. (33:53) Summary of video
32. (34:40) How to Printout Notes from Excel Workbook File
33. (35:13) Homework files to download from in Canvas
34. (36:12) Closing and Video Links
Видео Excel Statistical Analysis 04: PivotTable & Power Query to Build Frequency Distributions канала ExcelIsFun
Download Excel File: https://excelisfun.net/files/Ch01-ESA.xlsm
Download CSV File: https://excelisfun.net/files/Ch01-ESA-AutoData.csv
Learn about how to use Power Query to import and transform CSV File data and use the PivotTable to create Frequency Distributions for Categorical and Quantitative Data.
Topics:
1. (00:00) Introduction to topics in video
2. (00:53) Tabular Reporting goals for this video: 4 Different Frequency Distributions
3. (01:31) PivotTable to create Frequency Distribution from Nominal Data that is located in the worksheet
4. (02:52) What is the PivotTable Cache?
5. (03:19) Introduction to PivotTable User Interface
6. (05:07) Change calculation in PivotTable with: Summarize Values By and Shows Values As
7. (06:07) Alt Keyboard Efficiency Tricks to create PivotTable and other features
8. (09:48) Power Query to Import & Transform CSV file with table of data
9. (11:30) What is a CSV File? What is a Delimiter?
10. (12:38) Where is the Power Query tool in Excel?
11. (14:30) What are Data Types in Power Query? And why are they Important!
12. (15:40) Merge two fields to create one element field
13. (16:44) Load Power Query Table to Excel Worksheet
14. (17:57) Edit Query after it has been loaded
15. (19:30) Terms to describe the table, fields and data.
16. (20:07) Frequency Distributions for different types of data
17. (20:34) Frequency Distributions defined
18. (20:47) Frequency Distribution for Nominal Data, using a PivotTable.
19. (21:23) Frequency Distribution for Discrete Quantitative Data, using a PivotTable and the Grouping Feature
20. (22:47) How to Group in a PivotTable and create Upper and Lower Limits for Counting Categories.
21. (23:49) Use Field Settings to show categories without data.
22. (25:05) Change Grouping Categories
23. (25:36) Grouping Categories Created for Discrete Quantitative Data
24. (26:08) Frequency Distribution for Continuous Quantitative Data, using a PivotTable and the Grouping Feature
25. (27:11) Grouping Categories Created for Continuous Quantitative Data
26. (28:14) What happens to PivotTable when Source Data Changes?
27. (28:32) Refreshing a PivotTable to allow new data into Report
28. (29:04) Fixing Ambiguous Grouping Categories in a PivotTable with Decimal Number Data. Three Methods.
29. (31:30) How Grouping works with PivotTable Cache.
30. (32:33) How to create Multiple Grouping Categories from a Single Data Source using PivotTable Wizard QAT Button or Keyboard: Alt, D, P.
31. (33:53) Summary of video
32. (34:40) How to Printout Notes from Excel Workbook File
33. (35:13) Homework files to download from in Canvas
34. (36:12) Closing and Video Links
Видео Excel Statistical Analysis 04: PivotTable & Power Query to Build Frequency Distributions канала ExcelIsFun
Показать
Комментарии отсутствуют
Информация о видео
Другие видео канала
![Excel Data Analysis Class 01: PivotTables, Power Query, Formulas and Charts](https://i.ytimg.com/vi/LpvCj9vCtRA/default.jpg)
![](https://i.ytimg.com/vi/8qpVKiMiWmU/default.jpg)
![Don't Use Excel Filters! Use This Incredible Excel Formula Instead ...](https://i.ytimg.com/vi/Qi1nfN5F8F4/default.jpg)
![3 Hidden Tips in Excel Power Pivot (You Probably Don't Know About)](https://i.ytimg.com/vi/xxtONTBrvNE/default.jpg)
![Excel Statistical Analysis 06: Frequency Distributions, Visualizations & Skew for Quantitative Data](https://i.ytimg.com/vi/L-KNeRTT5N4/default.jpg)
![How to Create & Use Excel Macros (Real world example)](https://i.ytimg.com/vi/An-2Nxf_Dms/default.jpg)
![Consolidate & Clean Multiple Excel Sheets in One Pivot Table](https://i.ytimg.com/vi/FI4HkCzMaIk/default.jpg)
![Find the Mean, Variance, & Standard Deviation of Frequency Grouped Data Table| Step-by-Step Tutorial](https://i.ytimg.com/vi/NhahVPv6CeM/default.jpg)
![Advanced Excel - Data Validation and Drop-Down Lists](https://i.ytimg.com/vi/SlWIgMFpsPg/default.jpg)
![How to Combine Excel Tables or Worksheets with Power Query](https://i.ytimg.com/vi/HcfKOusOJhA/default.jpg)
![Create Excel Database and Data Entry Form. Excel Magic Trick 1690.](https://i.ytimg.com/vi/0dJFkqzxlR4/default.jpg)
![Excel Pivot Tables EXPLAINED in 10 Minutes (Productivity tips included!)](https://i.ytimg.com/vi/UsdedFoTA68/default.jpg)
![Powerful trick to combine all Excel files in a folder AUTOMATICALLY](https://i.ytimg.com/vi/SGzegma9bdY/default.jpg)
![Advanced Pivot Table Techniques (to achieve more in Excel)](https://i.ytimg.com/vi/yHzT_BUggQk/default.jpg)
![Excel Tutorial for Beginners | Excel Made Easy](https://i.ytimg.com/vi/0tdlR1rBwkM/default.jpg)
![How to compute Frequency and Percentage Distribution in Excel | Data Analysis](https://i.ytimg.com/vi/GdQ5i4BmyDI/default.jpg)
![AWESOME Excel trick to combine data from multiple sheets](https://i.ytimg.com/vi/k_ugshJ4wIw/default.jpg)
![Excel Data Analysis Class 02: Power Pivot, DAX Formulas, Relationships, Data Modeling & Much More!](https://i.ytimg.com/vi/eIaKC6zLmb0/default.jpg)
![Interactive Excel Project Management Dashboard - FREE Download](https://i.ytimg.com/vi/5qtSioTE2wY/default.jpg)
![Power Pivot Tutorial & Power Query in Excel - 1-Hour Class](https://i.ytimg.com/vi/80htpBa6OaM/default.jpg)