Microsoft Excel Level 3 (Advanced)

About Course

Master the most powerful features of Microsoft Excel with this Advanced Level 3 course. You will learn to build complex formulas, automate tasks with Macros and VBA, transform data with Power Query, and perform sophisticated analysis using PivotTables, What-If Analysis, and the Data Analysis ToolPak. You will also design interactive dashboards and learn to protect and audit your workbooks. This course will transform you into a true Excel expert, ready to handle any data challenge.

Build advanced formulas using INDEX/MATCH, SUMIFS, TEXT functions, and dynamic references like OFFSET and INDIRECT.
Automate repetitive tasks by recording Macros, writing VBA code, and creating interactive dashboards with form controls.
Transform and clean data from multiple sources efficiently using Power Query (Get & Transform) for refreshable reports.
Perform sophisticated data analysis with What-If Analysis, Goal Seek, Solver, and the Data Analysis ToolPak.
Confidently protect, audit, and share workbooks using collaboration tools, formula auditing, and worksheet protection.

Course Content

Module 1: Advanced Formulas And Functions

Lesson 1: Using TEXT Functions (LEFT, RIGHT, MID, LEN, TRIM, UPPER, LOWER, PROPER)
Lesson 2: Using DATE And TIME Functions (TODAY, NOW, DATE, DAY, MONTH, YEAR, NETWORKDAYS, WORKDAY, EDATE)
Lesson 3: Using Mathematical Functions (SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF, AVERAGEIFS)
Lesson 4: Using Lookup Functions (VLOOKUP With Multiple Criteria Using Helper Column, XLOOKUP Advanced, INDEX And MATCH Combo)
Lesson 5: Using INDIRECT Function (Dynamic Cell References) Using OFFSET Function (Dynamic Ranges)

Module 2: Advanced PivotTables

Lesson 1: Creating A PivotTable From Multiple Tables (Data Model/Relationships)
Lesson 2: Using Calculated Fields In PivotTables
Lesson 3: Using Calculated Items In PivotTables
Lesson 4: Grouping Dates By Month, Quarter, Year
Lesson 5: Grouping Numbers Into Ranges (Bins)
Lesson 6: Creating A PivotChart
Lesson 7: Adding Timeline Slicers
Lesson 8: Using Multiple Slicers For Interactive Filtering
Lesson 9: Connecting Slicers To Multiple PivotTables
Lesson 10: Showing Values As (Running Total, % Of Total, % Difference From)

Module 3: Power Query (Get & Transform)

Lesson 1: Understanding Power Query
Lesson 2: Importing Data From Excel Files
Lesson 3: Importing Data From CSV/Text Files
Lesson 4: Importing Data From A Folder (Combine Multiple Files)
Lesson 5: Cleaning Data (Remove Rows, Remove Columns)
Lesson 6: Changing Data Types
Lesson 7: Splitting Columns By Delimiter
Lesson 8: Merging Queries (Similar To VLOOKUP)
Lesson 9: Appending Queries (Stacking Data)
Lesson 10: Creating A Refreshable Connection

Module 4: What-If Analysis And Data Tables

Module 5: Advanced Conditional Formatting

Module 6: Introduction To Macros (VBA)

Lesson 1: Understanding Macros And VBA
Lesson 2: Enabling The Developer Tab
Lesson 3: Recording A Simple Macro
Lesson 4: Running A Recorded Macro
Lesson 5: Assigning A Macro To A Button Or Shape
Lesson 6: Viewing And Editing VBA Code
Lesson 7: Saving A Workbook As Macro-Enabled (.xlsm)
Lesson 8: Macro Security Settings (Trust Center)
Lesson 9: Recording A Macro With Relative References

Module 7: VBA Programming Fundamentals

Lesson 1: Opening The VBA Editor (Alt + F11)
Lesson 2: Understanding The Project Explorer And Code Window
Lesson 3: Inserting A Module
Lesson 4: Creating A Simple Subroutine (Sub…End Sub)
Lesson 5: Declaring Variables (Dim As String, Integer, Long, Double, Range)
Lesson 6: Using If…Then…Else Statements In VBA
Lesson 7: Using For…Next Loops
Lesson 8: Using Do While Loops
Lesson 9: Using MsgBox (Display Messages)
Lesson 10: Using InputBox (Get User Input)

Module 8: VBA For Worksheet Automation

Lesson 1: Referring To Ranges (Range(“A1”), Cells(1,1))
Lesson 2: Reading And Writing Cell Values
Lesson 3: Using Offset To Move Relative To A Cell
Lesson 4: Using End(xlDown) To Find Last Used Row
Lesson 5: Looping Through A Range Of Cells
Lesson 6: Applying Formulas With VBA
Lesson 7: Copying And Pasting With VBA
Lesson 8: Automating Chart Creation With VBA
Lesson 9: Creating Automated Reports

Module 9: Dashboard Design And Interactivity

Lesson 1: Understanding Dashboard Principles (KPIs, Visual Hierarchy)
Lesson 2: Creating A Dashboard Layout Without Gridlines
Lesson 3: Using Form Controls (Combo Box, List Box, Option Buttons)
Lesson 4: Connecting Form Controls To Cells
Lesson 5: Creating Dynamic Charts That Change With Control Selection
Lesson 6: Using The Camera Tool For Snapshots
Lesson 7: Adding Scroll Bars For Dynamic Ranges
Lesson 8: Creating A PivotTable Dashboard With Slicers
Lesson 9: Protecting Dashboard Elements From User Changes

Module 10: Advanced Data Analysis Tools

Module 11: Collaboration, Protection, And Auditing

