Advanced Excel for Data Analysis - 14-
Day Mastery Roadmap
Week 1: Foundation & Functions for Data Analysis
Day 1: Excel Essentials & Data Handling
Importing Data (CSV, TXT, Excel, web)
Data cleaning basics
Sorting and Filtering (AutoFilter, Advanced Filter)
Data validation & drop-down lists
Freeze Panes, Split, Named Ranges
Practice: Clean a messy dataset (sales, customer, or employee data).
Day 2: Text Functions & Data Cleaning
LEFT, RIGHT, MID, LEN, FIND, SEARCH, TRIM, CLEAN, SUBSTITUTE, REPLACE, TEXT,
CONCAT, TEXTJOIN
Practice: Clean and format unstructured names, emails, addresses.
Day 3: Logical & Lookup Functions
Logical: IF, IFS, AND, OR, NOT, IFERROR
Lookup: VLOOKUP, HLOOKUP, INDEX, MATCH, XLOOKUP
Combine INDEX-MATCH and nested IFs
Practice: Build a dynamic employee salary checker or stock lookup tool.
Day 4: Date & Time Functions
TODAY, NOW, DAY, MONTH, YEAR, WEEKDAY
DATEDIF, NETWORKDAYS, WORKDAY
Calculate age, service tenure, due dates
Practice: Create a leave tracker or employee tenure calculator.
Day 5: Advanced Data Cleaning
Remove duplicates, blanks
Use Go To Special
Text to Columns
Power Query basics: Load, transform, clean data
Practice: Load and transform a dataset with Power Query (e.g., messy sales data).
Day 6: Basic Charts & Visualizations
Column, Bar, Line, Pie, Combo Charts
Formatting charts, data labels, dynamic titles
Sparklines
Use named ranges in charts
Practice: Create a dashboard showing monthly sales trends.
Day 7: Pivot Tables & Slicers
Create pivot tables
Grouping, summarizing, drill-down
Calculated fields, filters, slicers
Pivot Charts
Practice: Analyze sales data by region, product, and month.
Week 2: Automation, Dashboards, and Projects
Day 8: Power Query Advanced
Merge, Append Queries
Unpivot data
Advanced transformations
Practice: Clean and consolidate multiple Excel files/sheets into one.
Day 9: Power Pivot & DAX
Add data model, relationships
Introduction to DAX: CALCULATE, FILTER, ALL, RELATED
Create KPIs using DAX
Practice: Build a KPI dashboard with multiple tables.
Day 10: Excel Dashboards (Static & Dynamic)
Design principles
Form controls (dropdowns, checkboxes)
Linked charts, conditional formatting
Interactive dashboards
Practice: Build a dynamic dashboard (e.g., Product Performance Tracker).
Day 11: Scenario, Goal Seek, What-If Analysis
Scenario Manager
Goal Seek, Solver
Data Tables (one-variable & two-variable)
Practice: Profit prediction model with what-if analysis.
Day 12: Excel VBA Basics (Optional but Powerful)
Recording Macros
VBA Editor: Modules, Sub, MsgBox, InputBox
Automating repetitive tasks
Practice: Automate report generation or formatting tasks.
Day 13: Mock Projects & Case Studies
Sales Dashboard
HR Leave Tracker
Customer Churn Analysis
Inventory Management Tool
Financial Budget Planner
Day 14: Interview Preparation + Review
Review key formulas, shortcuts, concepts
Practice common Excel interview questions
Prepare to explain your project logic: What problem you solved, how you used Excel
tools
