Module 1.1: Advanced Data Handling & Cleaning

Purpose: Real-world datasets are messy. Learn to clean, transform, and prepare data efficiently.

Topics Covered

• Data Cleaning Techniques
• Data Protection Techniques
• Removing Duplicates, Blanks, and Errors
• Text-to-Columns and Flash Fill (AI Functionality in Excel)

Industry Functions

• Text & String Functions: TRIM, CLEAN, SUBSTITUTE, TEXT, LEFT, RIGHT, MID, LEN
• Case Formatting Functions: UPPER, LOWER, PROPER
• Search & Replace Functions: FIND, SEARCH, REPLACE, VALUE, TEXTJOIN
• Error & Logical Validation Functions: IFERROR, ISNUMBER, ISTEXT, ISBLANK
• Date & Time Functions: DATEDIF, EDATE, EOMONTH, NOW, TODAY, DAY, MONTH, YEAR, HOUR, MINUTE, SECOND

Additional Concepts

• Using Data Validation for Input Control
• Dynamic Data Validation

Module 1.2: Advanced Formulas & Logical Functions

Purpose: Build dynamic, decision-based reports and formulas used across corporate functions.

Industry Functions

• IF, IFS, SWITCH
• AND, OR, NOT
• CHOOSE, INDEX, MATCH, XLOOKUP
• OFFSET, INDIRECT
• Dynamic Array Functions: FILTER, SORT, UNIQUE, SEQUENCE
• Nested IF and Multi-Condition Formulas

Module 1.3: Advanced Formulas & Logical Functions

Purpose: Learn to analyze, summarize, and interpret business data effectively.

Topics Covered

• Understanding and Creating KPIs
• Summarizing Large Datasets

Industry Functions

• SUMIFS, COUNTIFS, AVERAGEIFS
• SUBTOTAL, AGGREGATE
• LARGE, SMALL, RANK
• ROUND, ROUNDUP, ROUNDDOWN

Module 1.4: Advanced Lookup & Reference

Purpose: Master data linking across sheets and files — essential for analytics and reporting.

Topics Covered

• VLOOKUP vs XLOOKUP (with practical applications)
• Dynamic Range Referencing using INDEX-MATCH
• Cross-Workbook Linking and Handling Broken Links

Module 1.5: Data Visualization & Dashboards

Purpose: Learn how professionals visualize and communicate insights effectively.

Topics Covered

• Chart Design Best Practices
• Dynamic Charts with Form Controls
• Combo Charts, Sparklines, Conditional Formatting
• KPI Indicators (Traffic Lights, Data Bars, Icons)
• What-If Analysis for Business Scenario Testing

Module 1.6: Pivot Tables & Pivot Charts

Purpose: Master Excel’s most powerful tool for data summarization and analysis.

Topics Covered

• Creating and Customizing PivotTables
• Calculated Fields and Items
• Grouping by Date, Month, and Quarter
• Using Slicers and Timelines
• Creating Hundreds of Pivot Tables in Seconds
• Making Complex Pivot Tables Neat and Insightful

Module 1.7: Power Tools in Excel (for Analysts)

Purpose: Use Excel’s advanced BI tools for large-scale analytics and automation.

Topics Covered

• Power Query (Merge, Append, Clean Data)
• Power Pivot (Data Models, KPIs, Measures)
• Connecting Excel with External Data Sources (CSV, SQL)
• Introduction to RDBMS, Primary & Foreign Keys
• Building Table Relationships and Data Modelling

Module 1.8: Professional Efficiency & Productivity

Purpose: Work faster, smarter, and more accurately.

Topics Covered

• Advanced Shortcuts for Data Analysts
• Named Ranges, Dynamic Arrays
• Workbook Linking, Auditing, and Error Tracing
• Formula Debugging and Error Checking
• Version Control, Backup, and Template Creation

Module 1.9: Excel Integration & Productivity

Purpose: Integrate Excel with modern tools and leverage automation and AI.

