Course Content
Topic 1: Advanced Excel Formulas & Functions
This topic covers advanced Excel formulas and functions, including logical, lookup, text, and date/time functions, as well as combining multiple formulas. By mastering these, you can perform complex calculations, retrieve data efficiently, and automate tasks in Excel.
0/12
Lesson 1.1 – Introduction to Advanced Formulas & Functions
Lesson 1.2 – Logical Functions (IF, AND, OR, IFERROR)
Lesson 1.3 – Nested IF Statements
Lesson 1.4 – Conditional Functions (SUMIF, COUNTIF, AVERAGEIF)
Lesson 1.5 – Advanced Conditional Functions (SUMIFS, COUNTIFS, AVERAGEIFS)
Lesson 1.6 – Date & Time Functions (TODAY, NOW, DATEDIF, EOMONTH, NETWORKDAYS)
Lesson 1.7 – Financial Functions (PMT, FV, NPV, IRR)
Lesson 1.8 – Lookup Functions (VLOOKUP, HLOOKUP, XLOOKUP, INDEX, MATCH)
Lesson 1.9 – Text Functions (LEFT, RIGHT, MID, LEN, TRIM, PROPER, UPPER, LOWER, CONCAT, TEXTJOIN)
Lesson 1.10 – Array Formulas & Dynamic Arrays (FILTER, SORT, UNIQUE, SEQUENCE)
Lesson 1.11 – Advanced Error Handling Functions (IFERROR, ISERROR, ISNA, ERROR.TYPE)
Lesson 1.12 – Practical Examples & Exercises
Topic 2: Data Analysis & Pivot Tables
This topic covers essential Excel tools for analyzing and summarizing large datasets effectively. You will learn sorting, filtering, data cleaning, and subtotal techniques to prepare data for analysis. The topic also introduces Pivot Tables and Pivot Charts—powerful features to summarize, group, and visualize data for better decision-making. By the end, you’ll be able to create dynamic, interactive reports from raw data with ease.
0/12
Lesson 2.1 – Introduction to Data Analysis in Excel
Lesson 2.2 – Sorting Data in Excel
Lesson 2.3 – Filtering Data in Excel
Lesson 2.4 – Data Validation & Drop-down Lists
Lesson 2.5 – Subtotal Feature in Excel
Lesson 2.6 – Data Cleaning Tools in Excel
Lesson 2.7 – Introduction to Pivot Tables in Excel
Lesson 2.8 – Creating Pivot Tables in Excel
Lesson 2.9 – Pivot Table Field Settings in Excel
Lesson 2.10 – Grouping in Pivot Tables in Excel
Lesson 2.11 – Pivot Table Formatting in Excel
Lesson 2.12 – Pivot Charts & Practical Exercises
Topic 3: Advanced Data Visualization in Excel
Topic Overview Advanced Data Visualization in Excel focuses on creating insightful and interactive charts to present data effectively. Students will learn how to use dynamic charts, combo charts, sparklines, conditional formatting, and dashboard techniques to make reports visually appealing and easy to understand. Key Learning Points: Understand different types of charts and when to use them Learn to create dynamic and interactive charts Apply conditional formatting for visual insights Use sparklines for compact trend visualization Build dashboard components for presenting multiple data metrics Outcome: By the end of this topic, students will be able to turn raw data into professional, interactive, and visually appealing Excel reports that support decision-making.
0/6
Lesson 3.1 – Overview of Advanced Charts in Excel
Lesson 3.2 – Creating Combo Charts in Excel
Lesson 3.2 – Creating Combo Charts in Excel
Lesson 3.3 – Waterfall & Funnel Charts in Excel
Lesson 3.4 – Sparklines & Conditional Formatting in Excel
Lesson 3.5 – Dashboard Techniques & Practical Exercises in Excel
Topic 4: Advanced Excel Functions for Data Modeling
Topic Overview Advanced Excel Functions for Data Modeling focus on complex formulas, lookup techniques, dynamic calculations, and scenario analysis. This topic equips students with the ability to analyze, model, and predict data efficiently. Key Learning Points: Master advanced lookup and reference functions (INDEX, MATCH, XLOOKUP) Work with dynamic arrays and array formulas Apply logical, statistical, and financial functions for modeling Perform what-if analysis using Goal Seek and Scenario Manager Build interactive models for data-driven decision-making Outcome: By the end of this topic, students will be able to create robust Excel models for finance, sales, operations, or any analytical task, enhancing their professional skill set.
0/5
Lesson 4.1 – Advanced Lookup & Reference Functions in Excel
Lesson 4.2 – Advanced Logical & Statistical Functions in Excel
Lesson 4.3 – Date & Time Functions for Data Modeling in Excel
Lesson 4.4 – Financial Functions for Data Modeling in Excel
Lesson 4.5 – Scenario Analysis & What-If Modeling in Excel
Topic 5: Advanced Dashboard & Reporting Techniques
Topic Overview Advanced Dashboard & Reporting Techniques teach students how to design interactive, visually appealing, and professional dashboards for data-driven decision-making. This topic focuses on combining charts, pivot tables, conditional formatting, slicers, sparklines, and dynamic ranges into dashboards suitable for management reporting. Key Learning Points: Create interactive dashboards using Pivot Tables, Pivot Charts, and Slicers Use dynamic charts, sparklines, and conditional formatting for insights Implement advanced chart types like Waterfall, Combo, and Funnel Charts Learn dashboard layout best practices for clarity and readability Automate reporting with dynamic ranges and linked data Outcome: By the end of this topic, students will be able to build fully functional, professional dashboards that summarize large datasets and allow quick analysis for business decisions.
0/5
Lesson 5.1 – Interactive Dashboards using Pivot Tables & Charts
Lesson 5.2 – Advanced Charting Techniques for Dashboards
Lesson 5.3 – Conditional Formatting & Sparklines in Dashboards
Lesson 5.4 – Dynamic Dashboard Design & Layout Best Practices
Lesson 5.5 – Dashboard Automation & Reporting
Topic 6: Data Validation, Protection & Collaboration
Topic Overview Data Validation, Protection, and Collaboration techniques are crucial for ensuring accuracy, security, and teamwork in Excel projects. This topic teaches students how to restrict inputs, prevent errors, protect sensitive data, and collaborate efficiently in shared workbooks. Key Learning Points: Implement data validation rules to maintain data integrity Protect worksheets and workbooks with passwords and permissions Use shared workbooks and co-authoring for team collaboration Track changes and manage versions to prevent data conflicts Combine validation and protection for robust, error-free dashboards Outcome: By the end of this topic, students will be able to secure Excel workbooks, enforce data rules, and collaborate effectively while maintaining data integrity and accuracy.
0/5
Lesson 6.1 – Data Validation Rules & Techniques
Lesson 6.2 – Worksheet & Workbook Protection
Lesson 6.3 – Sharing & Collaboration in Excel
Lesson 6.4 – Protecting Shared Workbooks & Version Control
Lesson 6.5 — Combining Data Validation, Protection & Collaboration for Error-Free Workbooks
Topic 7: Advanced Excel Tools & Add-ins
Topic Overview Advanced Excel Tools & Add-ins enhance your productivity and analytical capabilities. This topic teaches students how to leverage Power Query, Power Pivot, Power BI integration, and useful Excel add-ins to perform advanced data analysis and automation. Key Learning Points: Import, clean, and transform data efficiently using Power Query Analyze large datasets using Power Pivot and Data Models Use Excel Add-ins to extend functionality Integrate Excel with Power BI for advanced reporting Automate repetitive tasks with Macros and VBA add-ins Outcome: By the end of this topic, students will be able to handle large datasets, automate tasks, and build advanced analytics dashboards using Excel’s powerful tools and add-ins.
0/5
Lesson 7.1 – Power Query: Data Import & Transformation
Lesson 7.2 – Power Pivot & Data Modeling
Lesson 7.3 – Excel Add-ins for Productivity & Analysis
Lesson 7.4 – Power BI Integration with Excel
Lesson 7.5 – Automating Tasks with Macros & VBA
Topic 8: Dashboard & Data Visualization
Topic Overview: Dashboards and Data Visualization are critical for turning raw data into actionable insights. This topic teaches students how to design professional dashboards, use advanced charts, slicers, and interactive elements to make data easy to interpret. Key Learning Points: Principles of effective dashboard design Creating interactive charts and visuals Using Slicers, Timelines, and Pivot Charts Combining multiple data sources for consolidated dashboards Best practices for layout, formatting, and user experience Outcome: By the end of this topic, students will be able to create interactive, insightful, and visually appealing dashboards that can be used in reports, presentations, and business analysis.
0/5
Lesson 8.1 – Dashboard Design Principles
Lesson 8.2 – Creating Interactive Charts & Pivot Charts
Lesson 8.3 – Combining Multiple Charts into a Dashboard
Lesson 8.4 – Advanced Dashboard Techniques (Slicers, Timelines, KPI Indicators)
Lesson 8.5 – Final Dashboard Project & Practice
Final Test – Comprehensive Assessment
The Final Test evaluates students on all 8 topics of the course. It ensures learners have understood advanced Excel formulas, data analysis, visualization, dashboards, Power Query, Power Pivot, macros, and add-ins. Covers theoretical knowledge and practical application. Designed for students to demonstrate their ability to create dashboards, use advanced formulas, and analyze data effectively. Passing marks: 70% or above. Students who pass get access to certificate download.
0/1
Final Test: Assess Your Excel & Dashboard Mastery
Advanced Microsoft Excel for Data Analysis & Dashboards

