- 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.
- 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.
- Datasets and databases in Excel. General versus database structures and functionality. Filters and sorting. Excel Tables. Advanced filters and criteria-based extraction. Pivot Tables. Slicers. Drill-downs. I
- Dynamic ranges and functions. Unique values, filters, and sorting. Variable-sized input and output ranges. Advanced lookup functions. Data Structures for modelling. Selection/exclusion and consolidation/inclusion structures.
- Statistical analysis and functions. Single variable statistics (e.g. averages, ranking, percentile, standard deviations). Confidence intervals. Multi-variable statistics (e.g. correlation, rank correlation, Kendall’s tau). Linear regression. Multiple regression. Array calculations and bespoke logic.
- Probabilistic analysis. Probability distributions. Inverse functions. Random sampling. Statistical analysis. Choice of distributions. Use of distributions to model risk and uncertainty. Conditional probability. Bayesian analysis. Value of test-based information.
- Data manipulation with Excel. Cleaning and correcting. Splitting and joining. Flat tables. Identifying unique items. Creation of keys. Lookup functions. Text functions. Information and other Excel functions.
- Using PowerQuery. Loading data. Creating connections. Generating reports. Grouped queries. Pivoting and unpivoting. Appending and consolidating data sets. Advanced queries and manipulation with the M-language.
- PowerPivot. Introduction to PowerPivot. Filter and Evaluation Contexts and Measures. The DAX languages. The Excel DataModel. Using Relational databases. Functions and iterator functions. Sensitivity analysis. CUBE functions. Time-intelligence. Complex queries. Links to Power BI.
- Tables, Filters, PivotTables (1 Quiz).
- Functions and Statistics (3 Quizzes)
- Data manipulation (1 Quizzes)