Search icon CANCEL
Arrow left icon
Explore Products
Best Sellers
New Releases
Books
Videos
Audiobooks
Learning Hub
Conferences
Free Learning
Arrow right icon
Microsoft Excel 2021/365 - Beginner to Advanced
Microsoft Excel 2021/365 - Beginner to Advanced

Microsoft Excel 2021/365 - Beginner to Advanced: Start your spreadsheet journey with this Excel 2021 training course from beginner to advanced level

Arrow left icon
Profile Icon Simon Sez IT
Arrow right icon
AU$24.99 per month
Video Oct 2022 30hrs 36mins 1st Edition
Video
AU$103.99
Subscription
Free Trial
Renews at AU$24.99p/m
Arrow left icon
Profile Icon Simon Sez IT
Arrow right icon
AU$24.99 per month
Video Oct 2022 30hrs 36mins 1st Edition
Video
AU$103.99
Subscription
Free Trial
Renews at AU$24.99p/m
Video
AU$103.99
Subscription
Free Trial
Renews at AU$24.99p/m

What do you get with a Packt Subscription?

Free for first 7 days. $24.99 p/m after that. Cancel any time!
Product feature icon Unlimited ad-free access to the largest independent learning library in tech. Access this title and thousands more!
Product feature icon 50+ new titles added per month, including many first-to-market concepts and exclusive early access to books as they are being written.
Product feature icon Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.
Product feature icon Thousands of reference materials covering every tech concept you need to stay up to date.
Subscribe now
View plans & pricing

Key benefits

  • Utilize useful keyboard shortcuts to increase productivity
  • Learn how to use logical functions to make better business decisions
  • Learn how to combine functions together to create practical formulas to complete specific tasks

Description

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

Who is this book for?

If you are an absolute beginner who has never used Excel before, this course can give you a great foundation. You might have some basic Excel skills and want to improve your proficiency, or you are using an older version of Excel and want to check out what’s new. Well, this course can help you get the ball rolling.

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

Product Details

Country selected
Publication date, Length, Edition, Language, ISBN-13
Publication date : Oct 27, 2022
Length: 30hrs 36mins
Edition : 1st
Language : English
ISBN-13 : 9781804612262
Vendor :
Microsoft
Category :
Languages :
Tools :

What do you get with a Packt Subscription?

Free for first 7 days. $24.99 p/m after that. Cancel any time!
Product feature icon Unlimited ad-free access to the largest independent learning library in tech. Access this title and thousands more!
Product feature icon 50+ new titles added per month, including many first-to-market concepts and exclusive early access to books as they are being written.
Product feature icon Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.
Product feature icon Thousands of reference materials covering every tech concept you need to stay up to date.
Subscribe now
View plans & pricing

Product Details

Publication date : Oct 27, 2022
Length: 30hrs 36mins
Edition : 1st
Language : English
ISBN-13 : 9781804612262
Vendor :
Microsoft
Category :
Languages :
Tools :

Packt Subscriptions

See our plans and pricing
Modal Close icon
AU$24.99 billed monthly
Feature tick icon Unlimited access to Packt's library of 7,000+ practical books and videos
Feature tick icon Constantly refreshed with 50+ new titles a month
Feature tick icon Exclusive Early access to books as they're written
Feature tick icon Solve problems while you work with advanced search and reference features
Feature tick icon Offline reading on the mobile app
Feature tick icon Simple pricing, no contract
AU$249.99 billed annually
Feature tick icon Unlimited access to Packt's library of 7,000+ practical books and videos
Feature tick icon Constantly refreshed with 50+ new titles a month
Feature tick icon Exclusive Early access to books as they're written
Feature tick icon Solve problems while you work with advanced search and reference features
Feature tick icon Offline reading on the mobile app
Feature tick icon Choose a DRM-free eBook or Video every month to keep
Feature tick icon PLUS own as many other DRM-free eBooks or Videos as you like for just AU$5 each
Feature tick icon Exclusive print discounts
AU$349.99 billed in 18 months
Feature tick icon Unlimited access to Packt's library of 7,000+ practical books and videos
Feature tick icon Constantly refreshed with 50+ new titles a month
Feature tick icon Exclusive Early access to books as they're written
Feature tick icon Solve problems while you work with advanced search and reference features
Feature tick icon Offline reading on the mobile app
Feature tick icon Choose a DRM-free eBook or Video every month to keep
Feature tick icon PLUS own as many other DRM-free eBooks or Videos as you like for just AU$5 each
Feature tick icon Exclusive print discounts

Frequently bought together


