Excel for Data Analytics - Detailed
Syllabus
Module 1: Introduction to Excel for Data Analytics
Topics Covered:
- Overview of Excel in the context of data analytics
- Excel interface: ribbons, menus, toolbars
- Understanding workbooks, worksheets, rows, and columns
- Data types: Text, Numbers, Date/Time, Boolean
- Data entry, formatting, and shortcuts
Objectives:
- Navigate Excel efficiently
- Use keyboard shortcuts to improve productivity
- Understand how Excel fits into the data analysis pipeline
Module 2: Data Cleaning and Preparation
Topics Covered:
- Removing duplicates
- Handling blank cells and missing data
- Text-to-columns
- Find & Replace, Go To Special
- Data validation (dropdowns, input messages, error alerts)
- Trimming and cleaning text using: TRIM(), CLEAN(), SUBSTITUTE(),
REPLACE()
- Parsing and combining data using: LEFT(), RIGHT(), MID(), FIND(),
LEN(), CONCATENATE(), TEXTJOIN()
Objectives:
- Prepare raw data for analysis
- Eliminate inconsistencies and errors
Module 3: Working with Excel Functions
Topics Covered:
Basic Functions: SUM(), AVERAGE(), MIN(), MAX(), COUNT(),
COUNTA()
Logical Functions: IF(), AND(), OR(), NOT(), IFERROR(), IFS()
Lookup & Reference: VLOOKUP(), HLOOKUP(), XLOOKUP(), INDEX(),
MATCH(), OFFSET()
Date & Time: TODAY(), NOW(), DATEDIF(), NETWORKDAYS(),
EDATE(), EOMONTH(), TEXT()
Text Functions: UPPER(), LOWER(), PROPER(), TEXT(), SEARCH()
Formula editing tips and tricks
Objectives:
- Master core Excel functions for manipulating and retrieving data
- Handle conditional logic and dynamic data referencing
Module 4: Data Sorting, Filtering, and Conditional Formatting
Topics Covered:
- Sorting by one or multiple columns
- Filtering using AutoFilter
- Advanced filters
- Conditional formatting based on rules, formulas, data bars, color scales
- Highlighting duplicates, top/bottom values
- Advanced Condition formatting
Objectives:
- Quickly analyze trends and patterns visually
- Filter large datasets for insights
Module 5: Data Visualization in Excel
Topics Covered:
- Chart types: Column, Bar, Line, Area, Pie, Scatter, Combo
- Creating and customizing charts
- Chart elements (titles, axis, legend, gridlines, data labels)
- Slicers and Timelines with PivotTables
- Sparklines
Objectives:
- Create compelling visuals
- Enhance presentations and reports
Module 6: PivotTables and PivotCharts
Topics Covered:
- Creating PivotTables from raw data
- Field arrangement: rows, columns, values, filters
- Grouping data (dates, numbers, text)
- Calculated fields and items
- PivotCharts for visual analysis
- Refreshing and updating PivotTables
Objectives:
- Analyze large datasets dynamically
- Gain insights using summarization
Module 7: Excel Data Analysis Tools
Topics Covered:
What-If Analysis: Goal Seek, Data Tables, Scenario Manager
Solver Add-in: Optimization problems, Constraints
Analysis ToolPak: Mean, Median, Mode, Std Dev, Histogram, Frequency
Objectives:
- Perform scenario and sensitivity analysis
- Generate statistical summaries
Module 8: Modern Excel -DAX and Power Query+ ChatGPT
Topics Covered:
- Importing data: CSV, TXT, Excel, Web, Database
- Power Query Editor: Transformation, Merge, Append
- Column operations: split, combine, custom columns
- Data refresh automation
Objectives:
- Automate data ingestion and transformation
- Clean and reshape data
Module 9: Power Pivot and Data Modeling
Topics Covered:
- Enabling Power Pivot
- Creating data models
- Calculated columns and measures using DAX
- Common DAX: SUMX(), CALCULATE(), FILTER(), RELATED()
Objectives:
- Handle advanced data models
- Perform complex aggregations using DAX
Module 10: Advanced Dashboard Reporting (DAX and
Visualization )
Topics Covered:
-Data storytelling Tips
- Designing interactive dashboards
- Linking charts, tables, slicers
- Creating dynamic charts
- Automating reports with basic VBA
- Best practices for layout and storytelling
Objectives:
- Build interactive dashboards
- Present data effectively
Module 11- Analytics Using Python in EXCEL
Modules 12 – Industry Related Example
