![Microsoft Excel 2021/365 - Beginner to Advanced [Video]](https://content.packt.com/_/image/original/V19095/cover_image_large.jpg)
Microsoft Excel 2021/365 - Beginner to Advanced [Video]
Video
Video
$74.99
Subscription
$15.99
$10 p/m for three months
What do you get with a Packt Subscription?
This book & 7000+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with a Packt Subscription?
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with Video + Subscription?
Download this video in MP4 format, plus a monthly download credit
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with a Packt Subscription?
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with eBook?
Download this book in EPUB and PDF formats
Access this title in our online reader
DRM FREE - Read whenever, wherever and however you want
Online reader with customised display settings for better reading experience
What do I get with Print?
Get a paperback copy of the book delivered to your specified Address*
Download this book in EPUB and PDF formats
Access this title in our online reader
DRM FREE - Read whenever, wherever and however you want
Online reader with customised display settings for better reading experience
What do I get with Print?
Get a paperback copy of the book delivered to your specified Address*
Access this title in our online reader
Online reader with customised display settings for better reading experience
What do you get with video?
Download this video in MP4 format
Access this title in our online reader
DRM FREE - Watch whenever, wherever and however you want
Online reader with customised display settings for better learning experience
What do you get with video?
Stream this video
Access this title in our online reader
DRM FREE - Watch whenever, wherever and however you want
Online reader with customised display settings for better learning experience
What do you get with Audiobook?
Download a zip folder consisting of audio files (in MP3 Format) along with supplementary PDF
What do you get with Exam Trainer?
Flashcards, Mock exams, Exam Tips, Practice Questions
Access these resources with our interactive certification platform
Mobile compatible-Practice whenever, wherever, however you want
Video
$74.99
Subscription
$15.99
$10 p/m for three months
What do you get with a Packt Subscription?
This book & 7000+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with a Packt Subscription?
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with Video + Subscription?
Download this video in MP4 format, plus a monthly download credit
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with a Packt Subscription?
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with eBook?
Download this book in EPUB and PDF formats
Access this title in our online reader
DRM FREE - Read whenever, wherever and however you want
Online reader with customised display settings for better reading experience
What do I get with Print?
Get a paperback copy of the book delivered to your specified Address*
Download this book in EPUB and PDF formats
Access this title in our online reader
DRM FREE - Read whenever, wherever and however you want
Online reader with customised display settings for better reading experience
What do I get with Print?
Get a paperback copy of the book delivered to your specified Address*
Access this title in our online reader
Online reader with customised display settings for better reading experience
What do you get with video?
Download this video in MP4 format
Access this title in our online reader
DRM FREE - Watch whenever, wherever and however you want
Online reader with customised display settings for better learning experience
What do you get with video?
Stream this video
Access this title in our online reader
DRM FREE - Watch whenever, wherever and however you want
Online reader with customised display settings for better learning experience
What do you get with Audiobook?
Download a zip folder consisting of audio files (in MP3 Format) along with supplementary PDF
What do you get with Exam Trainer?
Flashcards, Mock exams, Exam Tips, Practice Questions
Access these resources with our interactive certification platform
Mobile compatible-Practice whenever, wherever, however you want
-
Free ChapterExcel 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
- Creating a Dynamic Pivot Chart Title
- Adding a Dynamic Series to a Pivot Chart
- Adding Totals to a Stacked Column Chart
- Exercise 07
- Introduction to Form Controls
- Combo Box - Select from a List
- Check Box - Check/Uncheck Option
- Option Button - Choose an Option
- Spin Button - Move Up and Down a List
- List Box - Select from a List of Items
- Scroll Bar - Scroll Through a List
- Exercise 08
- Power Query Overview
- Importing Data Using Get and Transform
- The Power Query Editor
- Combining Files
- Transforming Files
- Loading Files into Excel
- Exercise 09
- Getting Forecast Data with FRED
- Creating a Linear Sales Forecast
- Creating a Sales Forecast with Seasonality
- Adding Confidence Levels
- Using Forecast Sheets
- Exercise 10
- Using Macros to Automate Tasks
- Recording, Saving, and Running Macros
- Recording a Macro with Relative Referencing
- Multi-Step Macros
- The VBA Editor
- Assigning Macros to Buttons
- Creating a Custom Macro Ribbon
- Exercise 11
- Finding the Last Occurrence of a Value
- Finding the st Value
- Extract Data from the Middle of a String
- Exercise 12
- Section Summary
About this
video
Microsoft has released its latest stand-alone Excel version, which opens up many of the features and functionalities that are available only in Excel 365 through a subscription.
In the first section, we study some basics about how to use Excel 2021 and become familiar with what’s new in it. We will first navigate the Excel 2021 interface and create our first Excel spreadsheet. You will learn some useful keyboard shortcuts to increase productivity, learn how to effectively apply formatting to cells and use conditional formatting, learn how to use basic and intermediate Excel formulas and functions, and more.
Then, in the second section, you will learn how to create intermediate-level formulas, prepare data for analysis using PivotTables and Pivot Charts, make use of WhatIf analysis tools, learn how to use validation rules to control data input, and explore the fundamentals of spreadsheet design.
In the final section, we will build on the skills learned in the beginner and intermediate sections and fast-track our way to being an Excel power user. This section covers the latest updates from Microsoft, including the LET and LAMBDA functions, which allow us to create our own variables and even Excel functions.
By the end of this course, you will be able to use Excel skills confidently.
All the course demo files, and exercise files are available at: https://github.com/PacktPublishing/Microsoft-Excel-2021-365---Beginner-to-Advanced
- Publication date:
- October 2022
- Publisher
- Packt
- Duration
- 30 hours 36 minutes
- ISBN
- 9781804612262
Download code from GitHub
![Microsoft Excel 2021/365 - Beginner to Advanced [Video]](https://content.packt.com/V19095/cover_image_small.jpg)