Topics Covered

• Excel with Google Sheets (Cloud Collaboration)
• ChatGPT + Excel (AI-Assisted Formula Writing)
• Excel with SQL (Data Import & Export)
• Learning Techniques for Excel Shortcuts
• Universal vs Non-Universal Shortcuts

Module 1.10: Advanced Dashboard Development

Purpose: Build professional, interactive dashboards.

Topics Covered

• Creating Different Types of Charts
• Designing Advanced Dashboards Using Summarized Data
• Dynamic Dashboards with Form Controls

Assignments & Live Projects

• Hands-On Practice

Introduction

• Flowcharts
• Step-by-Step Instructions
• Process Flow Diagrams
• KPIs
• Algorithms

VBA/Macros Basics to Advanced

Subs, Functions, Variables, Arrays, Loops, Logic, Arrays etc

• Excel Macro Language Review (VBA) Including Variables, Data Types, Constants, Arrays, Operators, Expressions, Loops, Logic Decisions and Calling
• Overview Of Commanding Excel Using VBA Including
• A Discussion Of Objects, Properties and Methods
• The Power of Macros – What, When, How to use Macros.

Introduction to Object Oriented Concepts

• Objects, Its Functions, Methods and Properties Introduction to Events
• Details of Events, How & When to use of Events, Preparing to ‘Macro’ Visual Basic Editor (VBE) – Developer Tab, Security
• Introduction to the VBE, Properties window, Project Explorer, Password Protection of Code How to use the VBE – Features, Options, Intelligence Technology
• Debugging Mode, Bookmarks, Breakpoints, Watch Window, Immediate Window and Locals Window Inbuilt VBE Help Feature – Tips and Tricks
• Form Controls vs. ActiveX Controls
• Getting into the Code
• Message Box and Input Box Working with Data in Excel through VBA

Data Types

• Constants and Variables
• Different type of data type; How and When to use Variables to Store Information.

Loops

• For-Next, For-Each, Do-While, Do until, Do Loop Decision-Making and Code Branching
• If-Then-Else,
• Select-Case
• And
• Nested Conditions
• What is user’s Defined Functions?
• How to create & use them.

Arrays

• Use of Arrays in VBA programming with one dimensional, two dimensional or multi-dimensional analysis

Excel VBA Power Programming For VBA Macros

• Working with Dynamic Ranges. Protecting Worksheets, Cells and Ranges. Working with Multiple Files. Opening & Saving Files
• How to Analyze Data On Multi Worksheets And Build Summary Sheets
• How to Access The Windows File And Folder System To Open And Close Workbooks
• How to Protect Your Code Against Errors
• How to Use Excel And VBA To Create Basic Dash Boards
• How to Create Your Own Custom Business Worksheet Functions In VBA
• How to Create Basic Report Generation Tools Using Excel VBA, Microsoft Word And PowerPoint
• How to Use The Excel Visual Basic Macro Recorder To Record Excel Tasks In VBA And Then Interpret The Code

Overview of User forms To Create Business Wizards

• Working with User Forms & User Forms Events like List box, Combo box, Option Buttons, Check box, Text box, Labels, Command button, Toggle button.
• How to create dynamic dashboard on user form with different controls
• How to link various user form with each other to create a complete interface between user and system

Connection between Excel VBA & other platforms

• How to Establish Connection Between VBA and Internet Explorer to Open any Internet Website through VBA
• How to Establish Connection Between Excel VBA and power presentation to create power point through VBA
• How to Establish Connection Between Excel VBA and Access database to update the data in access through VBA
• How to Establish Connection Between Excel VBA and outlooks through VBA
• How to Establish Connection Between Excel VBA and MS Word through VBA

Testing and Debugging Your Code

• Types of Errors
• Using Breakpoints
• Debugging Techniques

Dialogue Boxes

• UsingMsgBox Function
• UsingInput Box Function
• Working with FileDialog
• Working GetOpenFile name Method
• Working GetSaveAsFilename Method

