Advanced Excel Professional
About Course
This all-in-one Excel course takes you from essential foundations to advanced business automation. Learn practical skills in Excel formatting, functions, PivotTables, dashboards, Power Query, Power Pivot, and VBA. Perfect for professionals, accountants, and data enthusiasts seeking to boost productivity, automate tasks, and build real-world reports. Delivered via recorded lessons with hands-on classwork and projects.
What Will You Learn?
- Excel formatting, functions & analysis tools
- PivotTables, Charts & Interactive Dashboards
- Power Query & Power Pivot integration
- DAX basics for advanced modeling
- Automate repetitive tasks using VBA
- Build business-ready Excel projects
- Clean, combine, and transform raw data
- Prepare reports, summaries, and dynamic templates
Course Content
Section 1: Getting Started with Excel
-
What is Excel? Introduction and Use Cases
-
Exploring the Excel Interface (Ribbon, Tabs, Quick Access)
-
Understanding Workbooks vs Worksheets
-
Navigating and Selecting Cells, Ranges, Rows & Columns
Section 2: Managing Files & Workbooks
-
Saving, Opening, and Managing Excel Files
-
Using Zoom, Split, Freeze Panes for Navigation
-
Excel View Modes and Sheet Management
Section 3: Working with Worksheets
-
Creating, Renaming, and Deleting Worksheets
-
Undo, Redo, Repeat & Clipboard Features
-
Entering and Editing Data Effectively
-
Autofill, Flash Fill and Series Fill Techniques
Section 4: Excel Templates & Protection
-
Introduction to Excel Templates
-
Workbook Protection and Sheet Locking Basics
-
Keyboard Shortcuts for Productivity
Section 5: Cell Formatting & Styles
-
Cell Formatting: Font, Alignment, Number, Border, Fill
-
Format Painter, Clear Formats, Custom Number Formats
-
Applying Cell Styles, Themes, and Table Formatting
-
Applying Table Formats and Total Row
-
Practice: Creating a Personal Budget Sheet
Section 6: Cell References & Basic Functions
-
Understanding Relative, Absolute, and Mixed References
-
Introduction to Formulas and Functions
-
Using SUM, AVERAGE, MIN, MAX Functions
-
COUNT, COUNTA, COUNTBLANK – When to Use Each
-
AutoSum and Quick Calculation Options
Section 7: Data Validation & Cleaning
-
Named Ranges – How and Why to Use Them
-
Data Entry Rules using Data Validation
-
Using Drop-down Lists and Input Messages
-
Removing Duplicates and Using Text to Columns
-
Sorting & Filtering Basics
Section 8: Conditional Formatting
-
Conditional Formatting Introduction
-
Highlighting Cells Based on Conditions
-
Working with Custom Rules in Conditional Formatting
-
Using Icons, Color Scales, and Data Bars
Section 9: Logical Functions
-
Introduction to Logical Functions – IF, AND, OR
-
Nested IF Statements
-
Using IFS Function
-
Using NOT and XOR Logic
-
IFERROR vs IFNA – Error Handling Explained
Section 10: Information Functions & Lookup Basics
-
ISBLANK, ISNUMBER, ISTEXT, ISERROR Functions
-
Introduction to Lookup Functions
-
VLOOKUP – Basics and Limitations
-
HLOOKUP Explained
-
Practice: Product Price Sheet with Lookups
Section 11: Advanced Lookup & Dynamic Functions
-
INDEX and MATCH Combination
-
XLOOKUP – Syntax and Use Cases
-
LOOKUP, CHOOSE and INDIRECT
-
ROW, COLUMN, ADDRESS Functions
-
SEQUENCE and RANDARRAY Dynamic Arrays
-
Using FILTER, SORT, UNIQUE
Section 12: Linking, Referencing & Auditing
-
INDIRECT and Dynamic Referencing
-
Working with Hyperlinks and Cell Linking
-
Formula Auditing and Trace Dependents
-
Creating Dynamic Named Ranges
-
Quiz: Logical and Lookup Formulas
Section 13: PivotTables & PivotCharts
-
Introduction to PivotTables and When to Use Them
-
Creating PivotTables from Tables and Ranges
-
Rearranging Fields – Rows, Columns, Filters, Values
-
Using Value Field Settings – Sum, Count, Average, etc.
-
Formatting PivotTables Professionally
-
Creating PivotCharts Linked to PivotTables
-
Quiz: PivotTables and Charts
Section 14: Advanced PivotTable Features
-
Grouping Data by Date, Numbers or Categories
-
PivotTable Filters, Slicers and Timelines
-
Using ‘Show Values As’: % of Total, Difference From, etc.
-
Combining Multiple Tables using Relationships
-
Drill Down and Show Details in Pivot
-
Calculated Fields in PivotTables
Section 15: Charts & Data Visualization
-
Chart Types Overview – Column, Line, Pie, Bar, Area
-
Creating Your First Chart in Excel
-
Formatting Chart Elements – Titles, Axes, Legends
-
Combo Charts and Dual Axis Charts
-
sing Sparklines for Mini Trends
-
Dynamic Charts with Named Ranges
Section 16: Dashboard Design & Interactivity
-
Best Practices in Dashboard Design
-
Using Shapes, Icons, and Visual Elements
-
Inserting and Formatting Form Controls
-
Creating Drop-down, Checkboxes, Scrollbars
-
Linking Controls to Cells and Charts
-
Creating Interactive Dashboards
-
Printing & Exporting Dashboards as PDF
Section 17: Power Query Basics
-
Introduction to Power Query and Use Cases
-
Importing Data from Excel, Web, and Other Sources
-
Power Query Interface & Navigation
-
Removing Columns, Duplicates, Errors, Blanks
-
Transform Column Data – Split, Merge, Replace
-
Changing Data Types & Auto-detect Errors
-
Sorting and Filtering in Power Query
-
Load to Table, Data Model, or Connection Only
Section 18: Power Query Transformations
-
Group By and Summarize in Power Query
-
Using Pivot and Unpivot Columns
-
Extracting Parts of Text and Date Columns
-
Using Parameters and Custom Functions
-
Append Queries – Combine Rows from Tables
-
Merge Queries – Lookup-Like Join Operations
-
Refreshing Query Data Automatically
Section 19: Power Query Automation
-
Understanding Applied Steps and Dependencies
-
Working with Folder Sources
-
Creating Dynamic Power Query Templates
-
Practice: Power Query Sales Data Cleaner
-
Quiz: Power Query Transformations
Section 20: Power Pivot & DAX Basics
-
What is Power Pivot and When to Use It
-
Data Model and Relationships Basics
-
Creating Relationships Between Tables
-
Calculated Columns vs Measures
-
Basic DAX: SUM, AVERAGE, COUNT, DISTINCTCOUNT
-
Using KPIs and Hierarchies in Power Pivot
-
Practice: Profitability Dashboard using Power Pivot
Section 21: DAX Functions & Time Intelligence
-
Understanding Filter Context in DAX
-
DAX with IF, SWITCH, and CALCULATE
-
Time Intelligence Functions – DATESYTD, TOTALYTD
-
Filtering Using DAX Expressions
-
Managing Relationships and Model Performance
Section 22: Excel VBA Basics
-
Introduction to Macros and Recording Tasks
-
Saving Workbooks with Macros
-
Recording and Running Macros with Buttons
-
Introduction to VBA Editor and Windows
-
Understanding Sub Procedures and Modules
-
Using MsgBox and InputBox
-
Declaring Variables and Data Types
-
Using If, Else, Select Case in VBA
-
For Loop, Do Loop, and While Loop
-
Quiz: VBA Basics
Section 23: Intermediate VBA & UserForms
-
With…End With Structure
-
Working with Ranges and Worksheets in VBA
-
VBA Events: Workbook_Open, Worksheet_Change
-
Error Handling – On Error Resume Next
-
Creating a Simple UserForm
-
Adding Buttons, TextBoxes, ComboBoxes
-
Linking UserForm to Sheet Data
-
Protecting and Unprotecting Sheets via VBA
-
Reusing VBA Modules across Files
-
Practice: Attendance Automation Tool
Section 24: Final Projects & Integration
-
Final Project Overview: Automating Sales Report
-
Power Query + Pivot + Dashboard Integration
-
VBA Buttons to Automate Reports
-
Generating Printable PDF Reports via Macro
-
Real-World Project: HR Leave Management
Section 25: Wrap-Up & Bonus
-
Excel Tips & Tricks for Speed and Accuracy
-
Excel Keyboard Shortcuts Masterclass
-
Resource Library: Templates and Reference Files
-
Feedback & Course Completion
Student Ratings & Reviews
No Review Yet
