Microsoft Excel Level 2 (Intermediate)

About Course

Take your Excel skills to the next level with this Microsoft Excel Level 2 (Intermediate) course. Building on foundational knowledge, you will master advanced formatting, logical and lookup functions, Excel Tables, and chart creation. You will also learn to sort, filter, and validate data, and get hands-on experience with PivotTables. This course is designed to transform you from a basic user into a confident, efficient Excel power user.

Master advanced functions like VLOOKUP, XLOOKUP, and nested IF statements to perform complex data lookups and logical tests.
Create and manage dynamic Excel Tables and Named Ranges to make formulas more readable and efficient.
Build professional, visually appealing charts, including column, line, pie, and sparkline charts, to present data effectively.
Confidently sort, filter, and validate large datasets to organize information and ensure data integrity.
Analyze and summarize vast amounts of data quickly using PivotTables, slicers, and calculated fields.

Course Content

Module 1: Advanced Formatting Techniques

Lesson 1: Using Cell Styles
Lesson 2: Creating And Modifying Cell Styles
Lesson 3: Using Number Formatting Codes (Custom Formats)
Lesson 4: Using Format Painter Multiple Times
Lesson 5: Copying Formatting Only (Paste Special)
Lesson 6: Using Conditional Formatting (Highlight Cell Rules)
Lesson 7: Using Conditional Formatting (Top/Bottom Rules)
Lesson 8: Clearing Conditional Formatting
Lesson 9: Managing Conditional Formatting Rules

Module 2: Absolute And Mixed Cell References

Module 3: Logical Functions

Module 4: Lookup And Reference Functions

Module 5: Working With Named Ranges

Lesson 1: Understanding Named Ranges
Lesson 2: Creating A Named Range Using The Name Box
Lesson 3: Creating A Named Range Using Define Name
Lesson 4: Creating Names From Selection (Labels)
Lesson 5: Navigating Using Named Ranges
Lesson 6: Using Named Ranges In Formulas
Lesson 7: Using Named Ranges In VLOOKUP
Lesson 8: Editing And Deleting Named Ranges
Lesson 9: Viewing The Name Manager

Module 6: Working With Tables (Excel Tables)

Lesson 1: Understanding Excel Tables