Effective Error Handling ChatGPT AI for Excel and VBA

Module 1: Introduction To Databases & SQL

• What Is A Database?
• Types Of Databases (Relational, Non-Relational)
• What Is SQL? SQL Command Categories (DDL, DML, DCL, TCL)

Module 2: Database & Table Creation

• Creating Databases // Creating Tables
• Data Types In SQL
• Keys & Constraints: Primary Key, Foreign Key, NOT NULL, UNIQUE, CHECK, DEFAULT

Module 3: Insert, Update & Delete

• INSERT Statements
• UPDATE Statements
• DELETE Statements

Module 4: Basic Data Retrieval

• SELECT Statements
• Selecting Specific Columns
• Aliases (AS)
• DISTINCT Keyword

Module 5:Filtering Data

• WHERE Clause
• Comparison Operators
• Logical Operators (AND, OR, NOT)
• LIKE Pattern Matching
• IN, BETWEEN, IS NULL

Module 6: Sorting & Limiting

• ORDER BY
• Sorting By Multiple Columns
• LIMIT / TOP / FETCH

Module 7: Aggregate Functions & Grouping

• GROUP BY
• HAVING Clause
• COUNT(), SUM(), AVG(), MIN(), MAX()
• GROUP BY With Aggregates
• Filtering Aggregates (HAVING)

Module 8: SQL Functions – String

• CONCAT()
• LENGTH() / LEN()
• LOWER() / UPPER()
• SUBSTRING() / SUBSTR()
• TRIM(), LTRIM(), RTRIM()
• REPLACE()
• INSTR() / CHARINDEX()

Module 9: SQL Functions – Date/Time

• NOW(), CURRENT_TIMESTAMP
• CURRENT_DATE, CURRENT_TIME
• DATEADD(), DATEDIFF()
• EXTRACT()
• DATE_FORMAT(), FORMAT()
• DAY(), MONTH(), YEAR()

Module 10: SQL Functions – Math

• ABS(), ROUND()
• FLOOR(), CEILING()
• POWER(), SQRT()
• RAND()

Module 11: SQL Functions – Conversion

• CAST(), CONVERT()
• TO_CHAR(), TO_DATE()

Module 12: SQL Functions – Conditional

• CASE Expression
• NULLIF(), COALESCE()
• IIF()

Module 13: SQL Functions – Window

• PARTITION BY
• ORDER BY (Window)
• ROW_NUMBER()
• RANK(), DENSE_RANK()
• FIRST_VALUE(), LAST_VALUE()

Module 14: Working With Joins

• Inner Join // Left Join // Right Join
• Full Join // Self Join // Cross Join
• Left Anti Join // Right Anti Join

Module 14: Working With Joins

• Inner Join // Left Join // Right Join
• Full Join // Self Join // Cross Join
• Left Anti Join // Right Anti Join

Module 15: Sub queries

• Subqueries In WHERE
• Subqueries In SELECT
• Subqueries In FROM
• Correlated Subqueries

Module 16: Views

• Creating Views
• Updating Views
• Dropping Views

Module 17: Introduction To RDBMS

• What Is An RDBMS?
• DBMS Vs RDBMS
• Features Of RDBMS
• Popular Systems: MySQL, PostgreSQL, SQL Server, Oracle

Database Concepts

• Tables, Rows, Columns
• Schemas & Catalogs
• Primary & Foreign Keys
• Candidate & Composite Keys

Data Models

• Relational Model
• Entities & Attributes
• 1-1, 1-Many, Many-Many Relationships
• ER Diagrams

Module 18: SQL Server Agent (Job Scheduling)

• Overview Of SQL Server Agent
• Creating A Job
• Adding Job Steps
• Creating A Schedule

Module 1: Introduction to MS Access

• What is MS Access?
• Features of MS Access
• Understanding the Access interface
• Databases, tables, objects overview
• Differences between Access and other RDBMS

