
A) ADVANCE EXCEL FUNCTIONS
1. XLOOKUP (Modern replacement for VLOOKUP/HLOOKUP)
2. FILTER
3. SUMIF
4.SUMIFS
5. COUNT
6. COUNTIF
7. COUNTIFS
8. IF
9. IFS
10. AND
11. OR
1. Introduction to Power Query (Get & Transform)
What is Power Query?
ETL concept (Extract, Transform, Load)
Advantages over traditional Excel formulas
Where to find it in Excel (Data tab → Get & Transform)
2. Data Import (Extract)
Importing data from:
Excel files
CSV/Text files
Web sources
Folder (multiple files)
Databases (SQL Server, Access)
Understanding Query Editor interface
Preview vs Load options
3. Power Query Interface Deep Dive
Query Editor layout:
Ribbon
Applied Steps pane
Data preview
Understanding steps recording
Undo vs step deletion
4. Basic Data Cleaning
Remove rows (top, bottom, blank)
Remove duplicates
Replace values
Change data types
Rename columns
Sorting & filtering
5. Data Transformation Techniques
Split columns (by delimiter, position)
Merge columns
Add custom columns
Extract text (left, right, middle)
Formatting (uppercase, lowercase, trim)
6. Working with Dates & Numbers
Date transformations:
Year, Month, Quarter extraction
Calculations:
Add column with formulas
Data type importance
7. Combining Data
Merge Queries (JOIN operations)
Inner join
Left join
Right join
Full outer join
Append Queries
Combining multiple tables (stacking)
8. Working with Multiple Files
Folder import automation
Combine & transform multiple files
Handling dynamic file structures
9. Advanced Transformations
Pivot & Unpivot columns
Group By (aggregation)
Conditional columns (IF logic)
Index column
Fill down / fill up
Data Import (Extract)
Importing data from:
Excel files
CSV/Text files
Web sources
Folder (multiple files)
Merge Queries (JOIN operations)
Inner join
Left join
Right join
Full outer join
CREATING PIVOT TABLES, PIVOT CHARTS AND DASHBOARDS FOR THE FOLLOWING PROBLEM STATEMENT,
C1) Problem Statement: Retail Sales Performance Analysis
Retail businesses like Next Look and Fashions Direct operate across multiple locations, product categories, and time periods. However, decision-makers often face challenges in:
Understanding overall sales performance
Comparing performance between different retail chains
Identifying top-performing products, locations, and sales representatives
Tracking sales trends over time
Analyzing category-wise and regional performance
Filtering insights dynamically by year, month, and chain
C2) Problem Statement: Call Center Performance Analysis
Objective of the Dashboard
The primary objective of this dashboard is to:
Provide a comprehensive analysis of call center KPIs
Track and evaluate agent-wise performance
Monitor key metrics, including:
Total Calls
Calls Reached
Deals Closed
Deal Value
Average Call Duration
Call Drop Rate
Analyze monthly performance trends
Enable interactive filtering by agent for focused insights
C2) Problem Statement: Call Center Performance Analysis
Objective of the Dashboard
The primary objective of this dashboard is to:
Provide a comprehensive analysis of call center KPIs
Track and evaluate agent-wise performance
Monitor key metrics, including:
Total Calls
Calls Reached
Deals Closed
Deal Value
Average Call Duration
Call Drop Rate
Analyze monthly performance trends
Enable interactive filtering by agent for focused insights
More than 60 percent of employers say they want workers with problem-solving skills, Forbes reports. As workers train on Excel, they will build up those skills. As they learn to navigate Excel’s functions and formulas, they’ll be breaking down complex problems into manageable components, building their logical thinking and analytical reasoning abilities. Microsoft Excel powers productivity for millions of professionals worldwide. Since its launch in 1985, Excel has evolved into the go-to tool for data management, analysis, and visualization. You'll learn to organize complex data, perform advanced calculations, and create professional presentations that make a lasting impression. Our structured approach ensures that you build a solid foundation, whether you're starting from scratch or filling knowledge gaps. Learn by Practice, Train yourself with real-world examples and datasets. Master time-saving techniques professionals use daily. Visual Learning, Create compelling charts and graphs that tell data stories. Build Real Dashboards, Transform messy data into executive-ready reports that tell a story.
Some 33 percent of businesses say they are struggling with retention, according to a recent SCORE survey. Training can make a big difference: 45 percent of workers say that if their employers offered more training, they’d be more likely to stay at their current jobs, according to consulting firm Devlin Peck. When you invest in building Excel skills, you are showing a commitment to personal and professional development. Given Excel’s wide use across diverse businesses, proficiency with the software is an almost-universally applicable skill, one that can enhance productivity and help drive success across a variety of roles and industries.