Learn, Practice & Master Excel with Real-World Applications

Live + Recorded Classes | Hands-On Projects | Practical Business Applications

Course Outline

Week 1: Excel Foundations – Building Confidence

Day 1: Introduction to Excel & Getting Started
Overview of Excel interface and workbook basics.
Ribbon, tabs, and navigation tools explained.

Day 2: Mastering Excel Formatting Tools
Cell formatting, styles, borders, number formats, and fill options.

Day 3: Understanding Cell References & Basic Functions
Relative, Absolute, and Mixed references. SUM, AVERAGE, COUNT, MIN, MAX.

Day 4: Practice Session – Basic Functions & Formatting
Hands-on guided session applying Days 1–3 content.

Week 2: Essential Tools & Functional Mastery

Day 5: Deep Dive into the Excel Home Tab
Clipboard, font, alignment, number, styles, cells, and editing tools.

Day 6: Classwork – Home Tab Applications
Practical tasks using features from the Home Tab.

Day 7: Data Summarization Using Functions
Using functions like SUMIF, COUNTIF, AVERAGEIF, SUBTOTAL, and AGGREGATE.

Day 8: Classwork – Summarizing Data Practice
Hands-on exercises solving real-world data summary problems.

Day 9: Page Setup & Print Settings in Excel
Setting print area, adjusting margins, headers/footers, and print titles.

Week 3: Functions, Logic & Lookup Power

Day 10: Logical & Information Functions Explained
Using IF, AND, OR, IFS, ISERROR, IFERROR, and SWITCH.

Day 11: Classwork – Logical Function Exercises
Solving logical condition-based problems with functions.

Day 12: Lookup & Reference Functions
VLOOKUP, HLOOKUP, XLOOKUP, ROWS, COLUMN, SEQUENCE, INDEX, MATCH, and dynamic references.

Day 13: Classwork – Lookup & Reference Applications
Apply lookup functions in real datasets.

Day 14: Named Ranges, Dynamic Ranges & Data Validation
Create named ranges, apply validation rules, and set dynamic ranges.

Day 15: Advanced Lookup Techniques and Dynamic Linking
Use of INDIRECT, HYPERLINK, FILTER, UNIQUE, and SORT functions.

Week 4: Data Transformation & Business Application

Day 16: Date & Time Functions in Excel
WORKDAY, NETWORKDAYS, TODAY, NOW, EDATE, DATEDIF, YEAR, MONTH Etc.

Day 17: Text Functions for Data Manipulation
Use LEFT, RIGHT, MID, TRIM, SUBSTITUTE, SEARCH, TEXTJOIN Etc.

Day 18: Financial Functions for Real-world Calculations
PMT, RATE, NPV, FV, IRR with EMI, loan, and investment examples.

Day 19: Data Analysis with PivotTables
Create PivotTables, apply filters, sort, group, and analyze data.

Day 20: Classwork – PivotTable Analysis Practice
Build reports using PivotTables with real data.

Day 21: Exploring ‘Show Values As’ in PivotTables
% of Total, Difference From, Running Total, Rank, Index, etc.

Week 5: Visualization & Dashboarding

Day 22: Creating and Formatting Charts in Excel
Column, Line, Pie, Combo, and advanced chart formatting.

Day 23: Introduction to Excel Dashboards
Principles of dashboard design and layout.

Day 24: Interactive Dashboard Elements & Form Controls
Use of form controls like drop-downs, scroll bars, and checkboxes.

Day 25: Building Dashboards Using Functions and Options
Combine charts, form controls, and functions into a dynamic dashboard.

Day 26: Advanced Conditional Formatting Techniques
Formula-based formatting and data bars, color scales, icon sets.

Day 27: MCQ Practice – Formula and Function Review
Quiz-based class reviewing all functions and concepts learned.

Week 6: Data Modeling, Power Tools & Automation

Day 28: Introduction to Power Pivot & Data Model Design
Creating data models, relationships, and Power Pivot basics.

Day 29: DAX Basics: Measures, Calculated Columns & Filter Context
Learn basic DAX functions and how filter context works.

Day 30: Introduction to Power Query: What, Why & When
Overview of Power Query, benefits, and use cases.

Day 31: Classwork – Data Modeling & Query Recap
Combine Power Query and Power Pivot to build a model.

Day 32: Data Cleaning Using Power Query Editor
Remove duplicates, errors, blanks, and transform columns.

Day 33: Combining Data from Sheets, Workbooks, and Folders
Append and merge queries across multiple sources.

Day 34: Classwork – Power Query Practice
Real-world exercises applying Power Query steps.

Day 35: Power Query Calculations and Transformations
Create custom columns, extract data, and use M code basics.

Day 36: Creating Reusable Power Query Templates for Automation
Creating parameterized queries, reusable templates, and dynamic folder queries.

Week 7: Excel VBA & Automation

Day 37: Recording Macros to Automate Tasks
Learn to record, save, and run macros for automation.

Day 38: Understanding VBA: Concepts & Editor Tour
Tour of the VBA Editor, writing procedures, using MsgBox/InputBox.

Day 39: Final Classwork – VBA & Project Integration Practice
Build an automated project combining all features learned.

What You’ll Achieve by the End of This Course

• Master Excel from beginner to expert level.
• Build real-world projects for business and personal use.
• Create dashboards, automate tasks, and analyze large datasets.
• Learn Power Query, Power Pivot, DAX, and VBA essentials.
• Boost job readiness and productivity with Excel.

📞 Join Excel Point Nepal today and transform your Excel skills!