Course Content
Orientation
Introduction, • What is Excel and its historical background • Why Excel is important in modern work • Key benefits and practical applications
0/4
Q.01: History of Excel
Q.02: Why Use Excel?
Q.03: Why is Excel More Important?
Q.04: What Are the Benefits of Using Excel?
Excel Mastery- Session # 01
Session Overview: Introduction to Excel Essentials Excel Interface & Basic Setup: In this session, learners will become confident with Excel’s user interface, ribbon, and essential workspace components. They'll discover how to customize Excel for efficiency, format cells professionally, manage rows and columns smartly, apply consistent styling, handle document properties, and explore basic file and proofing tools. This foundational knowledge sets the tone for working faster and more accurately in future sessions.
0/8
Getting started with excel
Customizing Excel for Efficiency
Cell Formatting Essentials
Row & Column Management
Color & Style Customization
File Management & Document Settings
Exporting & Publishing
Basic Proofing & Review
Excel Mastery – Session # 02
Session Overview: Data Entry & Inserting Elements In this session, learners will master efficient data entry techniques and learn how to select, autofill, and structure information accurately. They’ll also discover how to enhance spreadsheets with notes, images, icons, and SmartArt, while customizing Excel’s working behavior for comfort and speed. By the end of this session, users will be able to input, personalize, and manage data and visual content confidently within any Excel sheet.
0/8
Data Input Techniques
Cell & Range Selection Techniques
Auto Fill & Custom Lists
HOMEWORK:
Adding Notes & Media Elements
HOMEWORK:
Text Box Customization
Advanced Excel Options
Excel Mastery- Session # 03
Session Overview: Copy, Paste, Sheet, and View Tools In this session, learners will become skilled at various copy-paste techniques, including advanced options like Paste Special and copying only visible cells. They’ll also learn how to manage worksheets, workbooks, and visual layouts with precision. By the end, users will be equipped to organize data across sheets, format numbers effectively, apply visual tools, and customize views for a productive and clear workspace.
0/10
Copy, Cut, and Paste Techniques
Smart Visual Tools
Managing Workbook Windows
Worksheet Operations
Special Formatting & Text Entries
Keyboard Shortcuts & Navigation
View & Layout Customization
Header, Footer & Watermark
Clearing & Freezing Content
Fill Series & Trends
Excel Mastery- Session # 04
Session Overview:
0/6
Page Setup & Orientation
Print Titles & Sheet Setup
Print Preview & Print Settings
Advanced Go-To & Special Selections
Formula Logic & Operators
Practical Formula Applications
Excel Mastery- Session # 05
Session Overview: Mastering Cell References, Data Cleaning & Text Functions in Excel This session dives deep into Excel's referencing logic, teaching how to control and manipulate data across cells using different types of references. You'll also explore data cleaning tools, text extraction techniques, and useful ranking functions. The session ends with a fun and creative use case to reinforce learning in an engaging way.
0/5
Cell Referencing Techniques
Data Preparation Tools
Text Extraction & Manipulation Functions
Ranking Functions
Excel Enigma Machine
Excel Mastery- Session # 06
Session Overview: SUM Variations & Advanced Calculations In this session, learners will dive deeper into Excel’s summing capabilities, learning not just standard SUM, but also advanced techniques involving name ranges, multi-sheet summing, and conditional summing across unique scenarios like blanks, odd/even values, and unit-based entries. They’ll convert text to numbers using multiple methods, apply array formulas for text-based SUM, and use powerful functions like PRODUCT and SUMPRODUCT for advanced calculations. The session also introduces text case functions and wraps up with practical tools for managing financial tasks like stock registers and petty cash.
0/7
Name Range & Basic SUM
Convert Text to Number (Multiple Methods)
Text-based SUM (Array Formula)
SUM with Units (Kg/Pcs/Ltr)
PRODUCT & SUMPRODUCT Functions
Text Case Conversion Functions
Practical Financial Tools
Excel Mastery- Session # 07
Session Overview: Advanced Aggregation Functions & Date-wise Calculations In this session, learners will master powerful aggregation techniques using conditional logic. They’ll explore how to use functions like SUMIF, COUNTIF, AVERAGEIF, and their multi-criteria counterparts for detailed data analysis. Learners will also work with named ranges for cleaner formulas, detect blanks, and extract max/min values conditionally. The session ends with practical methods for grouping and summarizing data by date parts like month, year, and quarter—essential for financial and operational reporting.
0/6
Using Named Ranges for Efficient Formula Management
Conditional Summing Techniques
Conditional Averaging Methods
Conditional Counting and Blank Detection
Conditional Max-Min Analysis
Aggregating Data by Date Components
Excel Mastery- Session # 08
Session Overview: - Logical Checks, Error Handling, Data Validation & Hyperlinks
0/5
Logical Tests Using IS Functions
Understanding Excel Error Types
Formatting & Sheet Protection
Data Validation Techniques
Hyperlink Management in Excel
Excel Mastery- Session # 09
Sorting, Filtering & Dynamic Array Functions In this session, learners will master data sorting and filtering techniques to organize information clearly and efficiently. They’ll explore powerful dynamic array functions introduced in modern Excel versions to reshape, extract, and transform data more flexibly. The session also dives into row-wise and column-wise calculations using BYROW and BYCOL, enabling users to build smarter, more dynamic spreadsheets for complex data analysis.
0/5
Comprehensive Sorting Options
Advanced Filtering Techniques
New Excel Dynamic Array Functions
BYROW and BYCOL Function Logic
Other Array-Based Utilities
Excel Mastery- Session # 10
SUBTOTAL, Data Import, AI Functions & Conditional Formatting
0/4
Understanding SUBTOTAL Functions
Data Import from External Sources
ChatGPT Excel Integration
Advanced Conditional Formatting
Excel Mastery- Session # 11
Session Overview: Date & Time Functions in Excel This session dives deep into Excel’s powerful date and time functions. Learners will gain the ability to manage calendar-based data, extract date components, calculate business timelines, and handle real-life scenarios like age calculation and project scheduling. From simple date functions to advanced workday logic, this session builds a solid foundation for time-sensitive data analysis.
0/3
Basic to Advanced Date Functions
Calculating Time Durations & Workdays
Special Use Cases
Excel Mastery- Session # 12
Logical Operations and Conditional Decision Making
0/3
Mastering the IF Family
Advanced Logical Operators
Creating Rule-Based Dynamic Reports
Excel Mastery- Session # 13
Summary Overview: VLOOKUP, HLOOKUP, XLOOKUP & Database Functions In this session, learners will gain deep expertise in Excel's powerful lookup and database functions. From traditional methods like VLOOKUP and HLOOKUP to modern solutions like XLOOKUP, the session builds the ability to fetch, match, and analyze data across complex spreadsheets. Learners will also work with Excel’s database functions to summarize structured data with precision.
0/4
VLOOKUP Essentials and Enhancements
HLOOKUP Variants
XLOOKUP for Modern Excel Users
Excel Database Functions
Excel Mastery- Session # 14
Summary Overview: INDEX, MATCH, and Lookup Techniques This session equips learners with advanced data retrieval and lookup techniques in Excel. By mastering functions like INDEX, MATCH, XLOOKUP, and others, learners will be able to extract and manipulate data in complex ways. They will also explore ways to enhance data management with sorting, filtering, and visual representation using Sparklines.
0/6
INDEX & MATCH Essentials
Advanced Lookup Methods
UNIQUE, SORT, and FILTER Functions
OFFSET & CHOOSE Functions
Excel Table Management
Sparkline Graphics
Excel Mastery- Session # 15
Session Overview: Financial Functions, Randomization, and Data Clean-up In this session, learners will explore various financial functions for calculating payments, interest, present/future value, and more. They will also learn randomization techniques for generating data and rank values. The session will further dive into data cleansing, using essential functions to clean and format data. Lastly, learners will discover powerful advanced Excel features like Scenario Manager, Goal Seek, and Macros to automate tasks and optimize modeling.
0/5
Financial Formulas
Randomization Functions
Data Cleansing
Time and Date Functions
Advanced Excel Features
Excel Mastery- Session # 16
Pivot Tables, Charts, and Dashboards
0/4
Pivot Table Creation and Reporting
Pivot Table Visualization
Dashboard Design
Excel Chart Design
Bonus Session
Session Overview: Mastering LAMBDA Functions In this bonus session, learners will dive into the power of LAMBDA functions to create custom formulas in Excel. They will learn both basic and advanced LAMBDA applications, including building recursive functions and utilizing LAMBDA for dynamic operations. The session will also explore dynamic Excel features for performing operations like SUMIF, COUNTIF, AVERAGEIF, MAXIF, and MINIF by color, making data manipulation more efficient.
0/4
Introduction to LAMBDA
Advanced LAMBDA Applications
Dynamic Excel Features
Data Normalization with Power Pivot
Final Quiz
You have to get 40% mark to pass
0/1
50 Quizzes
01:30:00
Final Exam
You have to get 40% mark to pass
MS Excel Basic to Professional Master Course
About Lesson

This section focuses on HLOOKUP for horizontal data searches. Learners will understand its variations and how to manage data across rows, deal with missing values, and use it in practical applications like horizontal marksheets with backup logic.

 

  • Horizontal data search using HLOOKUP with all variations of row, workbook, and double-value lookups
  • Handling missing data with IFERROR and constructing horizontal marksheets
Previous
Next
0% Complete