Module 2: Database Fundamentals

• Creating a new database
• Database file types (.accdb, .mdb)
• Opening, saving, and closing databases
• Navigation Pane & object organization

Module 3: Tables in MS Access

• Creating tables (Design View / Datasheet View)
• Field names and data types
• Field properties (size, format, input mask)
• Primary key selection
• Creating lookup fields
• Table relationships overview

Module 4: Data Types & Field Properties

• Short Text, Long Text // Number, Large Number
• Date/Time, Currency // Yes/No
• Hyperlink // Lookup Wizard // Attachment
• Field validation rules

Module 5: Working With Data

• Adding records
• Editing records
• Deleting records
• Sorting & filtering data
• Searching records

Module 5: Working With Data

• Adding records
• Editing records
• Deleting records
• Sorting & filtering data
• Searching records

Module 6: Keys & Relationships

• Primary keys // Foreign keys
• One-to-One relationships // One-to-Many relationships
• Referential integrity // Relationship window

Module 7a: Queries – Types of Queries

• Select Queries
• Action Queries (Append, Update, Delete, Make-Table)Crosstab Queries
• Find Duplicates Queries // Find Unmatched Queries

Module 7b: Queries – Concepts

• Query Design View // Adding criteria
• Sorting, filtering //Using expressions
• Calculated fields

Module 8: Forms

• Introduction to forms
• Creating forms (Form Wizard, Design View)
• Form layouts
• Controls (Text box, Combo box, Buttons)
• Binding forms to data // Navigation forms
• Subforms

Module 9: Reports

• Creating reports // Report Wizard // Grouping & sorting
• Headers & footers // Summary reports
• Printing & exporting reports

Module 10: Importing & Exporting Data

• Import from Excel // Export to Excel
• Importing text files // Linking tables (external data)
• Connecting to SQL databases

8 Major Points to Learn MS Access in Analytics

• 1. Easy Data Management for Non-Technical Users
• 2. Relational Database Capabilities
• 3. Powerful Querying (SQL Support)
• 4. Perfect for Small & Medium Projects
• 5. Integration With Excel & Business Tools
• 6. Forms & Reports for Business Insights
• 7. Automation Through Macros & VBA
• 8. Best for Teaching Structured Data Concepts

Microsoft Power BI – Introduction

• What is MSPBI& Scope
• Learn the common work flow in Power BI
• Building blocks of Power BIand its relations
• Quick demo how to create a business dashboard in MSPBI
• MSPBI components

MS Power BI – Getting Business Data

• Get data in shape for use with Power BI
• Combining two or more data sets (source data) for reporting
• Tackling messy data in MS Power BI
• Clean &Transform data

MS Power BI -Data Visualization

• Create and customize visualization
• Use combination charts
• Create and format slicers
• Map visualizations
• Visualizations utilization
• Use tables and matrixes
• Long live bubbles
• scatter charts in action
• Advanced funnel and waterfall charts
• Drive fast dashboard insights with gauges and numbers
• Color your visualization world with colors
• shapes and scales
• Adding personal touch
• logo etc. to reports and dashboards
• Display and present your dashboard in a way you want with summarize data
• Control how your report elements overlap with each other
• Learn to drill into hierarchies
• Manage how levels are shared (Z-order in reports)
• How to use R visuals in MSPBI

MS Power BI -Data Exploring & Sharing

• Quick insights in Power BI Service
• Create and configure a dashboard
• Share dashboard with your organization
• Display and edit visuals- tiles
• full screen
• Get more space on your dashboard
• Install and configure a personal gateway
• Excel and MSPBI
• Import and excel table into Power BI
• Import excel files with data models and power view sheets
• Connect One Drive for business to MSPBI
• Excel data in Power BI summary

MS Power BI – DAX (Data Analysis Expression) Application

Setting up Data Models with DAX

