on-demand course

Microsoft Excel 2021/365 - Beginner to Advanced

with Simon Sez IT

October 2022

Beginner

30h 46m

English

Packt Publishing

Closed Captioning available in English

Watch now

Unlock full access

Includes

Includes Quizzes

Earns Badge

Course outline

Section Introduction
2m 25s
Excel 2021 Versus Excel for Microsoft 365
5m 43s
Launching Excel
3m 43s
The Start Screen
10m 15s
Exploring the Interface
11m 43s
Understanding Ribbons, Tabs, and Menus
8m 33s
The Backstage Area
4m 45s
Customizing the Quick Access Toolbar
9m 48s
Useful Keyboard Shortcuts
9m 46s
Getting Help
6m 41s
Exercise 01
2m 23s
Working with Excel Templates
9m 6s
Working with Workbooks and Worksheets
7m 38s
Saving Workbooks and Worksheets
8m 48s
Entering and Editing Data
9m 56s
Navigating and Selecting Cells, Rows, and Columns
7m 26s
Exercise 02
5m 23s
Formulas and Functions Explained
11m 46s
Performing Calculations with the SUM Function
7m 43s
Counting Values and Blanks
7m 30s
Finding the Average with the AVERAGE Function
4m 23s
Working with the MIN and MAX Functions
2m 28s
Handling Errors in Formulas
8m 44s
Absolute Versus Relative Referencing
7m 1s
Autosum and AutoFill
9m 45s
Flash Fill
5m 6s
Exercise 03
5m 16s
What Are Named Ranges?
4m 44s
Creating Named Ranges
4m 29s
Managing Named Ranges
3m 53s
Using Named Ranges in Calculations
5m 23s
Exercise 04
4m 22s
Applying Number Formats
10m 7s
Applying Date and Time Formats
5m 43s
Formatting Cells, Rows, and Columns
8m 43s
Using Format Painter
4m 44s
Exercise 05
4m 10s
Working with Rows and Columns
8m 30s
Deleting and Clearing Cells
6m 29s
Aligning Text and Numbers
11m 17s
Applying Themes and Styles
7m 44s
Exercise 06
5m 27s
How to Structure a List
4m 42s
Sorting a List (Single-Level Sort)
5m 11s
Sorting a List (Multi-Level Sort)
5m 10s
Sorting Using a Custom List (Custom Sort)
8m 16s
Using Autofilter to Filter a List
6m 49s
Format as a Table
10m 23s
Creating Subtotals in a List
7m 39s
Exercise 07
4m 40s
Using Cut and Copy
5m 0s
Paste Options
7m 50s
Pasting from the Clipboard
4m 35s
Linking to Other Worksheets and Workbooks
6m 30s
3D Referencing
5m 58s
Inserting Hyperlinks to Worksheets
9m 43s
Exercise 08
4m 45s
Looking Up Information with VLOOKUP
10m 8s
VLOOKUP Approximate Match
5m 12s
Error Handling Functions
7m 15s
Basic Logical Functions (IF, AND, OR)
12m 28s
Making Decisions with IF Statements
4m 37s
Cleaning Data using Text Functions
12m 13s
Working with Time and Date Functions
11m 21s
Exercise 09
10m 23s
Choosing the Correct Chart Type
6m 39s
Presenting Data with Charts
10m 6s
Formatting Charts
12m 55s
Exercise 10
3m 58s
Highlighting Cell Values
7m 18s
Data Bars
5m 36s
Color Scales
4m 58s
Icon Sets
7m 53s
Exercise 11
5m 47s
Inserting Pictures
10m 57s
Inserting Shapes and Text Boxes
9m 8s
Inserting Icons and 3D Models
6m 3s
Creating Diagrams using SmartArt
10m 45s
Inserting Screenshots
4m 32s
Inserting Comments
5m 17s
Exercise 12
9m 20s
Workbook Views
6m 26s
Using Zoom in a Worksheet
3m 1s
Arranging Workbooks and Worksheets
5m 5s
Freezing Panes
4m 4s
Exercise 13
3m 30s
Setting Margins and Orientation
5m 9s
Setting and Clearing the Print Area
2m 57s
Inserting Page Breaks
4m 37s
Setting Print Titles and a Background
4m 34s
Inserting Headers and Footers
5m 46s
Printing a Workbook
6m 4s
Exercise 14
3m 11s
Protecting Workbooks and Worksheets
9m 2s
Spell Checking
3m 49s
Inspecting the Workbook
7m 25s
Saving the Workbook in Different Formats
4m 25s
Sharing a Workbook
5m 38s
Exercise 15
3m 33s
Section Summary
1m 9s
Section Introduction
6m 19s
The Golden Rules of Spreadsheet Design
11m 59s
Improving Readability with Cell Styles
5m 2s
Controlling Data Input
8m 23s
Adding Navigation Buttons
8m 47s
Logical Functions (AND, OR, IF)
13m 22s
The IF Function
5m 31s
Nested IFs
8m 6s
The IFS Function
6m 8s
Conditional IFs (SUMIF, COUNTIF, AVERAGEIF)
6m 48s
Multiple Criteria (SUMIFS, COUNTIFS, AVERAGEIFS)
7m 18s
Error Handling with IFERROR and IFNA
6m 5s
Exercise 01
7m 33s
Looking Up Information Using VLOOKUP (Exact Match)
10m 45s
Looking Up Information Using VLOOKUP (Approx Match)
4m 28s
Looking Up Information Horizontally Using HLOOKUP
5m 41s
Performing Flexible Lookups with INDEX And MATCH
10m 33s
Using XLOOKUP and XMATCH
10m 13s
The OFFSET Function
10m 54s
The INDIRECT Function
9m 11s
Exercise 02
5m 8s
Performing Sorts on Multiple Columns
7m 8s
Sorting Using a Custom List
3m 40s
The SORT and SORTBY Functions
10m 4s
Using the Advanced Filter
6m 57s
Extracting Unique Values - The UNIQUE Function
5m 21s
The FILTER Function
9m 36s
Exercise 03
5m 46s
Understanding How Dates are Stored in Excel
4m 15s
Applying Custom Date Formats
7m 13s
Using Date and Time Functions
8m 48s
Using the WORKDAY and WORKDAY.INT Functions
3m 52s
Using the NETWORKDAYS and NETWORKDAYS.INT Function
3m 1s
Tabulate Date Differences with the DATEDIF Function
6m 18s
Calculate Dates with EDATE and EOMONTH
7m 26s
Exercise 04
4m 54s
Importing Data into Excel
10m 1s
Removing Blank Rows, Cells, and Duplicates
5m 24s
Changing Case and Removing Spaces
9m 8s
Splitting Data Using Text to Columns
7m 6s
Splitting Data Using Text Functions
8m 10s
Splitting or Combining Cell Data Using Flash Fill
5m 4s
Joining Data Using CONCAT
7m 33s
Formatting Data as a Table
8m 39s
Exercise 05
5m 45s
PivotTables Explained
2m 16s
Creating a PivotTable from Scratch
4m 53s
Pivoting the PivotTable Fields
5m 36s
Applying Subtotals and Grand Totals
3m 19s
Applying Number Formatting to PivotTable Data
3m 2s
Show Values As and Summarize Values By
5m 54s
Grouping PivotTable Data
5m 45s
Formatting Error Values and Empty Cells
5m 7s
Choosing a Report Layout
4m 39s
Applying PivotTable Styles
4m 54s
Exercise 06
4m 9s
Creating a Pivot Chart
5m 9s
Formatting a Pivot Chart - Part 1
8m 42s
Formatting a Pivot Chart - Part 2
9m 20s
Using Map Charts
5m 37s
Exercise 07
4m 25s
Inserting and Formatting Slicers
7m 35s
Inserting Timeline Slicers
5m 12s
Connecting Slicers to Pivot Charts
5m 6s
Updating PivotTable Data
4m 31s
Exercise 08
6m 53s
What Is a Dashboard?
5m 31s
Assembling a Dashboard - Part 1
11m 30s
Assembling a Dashboard - Part 2
10m 52s
Assembling a Dashboard - Part 3
8m 55s
Exercise 09
2m 32s
Troubleshooting Common Errors
8m 15s
Tracing Precedents and Formula Auditing
7m 48s
Exercise 10
7m 34s
Creating Dynamic Drop-Down Lists
7m 32s
Other Types of Data Validation
7m 10s
Custom Data Validation
10m 15s
Exercise 11
6m 57s
Goal Seek and the PMT Function
5m 37s
Using Scenario Manager
7m 31s
Data Tables: One Variable
4m 33s
Data Tables: Two Variables
4m 16s
Exercise 12
8m 37s
Section Summary
1m 39s
Section Introduction
2m 51s
Exercise 01
1m 50s
What Are Dynamic Arrays?
2m 20s
Introduction to Spills and Arrays
10m 52s
Extract and Count Unique Entries
6m 18s
Unique Versus Distinct
3m 28s
Extract Unique Values with Multiple Criteria
3m 15s
Extract Unique Values by Column
4m 27s
The SORT Function
8m 57s
The SORT Function - Horizontal SORT
6m 10s
The SORTBY Function
6m 13s
The SORTBY Function - Horizontal SORT
3m 9s
Simple SEQUENCE and Unstacking Records
10m 13s
The FILTER Function
6m 56s
FILTER with Logic: + Operator (OR)
4m 52s
FILTER with Logic: * Operator (AND)
5m 42s
FILTER with Logic: = Operator (Both or Neither)
4m 16s
FILTER with Logic: - Operator (One or the Other)
4m 53s
Using RANDARRAY and RANDBETWEEN to Randomize Data
9m 50s
Using XLOOKUP to Perform Complex Lookups
12m 25s
Using XMATCH
6m 23s
Exercise 02
13m 14s
Performing Two-Way Lookups
8m 30s
Using the CHOOSE Function
10m 7s
Using the SWITCH Function
4m 37s
Exercise 03
7m 10s
Using MEDIAN, MODE.SNGL, and MODE.MULT
6m 19s
Using LARGE and SMALL
7m 32s
Ranking Data with Sorting Using RANK.EQ and RANK.AVG
7m 44s
The COUNTBLANK Function
4m 50s
Exercise 04
7m 10s
Rounding Values Using ROUND, ROUNDUP, and ROUNDDOWN
7m 2s
Specialized Rounding (MROUND, CEILING.MATH, and FLOOR.MATH)
4m 35s
Using the AGGREGATE Function
4m 36s
Exercise 05
5m 55s
The LET Function
15m 11s
The LAMBDA Function
14m 18s
Exercise 06
11m 54s
Creating a Custom PivotTable Style
9m 53s
Applying Custom Number Formatting
10m 6s
Sorting Using Custom Lists
4m 42s
Applying Multiple Filters to Data
5m 9s
Adjusting Slicer Settings
12m 7s
Locking Workbooks but Not Slicers
3m 22s
Creating a Calculated Field
8m 5s
Creating a Calculated Item
6m 34s
Solve Order
8m 16s
GETPIVOTDATA
12m 1s
Using GETPIVOTDATA - Dependent Data Validation Lists
8m 41s
Creating a Dynamic Pivot Chart Title
10m 45s
Adding a Dynamic Series to a Pivot Chart
12m 51s
Adding Totals to a Stacked Column Chart
5m 55s
Exercise 07
10m 15s
Introduction to Form Controls
4m 18s
Combo Box - Select from a List
7m 24s
Check Box - Check/Uncheck Option
6m 52s
Option Button - Choose an Option
6m 34s
Spin Button - Move Up and Down a List
5m 58s
List Box - Select from a List of Items
5m 3s
Scroll Bar - Scroll Through a List
5m 5s
Exercise 08
6m 1s
Power Query Overview
1m 35s
Importing Data Using Get and Transform
7m 13s
The Power Query Editor
4m 48s
Combining Files
4m 56s
Transforming Files
9m 34s
Loading Files into Excel
4m 5s
Exercise 09
5m 58s
Getting Forecast Data with FRED
5m 38s
Creating a Linear Sales Forecast
8m 25s
Creating a Sales Forecast with Seasonality
5m 7s
Adding Confidence Levels
7m 2s
Using Forecast Sheets
7m 19s
Exercise 10
2m 25s
Using Macros to Automate Tasks
6m 44s
Recording, Saving, and Running Macros
10m 4s
Recording a Macro with Relative Referencing
7m 2s
Multi-Step Macros
7m 15s
The VBA Editor
7m 59s
Assigning Macros to Buttons
6m 48s
Creating a Custom Macro Ribbon
6m 40s
Exercise 11
5m 33s
Finding the Last Occurrence of a Value
7m 54s
Finding the Closest Value
6m 20s
Extract Data from the Middle of a String
8m 59s
Exercise 12
5m 50s
Section Summary
3m 27s

