Course Outline: Data Analysis with Excel: From Beginner to Data Analyst
or Business Analyst
Objective: Equip learners with essential data analysis skills, from understanding data
fundamentals to advanced tools like Power Query, Power Pivot, Pivot Tables, and DAX for
creating impactful reports and dashboards.
Module 1: Fundamentals of Data Literacy Skills
• What is Data?
o Definition and importance of data.
o Types of data: structured vs. unstructured.
• Data Formats:
o Text, Numbers, Dates, and Categories.
• Data Sources:
o Internal vs. External Sources.
• Importance of Data in Decision-Making:
o How data informs business strategies.
o Real-world examples of data-driven decision-making.
• Overview of the DIKW Model (Data → Information → Knowledge → Wisdom):
o Understanding how raw data transforms into actionable insights.
• Basics of Data Collection:
o Common methods and best practices for data gathering.
• Basic Data Profiling:
o Introduction to data profiling and its role in quality management.
• Data Quality Management:
o Handling missing and inconsistent data.
• Data Cleaning Techniques:
o Common techniques for preparing data for analysis.
• Data Privacy and Ethics:
o Best practices for responsible data handling and privacy regulations (GDPR, etc.).
Module 2: Data Cleaning and Preparation
• Text Functions:
o LEFT, RIGHT, MID: Extract parts of a text string.
o LEN: Count the length of a text string.
o TRIM: Remove extra spaces.
o SUBSTITUTE: Replace text in a string.
o PROPER, UPPER, LOWER: Format text case.
• Logical Functions:
o IF: Perform conditional calculations.
o AND, OR, NOT: Combine logical conditions.
o ISERROR, IFERROR: Handle errors in formulas.
• Lookup & Reference Functions:
o VLOOKUP, HLOOKUP: Retrieve data from a table.
o INDEX, MATCH: Advanced lookup for flexibility.
o CHOOSE: Select a value from a list.
• Error Handling:
o ISBLANK: Check for empty cells.
o IFNA: Handle #N/A errors specifically.
• Practical Applications:
o Conditional calculations and error trapping for data cleaning.
• Case Study:
o Clean and prepare a dataset with missing values, duplicates, and inconsistencies.
Module 3: Basic Statistics for Data Analysis
Descriptive Statistics
• Measures of Central Tendency:
o Mean, Median, Mode.
• Measures of Dispersion:
o Range, Variance, Standard Deviation, IQR.
• Shape of Data:
o Skewness (symmetry of data distribution).
o Kurtosis (peak and tail heaviness).
Data Distributions
• Normal Distribution: Analyze standard deviations and Z-scores.
• Histograms: Visualize frequency distribution.
Correlation and Relationships
• Calculate correlation coefficients (e.g., CORREL).
• Visualize relationships using scatter plots.
Sampling
• Types of Sampling:
o Random Sampling.
o Stratified Sampling.
o Systematic Sampling.
• Sampling Bias: Identify and mitigate errors in sample selection.
Module 4: Data Models, Power Pivot, Power Query, and Pivot Tables
Objective: Introduce data modeling, Power Pivot, Power Query, Pivot Tables, and their role in
advanced data analysis and transformation.
• What is a Data Model?
o Relational data concepts and linking tables in Excel.
• Power Pivot Overview:
o Loading data into Power Pivot and managing it within the Data Model.
o Creating relationships between tables.
• Power Query for Data Transformation:
o Introduction to Power Query and its role in data extraction and transformation.
o Connecting to data sources (Excel, CSV, databases, etc.).
o Cleaning and transforming data with Power Query Editor: filtering, sorting, grouping,
and merging queries.
o Basic transformations: Text formatting, column splitting, replacing values.
o Data transformation examples: Date format changes, filtering rows, removing
duplicates.
o Automating data transformations using Power Query.
• Pivot Tables in Excel:
o Introduction to Pivot Tables: what they are and how to create them.
o Summarizing data with Pivot Tables: counting, summing, and averaging data.
o Grouping data in Pivot Tables: Date grouping, custom groupings, and more.
o Using Pivot Table fields for advanced data analysis.
o Calculated fields in Pivot Tables.
• Hands-On:
o Build a data model, perform data transformations with Power Query, and
summarize data using Pivot Tables for a sales report.
Module 5: Introduction to DAX (Data Analysis Expressions)
Objective: Learn the basics of DAX for writing complex calculations and measures.
• What is DAX?
o Introduction to DAX and its role in advanced Excel analysis.
• Basic DAX Formulas:
o Writing basic DAX formulas: SUM, AVERAGE, COUNTROWS, DISTINCT.
• Logical Functions in DAX:
o IF, SWITCH, and nested functions in DAX.
• Time Intelligence in DAX:
o Creating Year-to-Date (YTD), Month-to-Date (MTD), and Previous Year (PY)
calculations.
• Measures vs. Calculated Columns:
o Understanding the differences and when to use each.
• Hands-On:
o Write DAX measures for common business metrics like sales growth and customer
retention.
Module 6: Advanced Analysis and Interactive Dashboards
Objective: Use Power Pivot, DAX, and Pivot Tables to build dynamic, interactive dashboards for
decision-making.
• Advanced DAX Functions:
o CALCULATE, FILTER, and other complex functions.
o RELATED and RELATEDTABLE for working across tables.
o RANKX and advanced ranking functions.
• Creating Interactive Dashboards:
o Using Pivot Tables, slicers, and timelines in Power Pivot.
o Customizing and visualizing data with advanced charts.
• Dynamic Reports and Key Metrics:
o Building dashboards to track KPIs and trends.
• Case Study:
o Create an interactive sales or financial performance dashboard using Power Pivot,
Power Query, DAX, and Pivot Tables.
Module 7: Scenario Analysis, Forecasting, and Final Project
Objective: Apply forecasting techniques and integrate all learned skills into a capstone project.
• Scenario Analysis:
o Using What-If tools (Goal Seek, Data Tables, Scenario Manager) to model business
scenarios.
• Forecasting in Excel:
o Trendlines and predictive models using Power Pivot.
• Guided Project - Sales Analysis with the AdventureWorks Database:
o Analyze the AdventureWorks database for sales performance.
o Build a dynamic sales dashboard using Power Pivot and DAX.
o Perform data transformations with Power Query (e.g., cleaning, shaping, and
merging data).
o Perform forecasting and scenario analysis to predict sales trends and optimize
strategies.
Final Deliverables
• Guided Project: Complete the AdventureWorks Sales Analysis project using the
AdventureWorks database, incorporating Power Query, Power Pivot, Pivot Tables, and
DAX.
The course projects
#Project 1
#Project 2
Data Model Sample
FoodMart Data Model
Guided Sample Project
Revenue Forecast Case Study Sample
