Advanced Excel From Basics to Advanced -35 Hrs

Complete Advanced Excel Course – From Fundamentals to Advanced Excel

If you are thinking of learning Excel properly, this is the course for you. This is a 9+ hour deep-dive Excel course designed to take you from the fundamentals of Excel formulas to advanced, practical techniques used in real-world office projects.

The approach throughout the course is simple:

How does it work? And WHY does it work?

We don't just learn formulas—we understand the logic behind them, their limitations, combinations, and how they can be applied to solve practical Excel problems.

Excel Fundamentals & Formula Concepts

We start from the very beginning so that the foundation is absolutely clear.

• Understand Cells, Rows, Columns and Cell Addresses

• Formula Bar, Name Box and other important Excel features

• Difference between formulas and constants

• Essential Excel shortcut keys

• Understanding the $ sign and absolute, relative and mixed references

• Why locking and unlocking cells is important

• Practical examples showing exactly when and why cell references need to be locked

VLOOKUP – From Basic to Advanced

Take a complete deep dive into one of Excel's most widely used functions.

• Understand how VLOOKUP works

• Rules that must be followed while using VLOOKUP

• Advantages and limitations of VLOOKUP

• VLOOKUP within the same worksheet

• VLOOKUP across different worksheets

• VLOOKUP across different workbooks

• What happens when lookup values are repeated?

• How to handle multiple matching records

• Which lookup approach should be used and why?

• VLOOKUP using constants

• VLOOKUP using helper columns and helper rows

• VLOOKUP combined with MATCH

• Using multiple VLOOKUPs to search through different datasets

• Using IFERROR to make VLOOKUP more flexible

• Practical office-style VLOOKUP problems

MATCH Function – Understanding the Logic Behind Lookup

MATCH is often misunderstood, but it becomes extremely powerful once its logic is clear.

• How MATCH works

• MATCH as a standalone function

• Why learning MATCH is important

• Understanding positions and lookup logic

• Combining MATCH with VLOOKUP

• Combining MATCH with IF

• Using MATCH to solve problems that simple VLOOKUP cannot solve

IF Functions – Basic to Super Advanced

Understand the complete IF family through practical examples.

• Basic IF

• IF with multiple conditions

• IF + AND

• IF + OR

• Nested IF

• IF inside IF

• IF combined with VLOOKUP

• IF combined with MATCH

• MATCH combined with IF

• Practical business and office scenarios

• New IFS function introduced in modern Excel

• Difference between traditional nested IF and IFS

XLOOKUP – The Modern Lookup Function

Learn the powerful XLOOKUP function available in modern versions of Excel and Microsoft 365.

• Why XLOOKUP was introduced

• XLOOKUP vs VLOOKUP

• Practical XLOOKUP examples

• Understanding its advantages and flexibility

• Using XLOOKUP with other functions

• Solving lookup problems more efficiently

INDEX – A Powerful Alternative to VLOOKUP

Go beyond VLOOKUP and understand why INDEX can solve many problems that VLOOKUP cannot.

• How INDEX works

• Selecting data within INDEX

• Can you select the entire range or only specific portions?

• INDEX with MATCH

• INDEX with IFERROR

• INDEX with LEFT and other text functions

• Combining INDEX with multiple functions

• Understanding row and column parameters

• What happens when row or column parameters are left empty?

• Practical problems where INDEX provides greater flexibility than VLOOKUP

Error Handling – IFERROR & ISERROR

Learn how to handle errors intelligently instead of simply hiding them.

• What Excel errors mean

• ISERROR

• IFERROR

• Difference between ISERROR and IFERROR

• Which one should you use and when?

• Combining IFERROR with VLOOKUP

• Combining IFERROR with INDEX and MATCH

• Building multiple lookup attempts using IFERROR

• Making VLOOKUP behave like a search loop

• Using 3, 4 or even more lookup attempts

Text Functions – Extracting & Manipulating Data

Learn the most useful text functions and, more importantly, how to combine them.

• LEFT

• RIGHT

• MID

• FIND

• TEXT

• Combining text functions with INDEX, MATCH and IFERROR

• Extracting information from complex text strings

• Using FIND inside FIND

• Using multiple FIND functions

• Real-world data extraction problems

• Building complex formulas by combining simple functions

INDIRECT, ADDRESS & NAME MANAGER

Enter the world of dynamic Excel formulas.

• Introduction to INDIRECT

• Why INDIRECT is considered a highly dynamic function