• DAX for creating tables and columns
• Rows vs. query vs. filter context
• DAX for calculated tables and columns
• Creating a date table
• Calculated column for costs
• Data cleaning with DAX
• Connecting data from different tables
• Methods to create DAX measures
• Advantages of explicit measures V2
• DAX and measures
• Using variables
• Basic statistical measures
• Quick measures

Power BI – Common DAX Measures

• Filtering and counting with DAX
• Understanding different filter functions
• Using different filters with DAX
• Filter ALL the data
• Calculating with a filter
• Analyzing across dimensional tables
• Iterating functions
• DIY iterating functions
• Iterating functions in Power BI
• Practice with iterating functions
• More iterating functions
• Use of RANKX()

Power BI – Redefine DAX

• Logical functions
• Interpreting SWITCH()
• Logical functions in Power BI
• IF() for formatting tables
• Exploring SWITCH()
• Grouping
• Row-level security
• Applying row-level security
• Managed roles in Power BI
• Creating an email list
• Implementing RLS

Power BI – Advanced DAX

• Table manipulation functions
• Summary of SUMMARIZE()
• Table manipulations using DAX
• SUMMARIZE() the facts
• ADDCOLUMNS()? No problem!
• Time intelligence functions
• Time intelligence functions output
• Time intelligence in Power BI
• Use of TOTALYTD()
• Use of SAMEPERIODLASTYEAR()

Module 9: Reports

• Creating reports // Report Wizard // Grouping & sorting
• Headers & footers // Summary reports
• Printing & exporting reports

Module 10: Importing & Exporting Data

• Creating reports // Report Wizard // Grouping & sorting
• Headers & footers // Summary reports
• Printing & exporting reports

Tableau – Introduction

• Getting Started with Tableau
• Overview Of Tableau
• Tableau Architecture

Tableau – Connecting to Data

• Managing Metadata
• Managing Extracts
• Data Sources
• Cross-Database Joins
• Data Aggregation And Data Ports
• Tableau Charts
• Bar Charts and Stacked Bars Data Blending
• Tree Maps and Scatter Plots
• Individual Axes
• Blended Axes
• Dual Axes
• Combinational Chart

Tableau -Data Visualization/Visual Analytics

• Drill Down
• Hierarchies
• Sorting
• Filtering
• Grouping
• Trend
• Reference Lines
• Forecasting
• Clustering
• Analysis with Cubes
• MDX

Tableau -Developing First Bar Chart

• Connecting Tableau to Data File
• Navigating Tableau
• Calculated Fields
• Adding
• Colors
• Labels
• Formatting

Tableau – Time Series, Maps and Aggregation

• Data Extracts and Time Series
• Understanding Granularity, Aggregation and Level of Details
• Default Location in Maps
• Custom Geo Coding
• Symbol Map and Filled Map

Tableau- First Dashboard

• Into Section
• Joining Data In Tableau
• Working With Maps
• Hierarchies
• Scatter Plot
• Applying Filters in Different Sheets
• Creating 1st Dashboard
• Creating 2nd Dashboard

Tableau -Blending Data and Dual Axis Charts

• Duplicate Values
• Multiple Fields
• Data Blending
• Dual Axis Chart
• Building Calculated Fields

Tableau -Table Calculation

• Downloading Dataset and Connection
• Mapping
• Building Table Calculation For Gender
• Bins and Distributions for Age
• Tree Map Chart

Tableau – Advanced Dashboard

• Advanced Dashboard

Tableau – Storytelling

• Storyline and Storytelling

Tableau – Data Preparation

• Data Format
• Data Interpreter
• Multiple Columns And Pivot
• Metadata Grid

Advanced Data Preparation

Module 7.1.1: Introduction to Python Programming

Purpose:Build a strong foundation in Python syntax and programming concepts essential for data analysis.

Topics Covered

