Course Overview
In today’s data-driven world, Excel is more than just a spreadsheet tool—it’s a powerful engine for analysis and decision-making.
This course is designed to help you master the most important Excel functions used in real business environments, while building the mindset required to analyze data effectively. Instead of focusing on isolated formulas, you will learn how to combine functions to solve real problems, extract insights, and support smarter decisions.
You’ll start by developing a strong foundation in logical thinking—understanding how conditions work and how decisions are structured. Then, you’ll move into applying conditional functions such as IF, AND, and OR to model real-world scenarios. From there, you’ll learn how to analyze data using COUNTIF(S) and SUMIF(S), before advancing into modern lookup techniques like XLOOKUP and INDEX-MATCH.
Throughout the course, you will work with practical examples and real business cases, helping you bridge the gap between theory and application. By the end, you’ll be able to build dynamic formulas, retrieve data efficiently, and transform raw datasets into meaningful insights.
This course is not just about learning Excel—it’s about learning how to think, analyze, and make decisions using data.
This course is designed to help you master the most important Excel functions used in real business environments, while building the mindset required to analyze data effectively. Instead of focusing on isolated formulas, you will learn how to combine functions to solve real problems, extract insights, and support smarter decisions.
You’ll start by developing a strong foundation in logical thinking—understanding how conditions work and how decisions are structured. Then, you’ll move into applying conditional functions such as IF, AND, and OR to model real-world scenarios. From there, you’ll learn how to analyze data using COUNTIF(S) and SUMIF(S), before advancing into modern lookup techniques like XLOOKUP and INDEX-MATCH.
Throughout the course, you will work with practical examples and real business cases, helping you bridge the gap between theory and application. By the end, you’ll be able to build dynamic formulas, retrieve data efficiently, and transform raw datasets into meaningful insights.
This course is not just about learning Excel—it’s about learning how to think, analyze, and make decisions using data.
What You'll Learn
Understand how formulas are used inside companies
Master relative and absolute references
Use IF, Nested IF, and IFERROR professionally
Build formulas with multiple conditions
Use VLOOKUP, XLOOKUP, and INDEX MATCH
Clean and combine data with text functions
Use date and time functions in reports
Build smarter files that save time and reduce errors
Handle formula errors professionally
Complete a final project that simulates a real business environment
Requirements
It is recommended to complete Excel Foundations for Business first
Basic knowledge of cells, worksheets, and tables
Microsoft Excel 2019 or Microsoft 365 installed
Willingness to learn how companies think about formulas
Readiness to practice on real business files
Course Content
4 sections · 27 lessons
Business Data Analysis with Conditions
8 lessons
· 1 free
Using COUNTIF to Analyze Sales Data
In this lesson, we explore how to use the COUNTIF function to analyze sales data within a structured dataset. Through a practical example, we calculate how many times each product was sold and transfo…
What you'll learn (Preview)
- How to use COUNTIF to count occurrences within a dataset
- Analyzing product sales frequency
- Creating a simple report and visualizing it using charts
Key Concept (Preview)
Turning raw sales data into a clean, summarized, and visual report using COUNTIF and basic Excel data preparation techniques.
Business Use Case (Preview)
A sales analyst needs to identify the most frequently sold products to support inventory planning and marketing decisions. By using COUNTIF, they can quickly ca…
Video locked — enroll to watch
Unlock This Lesson
Mastering COUNTIFS with Multiple Conditions
In this lesson, we take a deeper dive into the COUNTIFS function by applying multiple conditions to analyze data more precisely. Through a practical example, learners will understand how to combine cr…
What you'll learn (Preview)
- How to use COUNTIFS with multiple criteria
- Structuring and combining conditions effectively
- Analyzing data across multiple dimensions
Key Concept (Preview)
Using COUNTIFS to apply multiple conditions simultaneously for more advanced and precise data analysis.
Business Use Case (Preview)
A business analyst needs to measure performance based on multiple factors, such as product category, region, and date. Using COUNTIFS, they can filter and count…
Video locked — enroll to watch
Unlock This Lesson
Using COUNTIFS with Two-Way Lookup (Matrix Analysis)
In this lesson, we introduce the COUNTIFS function through a practical scenario. Instead of a simple one-directional example, we build a two-way lookup table that allows data to be analyzed both verti…
What you'll learn (Preview)
- How to use COUNTIFS with multiple criteria
- Building a two-dimensional (row & column) analysis table
- Structuring criteria for both vertical and horizontal dimensions
Key Concept (Preview)
Transforming raw data into a matrix-style report using COUNTIFS, where results are displayed across both rows and columns for better insights.
Business Use Case (Preview)
A reporting analyst needs to create a dynamic summary that shows performance across two dimensions—such as sales by product and region or transactions by month …
Video locked — enroll to watch
Unlock This Lesson
Using SUMIF to Summarize Sales Data
In this lesson, we introduce the SUMIF function to calculate totals based on specific conditions. Using a real-world sales dataset, learners will summarize revenue by criteria such as product or categ…
What you'll learn (Preview)
- How to use SUMIF to sum values based on a condition
- Structuring criteria for accurate aggregation
- Summarizing sales data by product or category
Key Concept (Preview)
Using SUMIF to aggregate numerical data based on defined criteria, enabling targeted and meaningful analysis.
Business Use Case (Preview)
A sales analyst needs to calculate total revenue for specific products or categories. Using SUMIF, they can quickly generate focused summaries without filtering…
Video locked — enroll to watch
Unlock This Lesson
Analyze Sales with Multiple Conditions Using SUMIFS
Sales reports can be misleading when data is not properly structured, especially when working with dates. In this lesson, we go beyond basic aggregation and solve a real-world problem where incorrect …
What you'll learn (Preview)
- How to use SUMIFS with multiple criteria including dates
- Building a dynamic sales analysis system based on user inputs
- Structuring date conditions correctly
Key Concept (Preview)
Advanced analysis requires combining multiple conditions within a single function while ensuring data is clean and properly structured.
Business Use Case (Preview)
A business analyst needs to evaluate sales performance across multiple conditions such as product, region, and date ranges. Using SUMIFS, they can build flexibl…
Video locked — enroll to watch
Unlock This Lesson
Unlocking Accurate Sales Insights with SUMIFS
Sales reports often look correct—but hidden data issues and inconsistent formats can lead to misleading totals, especially when working with dates. In this lesson, we tackle a real-world challenge where sales analysis fails due to unclean data. Then, we build a dynamic system using SUMIFS that allows users to filter and analyze sales based on multiple criteria—including dates—while ensuring accurate results through proper data cleaning techniques.
What you'll learn in this lesson
- How to use SUMIFS with date-based criteria
- Building a dynamic filtering system based on multiple conditions
- Handling date-related challenges in Excel
- Cleaning and preparing data to ensure accurate calculations
- Creating reliable sales summaries based on user-selected inputs
Key Concept
Combining SUMIFS with clean and structured data to build a dynamic, criteria-based sales analysis system that delivers accurate results.
Business Use Case
A finance or sales analyst needs to generate accurate reports based on specific time periods and conditions. However, inconsistent date formats and messy data lead to incorrect totals. By cleaning the data and applying SUMIFS, they can build a reliable reporting system that dynamically responds to selected criteria and ensures trustworthy insights.
One Function, Multiple Business Perspectives
The same number can tell completely different stories depending on how it is analyzed. In this lesson, we use a single Excel function to evaluate the same dataset from multiple business perspectives. …
What you'll learn (Preview)
- Using one function for multiple analytical perspectives
- Changing criteria to generate different insights
- Understanding the impact of business context on data interpretation
Key Concept (Preview)
A single function can generate multiple insights by adjusting criteria, enabling different business perspectives from the same dataset.
Business Use Case (Preview)
Different departments analyze the same sales data but require different insights. Management focuses on overall performance, sales teams track product-level res…
Video locked — enroll to watch
Unlock This Lesson
One Number, Five Perspectives – How Departments Think
The same number can carry different meanings depending on who is analyzing it. In this lesson, we explore how various departments interpret the same data from completely different perspectives. By app…
What you'll learn (Preview)
- How the same data serves different business needs
- Thinking from multiple departmental perspectives
- Applying logic to extract multiple insights from one dataset
Key Concept (Preview)
Data remains constant, but perspective changes. Applying different logic enables multiple insights from the same value.
Business Use Case (Preview)
A single performance metric can be interpreted differently across departments. Management evaluates overall performance, finance focuses on cost efficiency, sal…
Video locked — enroll to watch
Unlock This Lesson
Logic & Decision Making (IF Thinking)
10 lessons
· 1 free
The Secret Behind Every IF Formula – Think Logic
Before writing any IF formula, there is a deeper layer that most learners overlook. In this lesson, we focus on building logical thinking—how to translate real-world rules into structured conditions t…
What you'll learn (Preview)
- Structuring conditions based on real-world scenarios
- Understanding how Excel evaluates logical expressions
- Translating business rules into structured logic
Key Concept (Preview)
Strong logical thinking is the foundation of every decision-based formula, enabling you to build solutions instead of memorizing them.
Business Use Case (Preview)
In business environments, decisions rely on rules such as approvals, classifications, and validations. Professionals who can convert these rules into structured…
Video locked — enroll to watch
Unlock This Lesson
What Can IF Return? Exploring Different Value Types
Most learners think of IF as a simple decision tool—but its real power lies in what it can return. In this lesson, we explore how IF can produce different types of outputs depending on the scenario, allowing you to build more flexible and dynamic solutions. You’ll learn how to go beyond basic results and start designing formulas that adapt to real business needs.
What you'll learn in this lesson
- How IF can return different types of values (text, numbers, and more)
- Structuring outputs based on different conditions
- Designing flexible formulas for multiple scenarios
- Adapting results to match business requirements
- Moving from fixed outputs to dynamic responses
Key Concept
The power of IF is not just in the condition—but in the flexibility of its output, enabling dynamic and context-driven results.
Business Use Case
In business reporting, outputs are rarely one-size-fits-all. A manager may need labels, numeric indicators, or status messages depending on the situation. Using IF, analysts can create dynamic outputs that adjust based on conditions—making reports clearer, more actionable, and tailored to different stakeholders.
From TRUE/FALSE to Real Decisions in Excel
Understanding logical results is only the beginning. In this lesson, we move beyond simple evaluations and show how these outcomes can be transformed into real business decisions. You’ll learn how to …
What you'll learn (Preview)
- How to convert logical outcomes into actionable decisions
- Structuring formulas that reflect real business rules
- Bridging the gap between logic and practical application
Key Concept (Preview)
Logical results become valuable only when they are translated into clear, actionable decisions within your analysis.
Business Use Case (Preview)
In business environments, data alone is not enough—decisions are what matter. Whether it’s approving requests, classifying performance, or triggering actions, p…
Video locked — enroll to watch
Unlock This Lesson
From IF Logic to Data Insights with COUNTIF
After understanding how to build logical conditions using IF, the next step is turning that logic into meaningful data insights. In this lesson, we shift from decision-making to analysis by using COUN…
What you'll learn (Preview)
- How to transition from IF-based logic to data analysis
- Using COUNTIF to measure occurrences based on conditions
- Applying logical thinking to large datasets
Key Concept (Preview)
The same logic used in IF can be scaled to analyze entire datasets using COUNTIF, transforming decisions into measurable insights.
Business Use Case (Preview)
A business analyst may define rules to evaluate individual records, but decision-makers need a broader view. By applying those same conditions with COUNTIF, the…
Video locked — enroll to watch
Unlock This Lesson
Nested IF In-Depth – Build Complex Logic
As business rules become more complex, a single condition is no longer enough. In this lesson, we dive deep into nested IF to handle multiple scenarios within a single formula. You’ll learn how to str…
What you'll learn (Preview)
- How to build and structure nested IF formulas
- Handling multiple conditions within one formula
- Organizing complex logic in a readable way
Key Concept (Preview)
Complex decisions can be built by layering simple conditions—structured correctly, nested IF turns multiple rules into one controlled formula.
Business Use Case (Preview)
In real-world scenarios, decisions often involve multiple layers—such as performance grading, pricing tiers, or approval levels. Using nested IF, analysts can t…
Video locked — enroll to watch
Unlock This Lesson
Logical Functions That Return TRUE or FALSE
Behind every decision in Excel lies a simple evaluation process. In this lesson, we explore the core logical functions that power this process—such as AND, OR, and NOT—and how they help structure cond…
What you'll learn (Preview)
- Understanding how logical functions evaluate conditions
- Using AND to enforce multiple requirements
- Using OR to allow flexible conditions
Key Concept (Preview)
Logical functions allow you to control how conditions are evaluated, enabling more flexible and accurate decision-making.
Business Use Case (Preview)
In business processes, decisions rarely depend on a single condition. For example, approvals may require multiple criteria to be met, or exceptions may apply un…
Video locked — enroll to watch
Unlock This Lesson
Using AND and OR to Build Smarter IF Logic
A single condition is rarely enough in real-world scenarios. In this lesson, we enhance the power of IF by combining it with AND and OR to handle multiple conditions more intelligently. You’ll learn h…
What you'll learn (Preview)
- Combining IF with AND for strict conditions
- Using OR to allow flexible decision paths
- Structuring multi-condition logic clearly
Key Concept (Preview)
By combining IF with AND and OR, you can control how multiple conditions interact—creating smarter and more flexible decision-making formulas.
Business Use Case (Preview)
In business environments, decisions often depend on multiple factors. For example, a transaction may be approved only if several conditions are met, or flagged …
Video locked — enroll to watch
Unlock This Lesson
Evaluate Employee Attendance with IF
Employee attendance is more than just numbers—it reflects discipline, consistency, and reliability. In this lesson, we use the IF function to evaluate attendance records and classify employees based o…
What you'll learn (Preview)
- How to use IF to evaluate attendance data
- Setting clear criteria for classification
- Converting percentages into meaningful categories
Key Concept (Preview)
Using IF to transform attendance data into clear classifications that support performance evaluation.
Business Use Case (Preview)
HR teams often need to monitor attendance and identify patterns such as consistent presence or frequent absence. By applying IF, they can automatically classify…
Video locked — enroll to watch
Unlock This Lesson
Identify High Performers Using Multiple Conditions (IF + AND)
Performance evaluation becomes more meaningful when it is based on multiple factors—not just a single metric. In this lesson, we use IF combined with AND to identify high-performing employees based on…
What you'll learn (Preview)
- Combining IF with AND to evaluate multiple conditions
- Defining clear criteria for high performance
- Structuring multi-condition logic for accurate classification
Key Concept (Preview)
High-quality decisions are built on multiple conditions—using IF + AND ensures that all required criteria are met before assigning a result.
Business Use Case (Preview)
HR and management teams often define high performers based on several factors, such as attendance, productivity, and behavior. By combining these criteria using…
Video locked — enroll to watch
Unlock This Lesson
Determine Bonus Eligibility Using IF, AND, and OR
Bonus decisions are rarely based on a single factor—they depend on a combination of performance, consistency, and specific business rules. In this lesson, we use IF along with AND and OR to build a fl…
What you'll learn (Preview)
- Combining IF, AND, and OR to handle complex conditions
- Designing flexible rules for bonus eligibility
- Structuring logic that balances strict and flexible criteria
Key Concept (Preview)
Effective decision-making often requires both strict conditions and flexible alternatives—combining AND and OR within IF allows you to model these real-world ru…
Business Use Case (Preview)
Organizations define bonus eligibility based on multiple criteria, such as performance metrics, attendance, and special achievements. Some conditions must all b…
Video locked — enroll to watch
Unlock This Lesson
Mastering Lookup Functions in Excel
8 lessons
· 1 free
Introduction to Lookup Functions
Modern data analysis is not just about working within a single table—it’s about connecting data across multiple sources. In this lesson, we introduce lookup functions and their role in retrieving data…
What you'll learn (Preview)
- What lookup functions are and why they matter
- The concept of retrieving data from another table
- Understanding relationships between datasets
Key Concept (Preview)
Lookup functions connect datasets and allow you to retrieve precise information—turning separate tables into a unified data model.
Business Use Case (Preview)
In business environments, data is often distributed across multiple tables such as customers, products, and transactions. Lookup functions allow analysts to con…
Video locked — enroll to watch
Unlock This Lesson
Master Exact Match Lookups in Excel
Accuracy is critical when retrieving data—especially when working with identifiers like IDs, codes, or exact names. In this lesson, we focus on exact match lookups and how to ensure your formulas retu…
What you'll learn (Preview)
- Understanding the concept of exact match in lookup functions
- When to use exact match vs other matching types
- Ensuring accurate data retrieval using lookup functions
Key Concept (Preview)
Exact match ensures that lookup functions return only perfectly matching results—making it essential for accurate and trustworthy analysis.
Business Use Case (Preview)
In business environments, many lookups depend on exact identifiers such as product codes, employee IDs, or transaction numbers. Using exact match ensures that t…
Video locked — enroll to watch
Unlock This Lesson
How Approximate Match Works in VLOOKUP
Approximate match in VLOOKUP is powerful—but often misunderstood. In this lesson, we break down how it actually works and why it can return unexpected results if not used correctly. You’ll learn the l…
What you'll learn (Preview)
- How approximate match works in VLOOKUP
- The importance of sorted data for correct results
- When to use approximate match vs exact match
Key Concept (Preview)
Approximate match returns the closest valid result based on data structure—making sorting and proper setup essential for accuracy.
Business Use Case (Preview)
In business scenarios such as pricing tiers, tax brackets, or discount levels, exact matches are not always required. Instead, values fall within ranges. Using …
Video locked — enroll to watch
Unlock This Lesson
Stop Using Fixed Column Numbers in VLOOKUP
Hardcoding column index numbers in VLOOKUP may work at first—but it quickly becomes a problem as your data evolves. In this lesson, we replace fixed column numbers with dynamic formulas, making your lookups more flexible and reliable. You’ll also learn how to correctly lock references to ensure your formulas behave consistently when copied across your dataset.
What you'll learn in this lesson
- Why fixed column index numbers can break your formulas
- How to replace static values with dynamic calculations
- Using functions to generate column index numbers automatically
- Applying correct reference locking for consistent results
- Building scalable and error-resistant lookup formulas
Key Concept
Dynamic formulas adapt to changes—replacing fixed column numbers with calculated references makes your lookups more robust and future-proof.
Business Use Case
In real-world reporting, data structures often change—columns are added, removed, or rearranged. If column numbers are hardcoded, lookup formulas can return incorrect results without warning. By using dynamic column indexing and proper reference locking, analysts can build flexible systems that remain accurate even as the dataset evolves.
XLOOKUP – The Modern Replacement for VLOOKUP and HLOOKUP
Traditional lookup functions like VLOOKUP and HLOOKUP come with limitations that can slow down analysis and introduce errors. In this lesson, we introduce XLOOKUP as a modern, more powerful alternativ…
What you'll learn (Preview)
- How XLOOKUP improves on traditional lookup functions
- Performing vertical and horizontal lookups with a single function
- Writing simpler and more flexible lookup formulas
Key Concept (Preview)
XLOOKUP unifies and enhances lookup functionality—eliminating common limitations and enabling more flexible, accurate data retrieval.
Business Use Case (Preview)
In modern business environments, analysts work with dynamic datasets that require flexible and reliable lookup solutions. By using XLOOKUP, they can streamline …
Video locked — enroll to watch
Unlock This Lesson
Return Multiple Values with XLOOKUP
Lookups are often assumed to return a single value—but modern Excel allows you to retrieve more. In this lesson, we explore how XLOOKUP can return multiple values at once, enabling richer and more dyn…
What you'll learn (Preview)
- How XLOOKUP can return multiple values, not just one
- Retrieving entire rows or multiple columns in a single formula
- Structuring lookup results for better analysis
Key Concept (Preview)
Modern lookup functions can return more than a single result—XLOOKUP enables multi-value outputs, making data retrieval more powerful and efficient.
Business Use Case (Preview)
In real-world scenarios, retrieving one value is often not enough. For example, when looking up a customer or product, analysts may need multiple details such a…
Video locked — enroll to watch
Unlock This Lesson
Stop Returning Unnecessary Data – Use XLOOKUP with CHOOSECOLS
In real-world scenarios, you don’t always need all the data—you need the right data. In this lesson, we combine XLOOKUP with CHOOSECOLS to return only selected columns from a dataset. This approach gi…
What you'll learn (Preview)
- Combining XLOOKUP with CHOOSECOLS for selective retrieval
- Returning specific columns instead of entire records
- Controlling output structure without changing source data
Key Concept (Preview)
Data retrieval should be flexible—by selecting only the needed columns, you can create more efficient and targeted outputs.
Business Use Case (Preview)
In business reporting, different stakeholders require different pieces of information. For example, a manager may only need customer names and ratings, while an…
Video locked — enroll to watch
Unlock This Lesson
Master Two-Way Lookup with INDEX and MATCH
Retrieving data based on a single condition is often not enough. In this lesson, we take lookup techniques to the next level by performing a two-way lookup using INDEX and MATCH. You’ll learn how to l…
What you'll learn (Preview)
- Understanding the concept of two-way lookup
- Using MATCH to locate both row and column positions
- Combining INDEX with MATCH for precise data retrieval
Key Concept (Preview)
By combining INDEX and MATCH, you can perform lookups across both rows and columns—enabling dynamic and precise data retrieval from any table.
Business Use Case (Preview)
In real-world reporting, data is often structured in matrix format—such as sales by product and region or performance by department and time period. Using INDEX…
Video locked — enroll to watch
Unlock This Lesson
Final Business Case Study
1 lessons
Final Excel Case Study – All-in-One Challenge
This is where everything comes together. In this final case study, you’ll apply multiple Excel functions in one comprehensive scenario that simulates real-world analysis. Instead of solving isolated p…
What you'll learn (Preview)
- Combining multiple functions within a single solution
- Applying lookup, conditional, and logical functions together
- Structuring a complete analysis workflow
Key Concept (Preview)
Real-world analysis is not about using one function—it’s about combining multiple tools to solve a complete problem.
Business Use Case (Preview)
In business environments, analysts rarely work with isolated tasks. They are expected to clean data, retrieve information, apply conditions, and generate insigh…
Video locked — enroll to watch
Unlock This LessonYour Instructor
Ashraf Elsheikh
Founder & CEO of Elite Training & Consulting
3 Courses
251 Learners
4.9 Rating
Ashraf Elsheikh is the Founder & CEO of Elite Training & Consulting with more than 20 years of experience in training employees inside companies on Excel, Data Analysis, Power BI, Power Query, and AI. Over the years, he has helped professionals improve the way they think about reports, dashboards, business workflows, and decision-making using data.
View Instructor ProfileFrequently Asked Questions
Do I need prior experience in Excel to take this course?
No prior experience is required. The course starts with the fundamentals of logical thinking and gradually builds up to more advanced techniques, making it suitable for beginners and intermediate users alike.
Is this course suitable for real business scenarios?
Yes. The course is designed around real-world use cases. You’ll learn how to apply Excel functions to solve practical problems and support business decision-making.
Will I learn only Excel functions or full analysis skills?
You will learn both. While the course focuses on Excel functions, the main goal is to help you think like a data analyst—combining functions to extract insights and make decisions.
What makes this course different from other Excel courses?
Most courses focus on explaining functions individually. This course focuses on how to use them together in real scenarios, building a mindset that goes beyond memorization.
Will this help me prepare for data analysis roles?
Absolutely. The course builds a strong foundation in logic, data handling, and analysis—all of which are essential for data-related roles.
Are there practical exercises or case studies?
Yes. The course includes hands-on examples and a final comprehensive case study that combines multiple functions in a real-world scenario.
Do I need a specific version of Excel?
It’s recommended to use a recent version to access modern functions like XLOOKUP. However, most concepts can still be applied in earlier versions.
How will this course improve my work performance?
You’ll be able to automate tasks, analyze data faster, reduce errors, and make more informed decisions—saving time and increasing your value in any role.
Is this course theoretical or practical?
It is highly practical. Every concept is explained through real examples and applied scenarios to ensure you can use it immediately in your work.
What will I be able to do after completing this course?
You’ll be able to analyze data efficiently, build dynamic reports, perform advanced lookups, and create decision-based formulas that reflect real business logic.
Do I need to be good at Excel before taking this course?
It is recommended to complete Excel basics first or have simple knowledge of worksheets and cells.
Will I learn VLOOKUP and XLOOKUP?
Yes, the course covers VLOOKUP, XLOOKUP, and INDEX MATCH with real business examples.
Is the course practical or theoretical?
The course is fully practical and based on real company scenarios and files.
Will I receive a certificate?
Yes, you will receive a certificate after completing all required lessons.
Can I watch the course more than once?
Yes, you get lifetime access and can watch the lessons anytime.
Will I be able to build professional files after this course?
Yes, after the course you will be able to build smart professional files and reports using advanced formulas and functions.