Lesson 1: Sharing Workbooks For Co-Authoring (OneDrive/SharePoint)
Lesson 2: Tracking Changes (Legacy Feature)
Lesson 3: Adding Comments And Notes
Lesson 4: Protecting A Worksheet (Locking/Unlocking Cells)
Lesson 5: Protecting A Workbook Structure (Prevent Adding/Deleting Sheets)
Lesson 6: Password Protecting A Workbook To Open
Lesson 7: Using The Formula Auditing Tools (Trace Precedents, Trace Dependents)
Lesson 8: Using Evaluate Formula Tool
Lesson 9: Using Watch Window For Critical Cells
Lesson 10: Checking For Formula Errors (#N/A, #VALUE, #REF, #DIV/0)
Lesson 11: Removing Hidden Metadata And Personal Information
Lesson 12: Finalizing And Distributing Industry-Ready Workbooks

Student Ratings & Reviews

No Review Yet

About Course

What Will You Learn?

Course Content

Module 1: Advanced Formulas And Functions

Lesson 1: Using TEXT Functions (LEFT, RIGHT, MID, LEN, TRIM, UPPER, LOWER, PROPER)

Lesson 2: Using DATE And TIME Functions (TODAY, NOW, DATE, DAY, MONTH, YEAR, NETWORKDAYS, WORKDAY, EDATE)

Lesson 3: Using Mathematical Functions (SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF, AVERAGEIFS)

Lesson 4: Using Lookup Functions (VLOOKUP With Multiple Criteria Using Helper Column, XLOOKUP Advanced, INDEX And MATCH Combo)

Lesson 5: Using INDIRECT Function (Dynamic Cell References) Using OFFSET Function (Dynamic Ranges)

Module 2: Advanced PivotTables

Lesson 1: Creating A PivotTable From Multiple Tables (Data Model/Relationships)

Lesson 2: Using Calculated Fields In PivotTables

Lesson 3: Using Calculated Items In PivotTables

Lesson 4: Grouping Dates By Month, Quarter, Year

Lesson 5: Grouping Numbers Into Ranges (Bins)

Lesson 6: Creating A PivotChart

Lesson 7: Adding Timeline Slicers

Lesson 8: Using Multiple Slicers For Interactive Filtering

Lesson 9: Connecting Slicers To Multiple PivotTables

Lesson 10: Showing Values As (Running Total, % Of Total, % Difference From)

Module 3: Power Query (Get & Transform)

Lesson 1: Understanding Power Query

Lesson 2: Importing Data From Excel Files

Lesson 3: Importing Data From CSV/Text Files

Lesson 4: Importing Data From A Folder (Combine Multiple Files)

Lesson 5: Cleaning Data (Remove Rows, Remove Columns)

Lesson 6: Changing Data Types

Lesson 7: Splitting Columns By Delimiter

Lesson 8: Merging Queries (Similar To VLOOKUP)

Lesson 9: Appending Queries (Stacking Data)

Lesson 10: Creating A Refreshable Connection

Module 4: What-If Analysis And Data Tables

Lesson 1: Understanding What-If Analysis

Lesson 2: Using Goal Seek (Find Input For Desired Result)

Lesson 3: Creating A One-Variable Data Table

Lesson 4: Creating A Two-Variable Data Table

Lesson 5: Using Scenario Manager

Lesson 6: Creating And Saving Scenarios (Best Case, Worst Case)

Lesson 7: Creating A Scenario Summary Report

Lesson 8: Using Solver For Optimization Problems (Maximize Profit/Minimize Cost)

Module 5: Advanced Conditional Formatting

Lesson 1: Using Formulas In Conditional Formatting

Lesson 2: Highlighting Entire Rows Based On A Cell Value

Lesson 3: Highlighting Duplicate Values Using Formula

Lesson 4: Highlighting Weekend Dates

Lesson 5: Creating Color Scales Based On Custom Formulas

Lesson 6: Creating Icon Sets Based On Custom Rules

Lesson 7: Using Conditional Formatting With AND/OR

Lesson 8: Setting Conditional Formatting Priority And Order

Module 6: Introduction To Macros (VBA)

Lesson 1: Understanding Macros And VBA

Lesson 2: Enabling The Developer Tab

Lesson 3: Recording A Simple Macro

Lesson 4: Running A Recorded Macro

Lesson 5: Assigning A Macro To A Button Or Shape

Lesson 6: Viewing And Editing VBA Code

Lesson 7: Saving A Workbook As Macro-Enabled (.xlsm)

Lesson 8: Macro Security Settings (Trust Center)

Lesson 9: Recording A Macro With Relative References

Module 7: VBA Programming Fundamentals

Lesson 1: Opening The VBA Editor (Alt + F11)

Lesson 2: Understanding The Project Explorer And Code Window

Lesson 3: Inserting A Module

Lesson 4: Creating A Simple Subroutine (Sub…End Sub)

Lesson 5: Declaring Variables (Dim As String, Integer, Long, Double, Range)

Lesson 6: Using If…Then…Else Statements In VBA

Lesson 7: Using For…Next Loops

Lesson 8: Using Do While Loops

Lesson 9: Using MsgBox (Display Messages)

Lesson 10: Using InputBox (Get User Input)

Module 8: VBA For Worksheet Automation

Lesson 1: Referring To Ranges (Range(“A1”), Cells(1,1))

Lesson 2: Reading And Writing Cell Values

Lesson 3: Using Offset To Move Relative To A Cell

Lesson 4: Using End(xlDown) To Find Last Used Row

Lesson 5: Looping Through A Range Of Cells

Lesson 6: Applying Formulas With VBA

Lesson 7: Copying And Pasting With VBA

Lesson 8: Automating Chart Creation With VBA

Lesson 9: Creating Automated Reports