Lesson 3.3 – Waterfall & Funnel Charts in Excel

Lesson Overview

Waterfall and Funnel charts are advanced visualization tools that help analyze processes, trends, and stage-based data. They provide insights that are hard to see in traditional charts.

1. Waterfall Chart

1.1 What is a Waterfall Chart?

  • Visualizes the cumulative effect of sequential data, showing how an initial value is affected by positive or negative changes.

  • Commonly used for financial analysis, profit/loss, revenue tracking, or budget changes.

Example: Show how total revenue changes through various expense categories to reach net profit.

1.2 Steps to Create a Waterfall Chart

  1. Prepare your dataset: Include categories and values (positive/negative).

  2. Select the dataset.

  3. Go to Insert → Waterfall or Stock Chart → Waterfall.

  4. Excel automatically differentiates increase (green), decrease (red), total (blue).

1.3 Customizing Waterfall Charts

  • Set Totals: Right-click a column → Set as Total

  • Change Colors: Click column → Format → Fill color

  • Add Data Labels: Chart Elements → Data Labels → Show values

  • Adjust Axes: Right-click axis → Format Axis → Set min/max

1.4 Use Cases

  • Profit and Loss Analysis

  • Budget Tracking: Expenses reducing total budget

  • Sales Analysis: Show contribution of different regions/products

