Data Analysis and Statistics

Data Analysis and Statistics

OBJECTIVES

  • Learn the core concepts in working with databases and tables in Excel.
  • Develop skills to analyse data.
  • Develop further knowledge of Excel functions most relevant to data analysis.

DESCRIPTION

  • Overview. This course covers the key aspects of analysing data in Excel. It starts with coverage of databases and tables, and then discusses methods to analyse data in these structures. It covers key Excel functions for aggregation, statistical analysis, database functions, and array calculations. It covers Excel PivotTables. (The focus of this course is on the analyse of clean data sets; the later course II.2 Data Analysis and Manipulation covers the cleaning and general manipulation of data and data sets).
  • Practical Work and Exercises. Readers are expected to build simple examples for themselves as they follow the text. The course also contains downloadable data sets or simple models that allow a reader to do this practical work without having to enter large sets of data or repeat previous steps.
  • Quizzes and Exercises. There are 5 Quizzes, which are placed throughout the course. Some require the construction of small models or parts of models.

KEY TOPICS

  • Introduction to data sets and analysis. Databases in Excel. Filters and sorting. Criteria-based data extraction. Advanced filters. Excel Tables · Pivot Tables. Slicers. Drill-downs. Introduction to PowerPivot. DAX measures. Introduction to the Excel DataModel.
  • Excel functions for data analysis. Database functions. Conditional aggregations · Statistical functions · Array calculations and bespoke logic · Statistics of single variables. Statistics of multiple variables. Averages, ranking, percentiles, deviations, correlations, confidence intervals, regression and multiple regression.
  • Core elements of data manipulation. Cleaning and correcting. Removing duplicates. Splitting and joining. Identifying unique items. Creation of keys. Lookup functions. Text functions.  Other Excel functions. Introduction to PowerQuery. 

QUIZZES

  • Tables, Filters, PivotTables (1 Quiz).
  • Functions and Statistics (3 Quizzes)
  • Data manipulation (1 Quizzes)