Stars icon
Total AU$ 208.97
Microsoft Excel 2021/365 - Beginner to Advanced
AU$103.99
Mastering Microsoft Power BI – Second Edition
AU$68.99
Exploring Microsoft Excel's Hidden Treasures
AU$35.99
Total AU$ 208.97 Stars icon

Table of Contents

3 Chapters
Excel 2021 for Beginner Level Chevron down icon Chevron up icon
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 Chevron down icon Chevron up icon
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 Chevron down icon Chevron up icon
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
Get free access to Packt library with over 7500+ books and video courses for 7 days!
Start Free Trial

FAQs

What is included in a Packt subscription? Chevron down icon Chevron up icon

A subscription provides you with full access to view all Packt and licnesed content online, this includes exclusive access to Early Access titles. Depending on the tier chosen you can also earn credits and discounts to use for owning content

How can I cancel my subscription? Chevron down icon Chevron up icon

To cancel your subscription with us simply go to the account page - found in the top right of the page or at https://subscription.packtpub.com/my-account/subscription - From here you will see the ‘cancel subscription’ button in the grey box with your subscription information in.

What are credits? Chevron down icon Chevron up icon

Credits can be earned from reading 40 section of any title within the payment cycle - a month starting from the day of subscription payment. You also earn a Credit every month if you subscribe to our annual or 18 month plans. Credits can be used to buy books DRM free, the same way that you would pay for a book. Your credits can be found in the subscription homepage - subscription.packtpub.com - clicking on ‘the my’ library dropdown and selecting ‘credits’.

What happens if an Early Access Course is cancelled? Chevron down icon Chevron up icon

Projects are rarely cancelled, but sometimes it's unavoidable. If an Early Access course is cancelled or excessively delayed, you can exchange your purchase for another course. For further details, please contact us here.

Where can I send feedback about an Early Access title? Chevron down icon Chevron up icon

If you have any feedback about the product you're reading, or Early Access in general, then please fill out a contact form here and we'll make sure the feedback gets to the right team. 

Can I download the code files for Early Access titles? Chevron down icon Chevron up icon

We try to ensure that all books in Early Access have code available to use, download, and fork on GitHub. This helps us be more agile in the development of the book, and helps keep the often changing code base of new versions and new technologies as up to date as possible. Unfortunately, however, there will be rare cases when it is not possible for us to have downloadable code samples available until publication.

When we publish the book, the code files will also be available to download from the Packt website.

How accurate is the publication date? Chevron down icon Chevron up icon

The publication date is as accurate as we can be at any point in the project. Unfortunately, delays can happen. Often those delays are out of our control, such as changes to the technology code base or delays in the tech release. We do our best to give you an accurate estimate of the publication date at any given time, and as more chapters are delivered, the more accurate the delivery date will become.

How will I know when new chapters are ready? Chevron down icon Chevron up icon

We'll let you know every time there has been an update to a course that you've bought in Early Access. You'll get an email to let you know there has been a new chapter, or a change to a previous chapter. The new chapters are automatically added to your account, so you can also check back there any time you're ready and download or read them online.

I am a Packt subscriber, do I get Early Access? Chevron down icon Chevron up icon

Yes, all Early Access content is fully available through your subscription. You will need to have a paid for or active trial subscription in order to access all titles.

How is Early Access delivered? Chevron down icon Chevron up icon

Early Access is currently only available as a PDF or through our online reader. As we make changes or add new chapters, the files in your Packt account will be updated so you can download them again or view them online immediately.

How do I buy Early Access content? Chevron down icon Chevron up icon

Early Access is a way of us getting our content to you quicker, but the method of buying the Early Access course is still the same. Just find the course you want to buy, go through the check-out steps, and you’ll get a confirmation email from us with information and a link to the relevant Early Access courses.

What is Early Access? Chevron down icon Chevron up icon

Keeping up to date with the latest technology is difficult; new versions, new frameworks, new techniques. This feature gives you a head-start to our content, as it's being created. With Early Access you'll receive each chapter as it's written, and get regular updates throughout the product's development, as well as the final course as soon as it's ready.We created Early Access as a means of giving you the information you need, as soon as it's available. As we go through the process of developing a course, 99% of it can be ready but we can't publish until that last 1% falls in to place. Early Access helps to unlock the potential of our content early, to help you start your learning when you need it most. You not only get access to every chapter as it's delivered, edited, and updated, but you'll also get the finalized, DRM-free product to download in any format you want when it's published. As a member of Packt, you'll also be eligible for our exclusive offers, including a free course every day, and discounts on new and popular titles.