Excel Statistical Analysis - PivotTable & Power Query to Build Frequency Distributions

Overview

Explore advanced Excel techniques for statistical analysis in this comprehensive 36-minute video tutorial. Master the use of Power Query to import and transform CSV file data, and learn how to create frequency distributions for categorical and quantitative data using PivotTables. Dive into topics such as PivotTable caching, user interface, and calculation methods. Discover efficient keyboard shortcuts, data type management in Power Query, and field merging techniques. Gain insights into creating and modifying frequency distributions for nominal, discrete quantitative, and continuous quantitative data. Learn how to handle source data changes, refresh PivotTables, and fix ambiguous grouping categories. Enhance your Excel skills with practical examples, downloadable resources, and valuable homework assignments to reinforce your learning.

Syllabus

) Introduction to topics in video.
) Tabular Reporting goals for this video: 4 Different Frequency Distributions.
) PivotTable to create Frequency Distribution from Nominal Data that is located in the worksheet.
) What is the PivotTable Cache?.
) Introduction to PivotTable User Interface.
) Change calculation in PivotTable with: Summarize Values By and Shows Values As.
) Alt Keyboard Efficiency Tricks to create PivotTable and other features.
) Power Query to Import & Transform CSV file with table of data.
) What is a CSV File? What is a Delimiter?.
) Where is the Power Query tool in Excel?.
) What are Data Types in Power Query? And why are they Important!.
) Merge two fields to create one element field.
) Load Power Query Table to Excel Worksheet.
) Edit Query after it has been loaded.
) Terms to describe the table, fields and data..
) Frequency Distributions for different types of data.
) Frequency Distributions defined.
) Frequency Distribution for Nominal Data, using a PivotTable..
) Frequency Distribution for Discrete Quantitative Data, using a PivotTable and the Grouping Feature.
) How to Group in a PivotTable and create Upper and Lower Limits for Counting Categories..
) Use Field Settings to show categories without data..
) Change Grouping Categories.
) Grouping Categories Created for Discrete Quantitative Data.
) Frequency Distribution for Continuous Quantitative Data, using a PivotTable and the Grouping Feature.
) Grouping Categories Created for Continuous Quantitative Data.
) What happens to PivotTable when Source Data Changes?.
) Refreshing a PivotTable to allow new data into Report.
) Fixing Ambiguous Grouping Categories in a PivotTable with Decimal Number Data. Three Methods..
) How Grouping works with PivotTable Cache..
) How to create Multiple Grouping Categories from a Single Data Source using PivotTable Wizard QAT Button or Keyboard: Alt, D, P..
) Summary of video.
) How to Printout Notes from Excel Workbook File.
) Homework files to download from in Canvas.
) Closing and Video Links.