Microsoft Excel 2021/365 - Beginner to Advanced

Microsoft Excel 2021/365 - Beginner to Advanced

by Simon Sez IT
Released October 2022
Publisher(s): Packt Publishing
ISBN: 9781804612262

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Video 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.

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

Audience

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.

About The Author

Simon Sez IT: Simon Sez IT has offered technical courses since 2008 for individuals, small businesses, and Fortune 500 companies with thousands of employees who can benefit from the easy-to-learn and hands-on software training. It offers over 8000 video tutorials on a range of software programs. Simon Sez IT ensures stress-free eLearning and enhanced employee productivity—whether you implement new software or a technological upgrade in your work environment. With over 600,000 students from 180 countries, Simon Sez IT is the preferred online learning choice for individuals and businesses worldwide.

Table of contents

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

Product information

  • Title: Microsoft Excel 2021/365 - Beginner to Advanced
  • Author(s): Simon Sez IT
  • Release date: October 2022
  • Publisher(s): Packt Publishing
  • ISBN: 9781804612262