RouteMap of Data Analysis
Module 1: Excel for Analytics (Ep 1–8)
Ep 1: What is Data Analytics + Why Excel?
• Real-world data: Netflix, Swiggy, Cricket
• Intro to Excel/Sheets: rows, columns, cells
• CSV vs XLSX vs XLS
• Excel limitations = motivation for SQL/Python
• Fun: Google Sheets Treasure Hunt
Ep 2: Data Entry, Formatting & Shortcuts
• Data types, autofill, wrap text, merge, freeze
• Keyboard shortcuts: Ctrl + ;, Ctrl + D, Alt + E, S, V
• Challenge: Make your own daily timetable
Ep 3: Formulas 101 – SUM, AVERAGE, COUNT, IF
• Formula bar, operators
• Logical functions: IF, AND, OR
• Mini Task: Budget Tracker with IF logic
Ep 4: Intermediate Formulas – VLOOKUP, HLOOKUP, CONCAT
• Lookup functions (with note on XLOOKUP)
• Text functions: CONCAT, LEN, LEFT, RIGHT
• Task: Match student names to scores
Ep 5: Data Filtering, Sorting & Conditional Formatting
• Multi-level sort, filters, rules
• Color scales, icon sets
• Mini Task: Attendance Heatmap
Ep 6: Pivot Tables Deep Dive
• Pivot basics: row, column, value fields
• Filters, slicers
• Fun: “School Performance Dashboard”
Ep 7: Charts – Column, Pie, Line, Bar
• Which chart when?
• Formatting tricks: dual-axis, data labels
• Task: Create 3 charts from IPL stats
Ep 8: Excel Dashboard Project
• Combine pivots + charts + slicers
• Project: Sales Report for Fictional Store
Module 2: SQL for Analysis (Ep 9–16)
Ep 9: Intro to Databases & SQL
• Tables, rows, columns, primary & foreign keys
• DB Tools: SQLite Browser, DB-Fiddle
• Task: Create own student marks DB
Ep 10: SELECT & WHERE
• SELECT fields, WHERE clauses
• Logical operators: =, >, <, AND, OR, LIKE
• Task: Get students with marks > 90
Ep 11: ORDER BY, LIMIT, BETWEEN, IN
• Sorting, ranges, Top N results
• Challenge: Build a Top 10 leaderboard
Ep 12: GROUP BY + HAVING + Aggregates
• SUM, AVG, COUNT, MIN, MAX
• GROUP BY logic
• HAVING vs WHERE
• Task: Avg marks by subject where avg > 80
Ep 13: Aliases + CASE Statements
• Renaming with AS
• CASE logic: Grade by score
• Task: Grade all students
Ep 14: JOINs Deep Dive – INNER, LEFT, RIGHT, OUTER
• Joining customers + orders
• Foreign keys, NULLs
• Task: Match orders to customers
Ep 15: Subqueries + Nested SELECT
• SELECT inside SELECT
• Filters with results
• Fun: Find the topper of each class
Ep 16: SQL Project – Mini School DB
• Design schema (ERD included)
• Full CRUD + JOIN queries
• Project: Analyze Student + Library DB
Module 3: Python for Analytics (Ep 17–24)
Ep 17: Python Basics + Installing Jupyter
• print(), variables, types
• Jupyter cells, Markdown
• Task: Make a student profile dictionary
Ep 18: Lists, Loops & Functions
• for loop, while loop
• list operations
• Task: Random marks generator
Ep 19: File Handling & CSV Reading
• open(), read(), csv module
• Pandas read_csv()
• Task: Print first 5 lines of dataset
Ep 20: pandas DataFrames & Series
• DataFrame creation, indexing
• head(), tail(), iloc, loc
• Task: Filter students scoring > 80
Ep 21: Cleaning with pandas
• isnull().sum(), dropna(), fillna()
• Convert types, rename columns
• Task: Clean messy shopping data
Ep 22: EDA & Descriptive Stats
• .describe(), .mean(), .std()
• Correlations, plotting basics
• Task: Titanic Dataset analysis
Ep 23: Grouping & Merging
• .groupby(), .agg(), .merge()
• Task: Merge branch-wise student data
Ep 24: Python Project – Data Cleaning Pipeline
• Load → Clean → Analyze → Export
• Project: Student Admissions Analysis
Module 4: Data Visualization & Projects (Ep 25–30)
Ep 25: Matplotlib Basics
• plt.plot(), bar(), pie()
• Titles, labels, legend
• Task: Plot study hours
Ep 26: Seaborn Advanced Visualization
• distplot, boxplot, heatmap
• sns themes + colors
• Task: Visualize class performance
Ep 27: Dashboards in Power BI or Tableau
• Importing CSV
• Building charts, slicers, KPIs
• Task: Sales or School Dashboard
Ep 28: Data Storytelling – Insight > Numbers
• Telling a story from analysis
• Insight formula: "Compared to X, Y increased Z%"
• Task: IPL data summary (5 points)
Ep 29: Capstone Project (Part 1 – Data Collection + Cleaning + EDA)
• Pick domain: Health, Education, Sports
• Clean + analyze + visualize
• Checklist for scoring
Ep 30: Capstone (Part 2 – Dashboard + Resume/Portfolio)
• Build dashboard/report
• Publish on GitHub + LinkedIn
• Career prep: resume, interview, portfolio tips
