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