Content preview from Microsoft Excel 2021/365 - Beginner to Advanced

Cleaning Data using Text Functions

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Start your free trial

The Complete Excel Guide: Beginners to Advanced

Simon Sez IT

Microsoft Power BI - A Complete Introduction [2023 EDITION]

Academind by Maximilian Schwarzmüller GmbH, Manuel Lorenz

Excel VBA Programming - The Complete Guide

Boris Paskhaver

Excel Cookbook

Dawn Griffiths

Publisher Resources

ISBN: 9781804612262Supplemental Content

Microsoft Excel 2021/365 - Beginner to Advanced

with Simon Sez IT

Chapter 1 : Excel 2021 for Beginner Level

Chapter 2 : Excel 2021 for Intermediate level

Chapter 3 : Excel 2021 for Advanced Level

Content preview from Microsoft Excel 2021/365 - Beginner to Advanced

Cleaning Data using Text Functions

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

You might also like

The Complete Excel Guide: Beginners to Advanced

Microsoft Power BI - A Complete Introduction [2023 EDITION]

Excel VBA Programming - The Complete Guide

Excel Cookbook

Publisher Resources

Chapter 1 : Excel 2021 for Beginner Level

Chapter 2 : Excel 2021 for Intermediate level

Chapter 3 : Excel 2021 for Advanced Level

Content preview from Microsoft Excel 2021/365 - Beginner to Advanced

Cleaning Data using Text Functions

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,and much more.

You might also like

The Complete Excel Guide: Beginners to Advanced

Microsoft Power BI - A Complete Introduction [2023 EDITION]

Excel VBA Programming - The Complete Guide

Excel Cookbook

Publisher Resources

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.