Microsoft Excel
Week 1: Excel Basics & Fundamentals
● Objective: Learn the Excel interface and fundamental operations.
● Topics:
o Excel Interface: Ribbon, cells, rows, columns, navigation
o Data Entry & Formatting: Text, numbers, dates, bold, borders, number
formats
o Basic Formulas: SUM(), AVERAGE(), COUNT(), MIN(), MAX()
o Cell Referencing: Relative, Absolute ($A$1), Mixed
o Basic sorting and filtering
o Introduction to Data Types (Text, Numbers, Dates)
o Data Validation (basic rules)
● Practice: Create simple reports with basic functions and formatting.
Week 2: Data Handling & Organization
● Objective: Learn how to organize, filter, and validate data.
● Topics:
o Sorting & Filtering Data
o Conditional Formatting (rules, color scales, icon sets)
o Data Validation (Dropdowns, Lists)
o Removing Duplicates
o Grouping & Ungrouping Data
o Introduction to Data Cleaning Functions: CLEAN(), TRIM(),
SUBSTITUTE()
● Practice: Create a data entry form with validation and explore conditional
formatting rules.
Week 3: Essential Functions for Data Analysis
● Objective: Master essential functions used for data transformation and basic
analysis.
● Topics:
o Logical Functions: IF(), AND(), OR(), NOT(), Nested IF()
o Lookup Functions: VLOOKUP(), HLOOKUP()
o Text Functions: LEFT(), RIGHT(), MID(), LEN(), CONCATENATE(), TEXT()
o Error Handling: IFERROR(), ISERROR()
● Practice: Use logical functions to clean data and lookups to join datasets.
Week 4: Advanced Lookup & Reference Functions
● Objective: Deepen your knowledge of lookup techniques and dynamic
referencing.
● Topics:
o Advanced Lookups: INDEX(), MATCH(), XLOOKUP()
o Named Ranges and Dynamic Named Ranges
o Error Handling: IFERROR(), ISNA()
o Basic Aggregation Functions: SUMIFS(), COUNTIFS(), AVERAGEIFS()
● Practice: Perform dataset lookups using INDEX() + MATCH() and manage
missing values.
Week 5: Data Analysis Tools & PivotTables
● Objective: Learn how to summarize data using PivotTables and core analysis
tools.
● Topics:
o Data Tables and Structured References
o PivotTables & PivotCharts (creating, grouping, filtering)
o Slicers & Timelines in PivotTables
o Calculated Fields & Items in PivotTables
o Introduction to Data Consolidation
● Practice: Create PivotTables for reports and visualize them with PivotCharts.
Week 6: Statistical & Mathematical Functions
● Objective: Learn functions for statistical analysis and mathematical
calculations.
● Topics:
o Descriptive Statistics: AVERAGE(), MEDIAN(), MODE(), STDEV(), VAR()
o Rounding Functions: ROUND(), ROUNDUP(), ROUNDDOWN(),
CEILING(), FLOOR()
o Frequency Distributions: FREQUENCY(), COUNTIF(), COUNTIFS()
o Percentages, Ratios, Proportions
● Practice: Analyze datasets using statistical functions and summaries.
Week 7: Advanced Data Manipulation
● Objective: Master advanced data transformation and cleaning techniques.
● Topics:
o Array Formulas (Introduction) and Dynamic Arrays
o Functions for Data Manipulation: FILTER(), UNIQUE(), SORT()
o Text-to-Columns, Flash Fill, and other Data Cleaning Techniques
● Practice: Work with messy datasets, cleaning them using advanced
functions and tools.
Week 8: Data Visualization with Excel
● Objective: Create professional data visualizations.
● Topics:
o Basic Charts: Bar, Column, Line, Pie Charts
o Advanced Charts: Combo Charts, Waterfall, Sparklines
o Chart Customization: Axis Labels, Legends, Data Labels, Gridlines
o Dynamic Charts: Using Named Ranges and OFFSET()
● Practice: Create interactive reports using dynamic charts and conditional
formatting.
Week 9: Data Analysis Add-ons & Power Query
● Objective: Automate data cleaning and transformation using Power Query.
● Topics:
o Introduction to Power Query (Get & Transform)
o Importing Data from External Sources (CSV, SQL)
o Data Cleaning: Removing Duplicates, Replacing Values, Merging
Queries
o Unpivoting Data for Analysis
● Practice: Use Power Query to clean and transform large datasets.
Week 10: Macros & VBA for Automation
● Objective: Automate repetitive tasks with Macros and VBA scripts.
● Topics:
o Introduction to Macros and the Developer Tab
o Recording and Running Macros
o Editing VBA Code for Custom Functions
o Automating Reports with VBA
● Practice: Record a Macro that automates data cleaning or report generation.
Week 11: Advanced Excel Functions for Data Science
● Objective: Learn key Excel functions for advanced data science tasks.
● Topics:
o Advanced Statistical Functions: LINEST(), FORECAST(), CORREL(),
COVAR()
o Time Intelligence Functions: DATEDIF(), NETWORKDAYS(),
WORKDAY()
o Solver Add-in for Optimization
● Practice: Build a forecasting model and calculate correlations with dataset
trends.
Week 12: Capstone Project & Case Study
● Objective: Apply all skills to a real-world data science problem.
● Project:
o Data Cleaning (using Power Query)
o Data Transformation (Lookup, Logical, and Array Functions)
o Data Analysis (PivotTables, Statistical Functions)
o Data Visualization (Charts, Dashboards)
● Presentation: Prepare a final report or dashboard showcasing the findings.
