Intermediate Data Analytics - Microsoft Excel
Course Description
In today’s data-driven landscape, businesses and organizations rely heavily on data analytics to inform strategic, operational, and financial decisions. Excel, as a robust and widely adopted analytics tool, empowers users to extract value from data, uncover trends, and generate actionable insights. This intermediate-level course is meticulously designed to equip learners with the practical skills necessary to perform advanced data analytics using Microsoft (MS) Excel, enhancing their ability to support data-driven decision-making and effectively communicate insights.
Building on the introductory data analytics course on MS Excel (beginner level), the course takes participants deeper into the world of data analytics. Learners will gain hands-on experience with advanced tools and techniques, including connecting to external data sources, cleaning and transforming data using Power Query, building relational data models with Power Pivot, and applying statistical and predictive methods such as regression and hypothesis testing. In addition, participants will explore prescriptive analytics using What-If Analysis, Goal Seek, and Solver, as well as perform basic sentiment analysis through Azure Machine Learning Excel add-in. Real-world datasets and scenarios are used throughout to provide practical context and deepen understanding. Spanning six comprehensive modules, this course enables learners to develop a holistic, hands-on command of Excel as a data analytics application and prepares them to deliver impactful business insights.
Learning Outcomes:
On successful completion of this course, learners will be able to:
1. Retrieve and structure data from diverse external sources for analysis in Excel.
2. Clean, transform and prepare datasets for analysis using both built-in tools and M scripting in Power Query
3. Build efficient data models for large datasets.
4. Use DAX (Data Analysis Expressions) for enhanced data models.
5. Apply basic forecasting and regression techniques to predict trends and outcomes using historical data.
6. Use Excel to recommend decision options through scenario modeling, goal seeking, and constraint-based optimization.
7. Conduct hypothesis testing to support data-driven decision-making and validate assumptions using statistical methods.
-
Course Outline
Module 1: Connecting to External Data Sources
Lesson 1: Understanding Data Structures in Excel
Lesson 2: Getting Data from Excel Ranges and Tables
Lesson 3: Importing Data from Databases
Module 2: Data Munging with Power Query
Lesson 1: Merging and Appending Queries
Lesson 2: Cleaning and Transforming Data Using Power Query Commands/Interface
Lesson 3: Advanced Data Wrangling using Power Query M Language
Module 3: Data Modeling with Power Pivot
Lesson 1: Introduction to Power Pivot and Data Models
Lesson 2: Creating Relationships between Tables in the Data Model
Lesson 3: Enhancing Data Models with Calculated Columns and Measures
Module 4: Predictive Analytics
Lesson 1: Forecasting Future Values from Historical Data
Lesson 2: Forecasting Future Values with Regression Analysis
Lesson 3: Introduction to Sentiment Analysis with Azure Machine Learning Add-in
Module 5: Prescriptive Analytics
Lesson 1: Scenario Planning with Spreadsheet-Based What-if Analysis
Leson 2: Optimization using Goal Seek and Solver
Module 6: Hypothesis Testing and Statistical Inference
Lesson 1: Comparing Sample Means (t-tests)
Lesson 2: Performing Analysis of Variance (ANOVA)
Learning Resources
Recommended Books
1. Excel Data Analysis For Dummies, by Paul McFedries (Author), 5th edition, Wiley
2. Microsoft Excel Data Analysis and Business Modeling (Office 2021 and Microsoft 365), by Wayne Winston (Author), 7th edition
3. Statistical Analysis with Excel For Dummies, by Joseph Schmuller (Author), 5th edition, Wiley
4. Marketing Analytics: Data-Driven Techniques with Microsoft Excel, by Wayne L. Winston (Author)
5. Business Analytics: Data Analysis & Decision Making, by S. Christian Albright (Author) and Wayne L. Winston (Author), 7th edition, Cengage
6. Excel Power Pivot & Power Query For Dummies, by Michael Alexander (Author), 2nd edition, Wiley
7. Applied Predictive Analytics. Principles and Techniques for the Professional Data Analyst, by Dean Abbot (Author), 2014, Wiley
8. Healey, Joseph F. & Steven G. Prus. 2019. Statistics: A Tool for Social Research, 4th Canadian Edition. Nelson Education Ltd.
II. Online Resources
Occasionally, the instructor will share selected articles, videos, and links to other online resources, which will be sent by email or made available via Google Drive.
Evaluation Modalities
1. Individual in-class assignment
2. Weekly Quizzes
3. Home assignments
Equipment Requirements
Excel 2021 / Office 365. Almost everything covered in this course should work for other Excel versions (2010, 2013, 2016, 2019 & Office 365).
-
Ability to speak, understand and write in English (CLB6)
Ability to use a computer with a stable internet connection.
Taken Introductory Data Analytics on MS Excel course
-
CAD $625
-
Waldo Beauséjour is a seasoned senior data analyst with over a decade of experience in the field of data analytics. His extensive expertise spans various sectors, including government, international development, research organizations, the private sector, and healthcare. Waldo holds a Master’s degree in applied economics, equipping him with a robust foundation in quantitative methods. His passion for education and teaching has driven him to serve as a college instructor in Canada, a dedicated tutor and graduate assistant in the USA, and a respected university professor in his home country. He firmly believes that a strong grasp of statistics empowers individuals to interpret data with confidence, thereby enhancing their capacity to extract valuable insights for informed decision-making. This conviction has led him to dedicate a significant portion of his professional journey to training professionals in the field of data analytics and equipping them with the necessary tools for effective data analysis.
-
Training model: online
Course length: 6 weeks, 30h