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 a wide set of Excel functionality and functions that enable such analysis, including to manipulate data sets, as well as to conduct statistical analysis. In addition to the coverage of classical PivotTables, the course covers the use of the PowerPivot add-in to create more powerful analysis and to integrate related data sets. The functionality of PowerQuery is also used to complement the use of classical Excel functionality to manipulate, consolidate and analyse data, and to link to external sources.
- Practical work and exercises. Students are required to conduct numerous hands-on modelling exercises and can also follow along by replicating other examples that are shown in the text.
- Assessment tests. There are several assessment tests which test the key concepts and require one to do practical exercises in data analysis in Excel.
KEY TOPICS
- 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.
Take the course
II.b Data Analysis, Manipulation and Statistics
This course covers intermediate and advanced level data analysis, including the manipulation, consolidation and integration of data sets.