2. Funnel Chart

2.1 What is a Funnel Chart?

  • Shows progression of data through stages of a process.

  • Narrowing visual indicates drop-off between stages.

  • Widely used in sales, marketing, recruitment, or project pipelines.

Example: Sales funnel showing leads → qualified leads → proposals → closed deals

2.2 Steps to Create a Funnel Chart

  1. Prepare your dataset with stages and values.

  2. Select the dataset.

  3. Go to Insert → Other Charts → Funnel.

  4. Excel generates a descending chart representing each stage.

2.3 Customizing Funnel Charts

  • Add Data Labels: Show numbers/percentages for each stage

  • Change Colors: Format → Shape Fill → Choose distinct colors for stages

  • Sort Stages: Ensure stages are in correct order (largest to smallest or vice versa)

2.4 Use Cases

  • Track conversion rates in sales or marketing funnels

  • Recruitment pipeline visualization

  • Project completion stages visualization

3. Tips for Effective Waterfall & Funnel Charts

  • Label all stages clearly

  • Use distinct colors for increases, decreases, and totals

  • Avoid clutter and unnecessary gridlines

  • Combine with Pivot Tables for dynamic updates

4. Real-Life Examples

  • Waterfall: Company starts with revenue, subtracts costs, adds income from other sources → Net profit

  • Funnel: Marketing campaign leads → Contacted leads → Qualified leads → Sales

Practice Questions

  1. What is the main difference between a Waterfall chart and a Funnel chart?

  2. How do you set a total column in a Waterfall chart?

  3. When should you use a Funnel chart?

  4. How can you customize colors in Waterfall or Funnel charts?

  5. Give one real-life scenario where a Waterfall chart is better than a Column chart.

Previous
Next
Scroll to Top