This course explores Microsoft Excel 2021, the latest stand-alone version packed with many features previously exclusive to Excel 365. You’ll start by navigating the interface, creating spreadsheets, using shortcuts, applying formatting, and working with essential formulas. Moving forward, you’ll dive into intermediate skills like building advanced formulas, analyzing data with PivotTables and Pivot Charts, applying What-If tools, and using validation rules. Finally, you’ll level up to power-user techniques, including new functions like LET and LAMBDA to create custom variables and functions. By the end, you’ll be equipped
Who should take this course?
This course is designed for students, professionals, business analysts, and anyone looking to improve their data management and analysis skills using Microsoft Excel in Office 365. It’s ideal for beginners who want to learn the fundamentals as well as intermediate users aiming to boost productivity with modern Excel features and tools.
What you will learn
Become familiar with what's new in Excel 2021
Use Excel lists and master sorting and filtering
Represent data visually with Pivot Charts
Use WhatIf analysis tools to see how changing inputs affect outcomes
Analyze data with advanced PivotTable and PivotChart hacks
Import and clean data using Power Query
Course Outline
Excel 2021 for Beginner Level
Section Introduction
Excel 2021 Versus Excel for Microsoft 365
Launching Excel
The Start Screen
Exploring the Interface
Understanding Ribbons, Tabs, and Menus
The Backstage Area
Customizing the Quick Access Toolbar
Useful Keyboard Shortcuts
Getting Help
Exercise 01
Working with Excel Templates
Working with Workbooks and Worksheets
Saving Workbooks and Worksheets
Entering and Editing Data
Navigating and Selecting Cells, Rows, and Columns
Exercise 02
Formulas and Functions Explained
Performing Calculations with the SUM Function
Counting Values and Blanks
Finding the Average with the AVERAGE Function
Working with the MIN and MAX Functions
Handling Errors in Formulas
Absolute Versus Relative Referencing
Autosum and AutoFill
Flash Fill
Exercise 03
What Are Named Ranges?
Creating Named Ranges
Managing Named Ranges
Using Named Ranges in Calculations
Exercise 04
Applying Number Formats
Applying Date and Time Formats
Formatting Cells, Rows, and Columns
Using Format Painter
Exercise 05
Working with Rows and Columns
Deleting and Clearing Cells
Aligning Text and Numbers
Applying Themes and Styles
Exercise 06
How to Structure a List
Sorting a List (Single-Level Sort)
Sorting a List (Multi-Level Sort)
Sorting Using a Custom List (Custom Sort)
Using Autofilter to Filter a List
Format as a Table
Creating Subtotals in a List
Exercise 07
Using Cut and Copy
Paste Options
Pasting from the Clipboard
Linking to Other Worksheets and Workbooks
3D Referencing
Inserting Hyperlinks to Worksheets
Exercise 08
Looking Up Information with VLOOKUP
VLOOKUP Approximate Match
Error Handling Functions
Basic Logical Functions (IF, AND, OR)
Making Decisions with IF Statements
Cleaning Data using Text Functions
Working with Time and Date Functions
Exercise 09
Choosing the Correct Chart Type
Presenting Data with Charts
Formatting Charts
Exercise 10
Highlighting Cell Values
Data Bars
Color Scales
Icon Sets
Exercise 11
Inserting Pictures
Inserting Shapes and Text Boxes
Inserting Icons and 3D Models
Creating Diagrams using SmartArt
Inserting Screenshots
Inserting Comments
Exercise 12
Workbook Views
Using Zoom in a Worksheet
Arranging Workbooks and Worksheets
Freezing Panes
Exercise 13
Setting Margins and Orientation
Setting and Clearing the Print Area
Inserting Page Breaks
Setting Print Titles and a Background
Inserting Headers and Footers
Printing a Workbook
Exercise 14
Protecting Workbooks and Worksheets
Spell Checking
Inspecting the Workbook
Saving the Workbook in Different Formats
Sharing a Workbook
Exercise 15
Section Summary
Excel 2021 for Intermediate level
Section Introduction
The Golden Rules of Spreadsheet Design
Improving Readability with Cell Styles
Controlling Data Input
Adding Navigation Buttons
Logical Functions (AND, OR, IF)
The IF Function
Nested IFs
The IFS Function
Conditional IFs (SUMIF, COUNTIF, AVERAGEIF)
Multiple Criteria (SUMIFS, COUNTIFS, AVERAGEIFS)
Error Handling with IFERROR and IFNA
Exercise 01
Looking Up Information Using VLOOKUP (Exact Match)
Looking Up Information Using VLOOKUP (Approx Match)
Looking Up Information Horizontally Using HLOOKUP
Performing Flexible Lookups with INDEX And MATCH
Using XLOOKUP and XMATCH
The OFFSET Function
The INDIRECT Function
Exercise 02
Performing Sorts on Multiple Columns
Sorting Using a Custom List
The SORT and SORTBY Functions
Using the Advanced Filter
Extracting Unique Values - The UNIQUE Function
The FILTER Function
Exercise 03
Understanding How Dates are Stored in Excel
Applying Custom Date Formats
Using Date and Time Functions
Using the WORKDAY and WORKDAY.INT Functions
Using the NETWORKDAYS and NETWORKDAYS.INT Function
Tabulate Date Differences with the DATEDIF Function
Calculate Dates with EDATE and EOMONTH
Exercise 04
Importing Data into Excel
Removing Blank Rows, Cells, and Duplicates
Changing Case and Removing Spaces
Splitting Data Using Text to Columns
Splitting Data Using Text Functions
Splitting or Combining Cell Data Using Flash Fill
Joining Data Using CONCAT
Formatting Data as a Table
Exercise 05
PivotTables Explained
Creating a PivotTable from Scratch
Pivoting the PivotTable Fields
Applying Subtotals and Grand Totals
Applying Number Formatting to PivotTable Data
Show Values As and Summarize Values By
Grouping PivotTable Data
Formatting Error Values and Empty Cells
Choosing a Report Layout
Applying PivotTable Styles
Exercise 06
Creating a Pivot Chart
Formatting a Pivot Chart - Part 1
Formatting a Pivot Chart - Part 2
Using Map Charts
Exercise 07
Inserting and Formatting Slicers
Inserting Timeline Slicers
Connecting Slicers to Pivot Charts
Updating PivotTable Data
Exercise 08
What Is a Dashboard?
Assembling a Dashboard - Part 1
Assembling a Dashboard - Part 2
Assembling a Dashboard - Part 3
Exercise 09
Troubleshooting Common Errors
Tracing Precedents and Formula Auditing
Exercise 10
Creating Dynamic Drop-Down Lists
Other Types of Data Validation
Custom Data Validation
Exercise 11
Goal Seek and the PMT Function
Using Scenario Manager
Data Tables: One Variable
Data Tables: Two Variables
Exercise 12
Section Summary
Excel 2021 for Advanced Level
Section Introduction
Exercise 01
What Are Dynamic Arrays?
Introduction to Spills and Arrays
Extract and Count Unique Entries
Unique Versus Distinct
Extract Unique Values with Multiple Criteria
Extract Unique Values by Column
The SORT Function
The SORT Function - Horizontal SORT
The SORTBY Function
The SORTBY Function - Horizontal SORT
Simple SEQUENCE and Unstacking Records
The FILTER Function
FILTER with Logic: + Operator (OR)
FILTER with Logic: * Operator (AND)
FILTER with Logic: = Operator (Both or Neither)
FILTER with Logic: - Operator (One or the Other)
Using RANDARRAY and RANDBETWEEN to Randomize Data
Using XLOOKUP to Perform Complex Lookups
Using XMATCH
Exercise 02
Performing Two-Way Lookups
Using the CHOOSE Function
Using the SWITCH Function
Exercise 03
Using MEDIAN, MODE.SNGL, and MODE.MULT
Using LARGE and SMALL
Ranking Data with Sorting Using RANK.EQ and RANK.AVG
The COUNTBLANK Function
Exercise 04
Rounding Values Using ROUND, ROUNDUP, and ROUNDDOWN
Specialized Rounding (MROUND, CEILING.MATH, and FLOOR.MATH)
Using the AGGREGATE Function
Exercise 05
The LET Function
The LAMBDA Function
Exercise 06
Creating a Custom PivotTable Style
Applying Custom Number Formatting
Sorting Using Custom Lists
Applying Multiple Filters to Data
Adjusting Slicer Settings
Locking Workbooks but Not Slicers
Creating a Calculated Field
Creating a Calculated Item
Solve Order
GETPIVOTDATA
Using GETPIVOTDATA - Dependent Data Validation Lists