• Using INDIRECT in real-world scenarios

• Introduction to ADDRESS

• Combining INDIRECT and ADDRESS

• Solving complex data-alignment problems

• Using INDIRECT in dashboards

• What is Name Manager?

• Creating and managing named ranges

• Using Name Manager with INDIRECT

• Creating simple dropdown lists

• Creating dynamic dropdown lists

• Linking one dropdown with another

• Dependent and dynamic dropdowns

• Using dynamic dropdowns in dashboards

COUNT & SUM Family Functions

Master Excel's most important counting and conditional calculation functions.

• COUNT

• COUNTA

• COUNTBLANK

• COUNTIF

• COUNTIFS

• SUMIF

• SUMIFS

• MAXIFS

• Combining these functions with VLOOKUP

• Combining VLOOKUP with SUMIF and COUNTIF

• Solving practical business problems

• Using wildcard characters * and ?

• Understanding how wildcards change the way Excel searches data

Sorting & Filtering

Learn how to control and analyze large datasets efficiently.

• Basic Filters

• Sorting data

• Filter by values

• Filter by colors

• Filter by icons

• Sort by color

• Sort by values

• Column-wise sorting

• Row-wise sorting

• Understanding when normal filters are sufficient

Advanced Filter – Deep Dive

Take filtering to an advanced level.

• What is Advanced Filter?

• Why is Advanced Filter required?

• Advanced Filter vs normal Filter

• Extracting data using criteria

• Fetching unique records

• Filter in Place

• Copying filtered results to another location

• Creating AND criteria

• Creating OR criteria

• Working with multiple headers

• Using wildcards * and ?

• Using formulas as Advanced Filter criteria

• Extracting complex data points using Advanced Filter

Conditional Formatting – Basic to Advanced

Learn how to make Excel automatically identify important information.

• Highlight cells based on values

• Highlight duplicate values

• Highlight unique values

• Highlight values occurring multiple times

• Highlight the nth occurrence of a value

• Conditional Formatting using formulas

• Creating formula-based formatting rules

• Using icons

• Understanding rule priorities

• Managing multiple Conditional Formatting rules

• Building practical, dynamic formatting solutions

Dates & Time in Excel

Understand the science behind Excel dates and time instead of simply memorizing functions.

• How Excel actually stores dates

• Are dates text, numbers or something else?

• Understanding Excel's date serial numbers

• Why dates can be added and subtracted

• Restrictions and limitations of Excel dates

• Excel's date range

• TODAY function

• NOW function

• Keyboard shortcuts for inserting date and time

• Calculating the difference between dates

• Calculating working days

• Excluding weekends

• Excluding holidays

• NETWORKDAYS and working-day calculations

• DATEDIF function

• TEXT function with dates

• Extracting date components

• Understanding time values

• HOUR

• MINUTE

• SECOND

• How Excel secretly stores time

• Adding and subtracting time values

• Understanding why time calculations sometimes appear confusing

• Practical date and time problems

Arrays – Understanding the Science Behind Excel

Move towards modern and advanced Excel concepts by understanding arrays.

• What are Arrays?

• Why do we need arrays?

• How arrays work in Excel

• Understanding the logic behind array calculations

• Working with multiple values simultaneously

• Practical array examples

• Understanding how modern Excel handles arrays

• Using arrays to solve complex data problems

Practical Projects & Assignments

This course is not limited to individual functions.

Throughout the course, we combine functions to solve real-world Excel problems.

You will work with combinations such as:

• INDEX + MATCH

• VLOOKUP + IFERROR

• VLOOKUP + SUMIF

• VLOOKUP + COUNTIF

• IF + AND + OR

• IF + VLOOKUP + MATCH

• INDEX + IFERROR

• INDEX + MATCH + IFERROR

• LEFT + INDEX + IFERROR

• FIND + FIND

• INDIRECT + ADDRESS

• INDIRECT + Name Manager

• Dynamic Dropdowns

• Advanced Filter + Criteria

• Formula-based Conditional Formatting

The objective is not just to know what a function does, but to understand how different functions can work together to solve complex problems.

Classwork & Assignments

You will also receive classwork files and practical assignments so that you can apply what you learn.

The difficulty gradually increases from fundamentals to advanced Excel problems, giving you the opportunity to test your understanding and develop confidence in solving real Excel scenarios.

By the end of the course, you will have moved beyond simply knowing Excel functions—you will understand how to combine them, when to use them, why they work, and how to approach complex Excel problems logically.