• Introduction to Python What is Python and why it’s popular for Data Analysis Installing Python, Jupyter Notebook, and Anaconda
• Python Basics Data Types: int, float, str, bool Variables and Constants Input and Output operations Type Conversion and Casting
• Operators Arithmetic, Logical, Comparison, and Assignment Operators
• Control Flow Conditional Statements: if, elif, else Loops: for and while Loop Control Statements: break, continue, pass

Module 7.1.2: Data Structures in Python

Purpose:Learn how to store, organize, and manipulate data efficiently using Python’s built-in structures.

Topics Covered

• Core Data Structures Lists, Tuples, Sets, Dictionaries
• Operations Indexing and Slicing Adding, Updating, and Removing Elements
• Comprehensions List Comprehension Dictionary Comprehension
• Common Built-in Functions len(), max(), min(), sum(), sorted(), type()

Module 7.1.3: Functions, Modules, and Packages

Purpose:Learn how to write reusable and modular Python code for analytical workflows.

Topics Covered

• Functions
  • Defining and Calling Functions
  • Return Statements and Parameters
  • Default, Positional, and Keyword Arguments
• Modules
  • Using Built-in Modules (math, datetime, os, random)
  • Creating and Importing Custom Modules
• Packages Installing and Managing Packages using pip

Module 7.1.4: Working with Files

Purpose:Learn how to import, read, write, and manage external datasets effectively.

Topics Covered

• File Handling Basics — open(), read(), write(), close()
• Reading and Writing Files (CSV, TXT)
• Working with File Paths using os and pathlib
• Writing Processed Data to Files

Module 7.1.5: Introduction to Data Analysis with Pandas

Purpose:Master the most widely used Python library for real-world data manipulation and analysis.

Topics Covered

• Pandas Basics Introduction to Series and Data Frame
• Reading & Writing Data read_csv(), to_csv(), read_excel(), to_excel()
• Exploring Data head(), .tail(), .info(), .describe()
• Selecting & Filtering Data .loc[], .iloc[] for row/column selection Conditional filtering (df[df[‘column’] > value])

Data Cleaning

• Handling Missing Data (fillna, dropna)
• Adding, Renaming, and Dropping Columns
• Sorting and Grouping Data (sort_values, groupby)

Module 7.1.6: Data Visualization with Matplotlib & Seaborn

Purpose:Develop the ability to present data visually for better insights and reporting.

Topics Covered

• Introduction to Matplotlib – Basic Plot Types: Line, Bar, Pie, Histogram, Scatter Customizing Visuals (titles, labels, colors, legends)
• Seaborn for Statistical Visualization – Plot Types: barplot, countplot, boxplot, heatmap, pairplot
• Styling and Aesthetics
• Exporting Charts Saving visuals in .png and .pdf formats

Module 7.1.7: Data Cleaning & Preprocessing

Purpose:Learn how to prepare raw datasets for analysis — an essential industry skill.

Topics Covered

• Handling Missing Values
• Detecting & Removing Duplicates
• Changing Data Types (astype())
• String Operations on Columns (.str.upper(), .str.strip(), .str.replace())
• Date Conversion using pd.to_datetime()
• Identifying and Removing Outliers
• Basic Feature Engineering for better model performance

Module 7.1.8: Introduction to NumPy

Purpose:Learn efficient numerical computing — the backbone of Python data analytics.

Topics Covered

• NumPy Basics
• Arrays vs Lists
• Creating and Manipulating Arrays
• Array Operations
• Indexing, Slicing, and Broadcasting Vectorized Computations
• Mathematical Functions mean(), median(), std(), sum(), sqrt(), dot()
• Random Module Generating Random Numbers (np.random.rand(), np.random.randint())

Module 7.1.9: Mini Projects & Practical Assignments

Purpose:Apply your learning to hands-on, real-world projects.

Mini Projects

• Sales Data Analysis (Pandas + Matplotlib) Cleaning and visualizing monthly sales data
• COVID Data Dashboard Using Pandas and Seaborn to visualize cases and recovery trends
• Customer Segmentation (CSV File Analysis) Filtering, grouping, and reporting based on demographic data