Data Analyst Course

Module 1: Foundations & Advanced Excel (Weeks 1-2)

Goal: Master the most common analysis tool and build a strong conceptual foundation.

●​ Session 1: The Data Analyst Mindset & Excel Basics

○​ Topic: What is data analysis? (Roles, responsibilities).
○​ Contents: The 6 steps of the data analysis lifecycle (Ask, Prepare, Process,
Analyze, Share, Act). Introduction to Excel: navigation, data entry, and cell
formatting. Understanding data types (string, number, date).
○​ Hands-on: Importing data (CSV, TXT). Using Sort, Filter, and creating Tables.
●​ Session 2: Excel Functions for Data Cleaning
○​ Topic: Cleaning and shaping data.
○​ Contents: Text functions (LEFT, RIGHT, MID, TRIM, CLEAN, CONCATENATE,
LEN). Logical functions (IF, AND, OR, IFERROR).
○​ Hands-on: Cleaning a messy "customer list" dataset.
●​ Session 3: Excel Functions for Analysis
○​ Topic: Lookups and conditional aggregation.
○​ Contents: VLOOKUP (and its flaws). The superior INDEX & MATCH combo.
The modern XLOOKUP.
○​ Hands-on: Combining two different spreadsheets (e.g., "Sales" and "Product
Info") using lookup functions.
●​ Session 4: Aggregating Data with Pivot Tables
○​ Topic: Summarizing data without formulas.
○​ Contents: Introduction to Pivot Tables. Rows, Columns, Values, and Filters.
Using SUMIFS, COUNTIFS, AVERAGEIFS as a formula-based alternative.
○​ Hands-on: Creating a summary report from a large "sales" table.
●​ Session 5: Advanced Pivot Tables
○​ Topic: Making Pivot Tables dynamic.
○​ Contents: Grouping data (by date, by numerical range). Slicers and
Timelines. Calculated Fields.
○​ Hands-on: Building an interactive sales dashboard in an Excel tab.
●​ Session 6: Excel Visualization & What-If Analysis
○​ Topic: Charting and basic forecasting.
○​ Contents: Creating effective charts (bar, line, scatter). Pivot Charts.
Introduction to What-If Analysis (Goal Seek, Data Tables).
○​ Hands-on: Building a final, shareable Excel dashboard.

Module 2: Database Fundamentals with SQL (Weeks

3-4)
Goal: Learn the language of data. How to "ask" a database for the exact information you
need.

●​ Session 7: Introduction to SQL & Databases

○​ Topic: What is a relational database?
○​ Contents: Understanding schemas, tables, primary keys, and foreign keys.
The SELECT and FROM statements. Filtering with WHERE. Using LIMIT.
○​ Hands-on: Writing your first queries to retrieve specific data from a table.
●​ Session 8: Filtering & Sorting in SQL
○​ Topic: Advanced filtering logic.
○​ Contents: AND, OR, NOT. IN (for lists). BETWEEN (for ranges). LIKE (for
pattern matching with % and _). ORDER BY (ASC, DESC).
○​ Hands-on: Querying a "movie" or "product" database with complex filtering
criteria.
●​ Session 9: Aggregating Data in SQL
○​ Topic: Summarizing data.
○​ Contents: The GROUP BY clause. Aggregate functions: COUNT, SUM, AVG,
MIN, MAX.
○​ Hands-on: Answering business questions (e.g., "How many sales per store?"
or "What is the average rating per movie genre?").
●​ Session 10: SQL Joins (Part 1)
○​ Topic: Combining tables.
○​ Contents: The concept of JOINs. INNER JOIN (the default). LEFT JOIN.
○​ Hands-on: Combining an "orders" table with a "customers" table to see
customer names on each order.
●​ Session 11: SQL Joins (Part 2) & Unions
○​ Topic: Advanced data combining.
○​ Contents: RIGHT JOIN and FULL OUTER JOIN (and why LEFT JOIN is
usually enough). Joining multiple tables. UNION vs. UNION ALL.
○​ Hands-on: Answering a complex question that requires 3-4 tables (e.g.,
"What is the total sales amount per product category, for customers in New
York?").
●​ Session 12: Advanced SQL
○​ Topic: Subqueries and Common Table Expressions (CTEs).
○​ Contents: Using a query inside another query (subquery). The WITH clause
(CTEs) to make queries cleaner and more readable.
○​ Hands-on: Re-writing a complex JOIN query using a CTE.

Module 3: Data Visualization (Weeks 5-6)

Goal: Learn to show your findings and tell a compelling story. (This syllabus will use Power
BI as the example tool. The concepts are identical for Tableau).

●​ Session 13: Intro to Power BI & Data Connections

 ○​ Topic: The Power BI ecosystem.
○​ Contents: Power BI Desktop vs. Service. Connecting to data (Excel, CSV,
SQL Server). The 3 views: Report, Data, Model.
○​ Hands-on: Importing 3-4 tables into Power BI.
●​ Session 14: Data Modeling in Power BI
○​ Topic: Building the "brain" of your report.
○​ Contents: Understanding relationships (one-to-many, many-to-one). Star
Schema (Fact vs. Dimension tables). Hiding fields and setting data types.
○​ Hands-on: Creating a clean star schema from the imported tables.
●​ Session 15: Data Transformation with Power Query
○​ Topic: The "Process" step from Session 1.
○​ Contents: The Power Query Editor. Appended steps. Splitting columns.
Changing data types. Unpivoting data. Merging and Appending queries.
○​ Hands-on: Cleaning a messy dataset before it gets loaded into the model.
●​ Session 16: Introduction to DAX
○​ Topic: The formula language of Power BI.
○​ Contents: Calculated Columns vs. Measures. Implicit vs. Explicit Measures.
Basic DAX: SUM, AVERAGE, COUNT, DISTINCTCOUNT. Introduction to
CALCULATE.
○​ Hands-on: Writing 5-10 key measures for your data model (e.g., "Total
Sales," "Avg. Order Value," "YoY Sales %").
●​ Session 17: Building Visualizations
○​ Topic: Creating your first dashboard.
○​ Contents: The visualization pane. Key visuals: Bar/Column charts, Line
charts, Pie/Donut charts, Cards, and Slicers. Formatting and themes.
○​ Hands-on: Building a 1-page "Executive Summary" dashboard.
●​ Session 18: Interactive Dashboards & Storytelling
○​ Topic: Making your report "clickable."
○​ Contents: Page navigation. Filters (visual, page, report level). Drill-through
pages. Bookmarks. Data Storytelling principles (how to build a narrative).
○​ Hands-on: Creating a multi-page interactive dashboard and publishing it to
Power BI Service.

Module 4: Programming for Data Analysis (Weeks 7-8)

Goal: Use Python and its libraries to perform analysis that is too large or complex for Excel.

●​ Session 19: Python & Jupyter Notebook Basics

○​ Topic: Why Python?
○​ Contents: Setting up your environment (e.g., Google Colab or Anaconda).
Introduction to Jupyter Notebooks. Python basics: variables, data types
(string, int, list, dictionary).
○​ Hands-on: Writing your first Python commands.
●​ Session 20: Intro to Pandas
○​ Topic: Excel on steroids.
 ○​ Contents: The Pandas library. What is a DataFrame? What is a Series?
Reading data (pd.read_csv, pd.read_excel).
○​ Hands-on: Loading a CSV into a DataFrame and using .head(), .info(),
and .describe().
●​ Session 21: Data Selection with Pandas
○​ Topic: Selecting and filtering data (the [], .loc, and .iloc methods).
○​ Contents: Selecting columns. Filtering rows based on single or multiple
conditions.
○​ Hands-on: Answering questions like "Show me all sales from the 'North'
region with a value over $1000."
●​ Session 22: Data Cleaning with Pandas
○​ Topic: Handling messy data.
○​ Contents: Finding missing data (.isnull()). Dropping data (.dropna()).
Filling data (.fillna()). Handling duplicates (.drop_duplicates()).
○​ Hands-on: Cleaning a "real-world" messy dataset.
●​ Session 23: Data Manipulation with Pandas
○​ Topic: Grouping and Merging.
○​ Contents: The .groupby() method (similar to SQL). Aggregating data
(.agg()). Merging DataFrames ([Link], similar to SQL JOINs).
○​ Hands-on: Replicating a SQL GROUP BY and JOIN query, but in Pandas.
●​ Session 24: Basic Visualization with Python
○​ Topic: Plotting with Matplotlib & Seaborn.
○​ Contents: Introduction to Python visualization libraries. Seaborn for quick,
beautiful plots.
○​ Hands-on: Creating basic plots: histplot, boxplot (for distributions),
barplot, and scatterplot (for relationships).

Module 5: Statistics & Capstone (Weeks 9-10)

Goal: Tie all skills together with foundational statistics and a portfolio-ready project.

●​ Session 25: Essential Statistics for Analysts

○​ Topic: Understanding your data's "shape."
○​ Contents: Descriptive Statistics (Mean, Median, Mode, Standard Deviation,
Variance). Distributions (Normal, Skewed).
○​ Hands-on: Using Pandas .describe() and histograms to analyze
distributions.
●​ Session 26: Core Statistical Concepts
○​ Topic: Correlation and Hypothesis Testing.
○​ Contents: Correlation vs. Causation (and why it's the most important rule).
What is a p-value? (Conceptual understanding). What is A/B testing?
○​ Hands-on: Building a correlation matrix in Seaborn to find relationships in
data.
●​ Session 27: Capstone Project - The "Ask" & "Prepare"
○​ Topic: Kicking off the final project.
○​ Contents: You will be given a large, multi-table dataset (e.g., e-commerce,
movie ratings, public health data).
○​ Hands-on: Define business questions. Use SQL to query and join the data.
Export the data to a CSV.
●​ Session 28: Capstone Project - The "Process" & "Analyze"
○​ Topic: Cleaning and analyzing the project data.
○​ Contents: Using your tool of choice (Pandas or Power Query) to clean the
data you extracted.
○​ Hands-on: Perform your main analysis. Find insights, trends, and anomalies.
●​ Session 29: Capstone Project - The "Share"
○​ Topic: Building your final dashboard.
○​ Contents: In-class workshop.
○​ Hands-on: Load your clean, analyzed data into Power BI or Tableau. Build a
2-3 page dashboard that tells the "story" of your findings.
●​ Session 30: Capstone Presentations & Career Prep
○​ Topic: Presenting your findings.
○​ Contents: Each student presents their capstone dashboard and findings
(5-10 min).
○​ Hands-on: Final Q&A. Discussion on portfolio building, resume tips, and how
to continue learning.