09:32
Lesson 2: Creating A Table From Existing Data
Lesson 3: Table Formatting Styles
Lesson 4: Adding Data To A Table (Auto-Expanding)
Lesson 5: Removing Duplicate Rows From A Table
Lesson 6: Converting A Table Back To A Range
Lesson 7: Using Structured References (@ and # Symbols)
Lesson 8: Adding A Total Row To A Table
Lesson 9: Using Slicers With Tables

Module 7: Creating And Formatting Charts

Lesson 1: Understanding Charts
Lesson 2: Creating A Column Chart
Lesson 3: Creating A Bar Chart
Lesson 4: Creating A Line Chart
Lesson 5: Creating A Pie Chart
Lesson 6: Moving And Resizing A Chart
Lesson 7: Changing Chart Type
Lesson 8: Adding Chart Titles And Data Labels
Lesson 9: Adding A Legend And Gridlines
Lesson 10: Formatting Chart Elements (Colors, Fonts)
Lesson 11: Creating A Sparkline (Mini Chart Inside A Cell)

Module 8: Working With Multiple Worksheets

Module 9: Sorting And Filtering Data

Lesson 1: Understanding Sorting
Lesson 2: Sorting A Single Column
Lesson 3: Sorting Multiple Columns (Custom Sort)
Lesson 4: Sorting By Cell Color Or Font Color
Lesson 5: Understanding Filtering
Lesson 6: Applying Text Filters (Contains, Begins With)
Lesson 7: Applying Number Filters (Greater Than, Between)
Lesson 8: Applying Date Filters (This Month, Last Quarter)
Lesson 9: Clearing Filters
Lesson 10: Using Advanced Filter (Unique Records Only)

Module 10: Data Validation

Lesson 1: Understanding Data Validation
Lesson 2: Creating A Whole Number Validation (Between 1 And 100)
Lesson 3: Creating A Decimal Validation
Lesson 4: Creating A Date Validation (No Past Dates)
Lesson 5: Creating A Text Length Validation
Lesson 6: Creating A Dropdown List From A Range
Lesson 7: Creating A Dropdown List By Typing Values
Lesson 8: Creating Custom Validation Using A Formula
Lesson 9: Adding Input Messages And Error Alerts
Lesson 10: Finding Invalid Data With Circle Invalid Data

Module 11: Introduction To PivotTables

Lesson 1: Understanding PivotTables
Lesson 2: Preparing Data For A PivotTable
Lesson 3: Creating A PivotTable From A Table/Range
Lesson 4: Understanding PivotTable Fields (Rows, Columns, Values, Filters)
Lesson 5: Adding Fields To Rows And Columns
Lesson 6: Adding Fields To Values (Sum, Count, Average)
Lesson 7: Adding Report Filters (Slicers)
Lesson 8: Changing Value Field Settings (Sum To Average)
Lesson 1: Refreshing PivotTable Data
Lesson 10: Formatting PivotTable Numbers

Student Ratings & Reviews

No Review Yet

About Course

What Will You Learn?

Course Content

Module 1: Advanced Formatting Techniques

Lesson 1: Using Cell Styles

Lesson 2: Creating And Modifying Cell Styles

Lesson 3: Using Number Formatting Codes (Custom Formats)

Lesson 4: Using Format Painter Multiple Times

Lesson 5: Copying Formatting Only (Paste Special)

Lesson 6: Using Conditional Formatting (Highlight Cell Rules)

Lesson 7: Using Conditional Formatting (Top/Bottom Rules)

Lesson 8: Clearing Conditional Formatting

Lesson 9: Managing Conditional Formatting Rules

Module 2: Absolute And Mixed Cell References

Lesson 1: Understanding Absolute References (A1)

Lesson 2: Understanding Mixed References (A1 or A1)

Lesson 3: Creating An Absolute Reference In A Formula

Lesson 4: Creating Mixed References

Lesson 5: Using F4 To Toggle Reference Types

Lesson 6: Applying Absolute References To SUM Formulas

Lesson 7: Using Absolute References In Percentage Calculations

Module 3: Logical Functions

Lesson 1: Understanding Logical Functions

Lesson 2: Using The IF Function (Simple True/False)

Lesson 3: Creating Nested IF Functions (Multiple Conditions)

Lesson 4: Using The AND Function

Lesson 5: Using The OR Function

Lesson 6: Combining IF With AND/OR

Lesson 7: Using The NOT Function

Lesson 8: Using The IFERROR Function (Hide Errors)

Module 4: Lookup And Reference Functions

Lesson 1: Understanding Lookup Functions

Lesson 2: Using The VLOOKUP Function (Vertical Lookup)

Lesson 3: Using The HLOOKUP Function (Horizontal Lookup)

Lesson 4: Using VLOOKUP With Exact Match (FALSE)

Lesson 5: Using VLOOKUP With Approximate Match (TRUE)

Lesson 6: Using The XLOOKUP Function (Modern Excel)

Lesson 7: Using The CHOOSE Function

Lesson 8: Handling #N/A Errors In Lookups

Module 5: Working With Named Ranges

Lesson 1: Understanding Named Ranges

Lesson 2: Creating A Named Range Using The Name Box

Lesson 3: Creating A Named Range Using Define Name

Lesson 4: Creating Names From Selection (Labels)

Lesson 5: Navigating Using Named Ranges

Lesson 6: Using Named Ranges In Formulas

Lesson 7: Using Named Ranges In VLOOKUP

Lesson 8: Editing And Deleting Named Ranges

Lesson 9: Viewing The Name Manager

Module 6: Working With Tables (Excel Tables)

Lesson 1: Understanding Excel Tables

Lesson 2: Creating A Table From Existing Data

Lesson 3: Table Formatting Styles

Lesson 4: Adding Data To A Table (Auto-Expanding)

Lesson 5: Removing Duplicate Rows From A Table

Lesson 6: Converting A Table Back To A Range

Lesson 7: Using Structured References (@ and # Symbols)

Lesson 8: Adding A Total Row To A Table

Lesson 9: Using Slicers With Tables

Module 7: Creating And Formatting Charts

Lesson 1: Understanding Charts

Lesson 2: Creating A Column Chart

Lesson 3: Creating A Bar Chart

Lesson 4: Creating A Line Chart

Lesson 5: Creating A Pie Chart

Lesson 6: Moving And Resizing A Chart

Lesson 7: Changing Chart Type

Lesson 8: Adding Chart Titles And Data Labels

Lesson 9: Adding A Legend And Gridlines

Lesson 10: Formatting Chart Elements (Colors, Fonts)

Lesson 11: Creating A Sparkline (Mini Chart Inside A Cell)

Module 8: Working With Multiple Worksheets

Lesson 1: Creating 3D Formulas (Across Worksheets)

Lesson 2: Using SUM Across Multiple Worksheets

Lesson 3: Linking Cells Between Worksheets

Lesson 4: Creating A Summary Sheet From Multiple Sheets

Lesson 5: Moving And Copying Worksheets Between Workbooks

Lesson 6: Grouping Worksheets For Simultaneous Editing

Lesson 7: Creating External References (Between Workbooks)

Module 9: Sorting And Filtering Data