Excel 2021 for Advanced Level | Microsoft Excel 2021/365

Book Overview & Buying
Table Of Contents

Microsoft Excel 2021/365 - Beginner to Advanced

By : Simon Sez IT

5 (3)

Buy this Video

Microsoft Excel 2021/365 - Beginner to Advanced

5 (3)

By: Simon Sez IT

Buy this Video

Overview of this book

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

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

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

Microsoft Excel 2021/365 - Beginner to Advanced

By : Simon Sez IT

Microsoft Excel 2021/365 - Beginner to Advanced

By: Simon Sez IT

Overview of this book

Confirmation

Buy this book with your credits?

Submit Your Feedback

Create a Free Account To Continue Reading

Sign in to activate your 7-day free access