Learn Excel 2021 Beyond the Basics – An Intermediate Course

  • Post category:StudyBullet-9
  • Reading time:12 mins read


Get more out of Microsoft Excel 2021/365 with our intermediate training course!

What you will learn

Designing better spreadsheets and controlling user input

How to use logical functions to make better business decisions

Constructing functional and flexible lookup formulas

How to use Excel tables to structure data and make it easy to update

Extracting unique values from a list

Sorting and filtering data using advanced features and new Excel formulas

Working with date and time functions

Extracting data using text functions

Importing data and cleaning it up before analysis

Analyzing data using PivotTables

Representing data visually with PivotCharts

Adding interactions to PivotTables and PivotCharts

Creating an interactive dashboard to present high-level metrics

Auditing formulas and troubleshooting common Excel errors

How to control user input with data validation

Using WhatIf analysis tools to see how changing inputs affect outcomes.

Description

***Exercise and demo files included***

In this second installment of our Excel 2021 course series, you can expand your Excel foundation beyond what you’ve learned in the beginners’ course and take your skills up another notch.

During this course, you will create intermediate-level formulas, prepare data for analysis using PivotTables and PivotCharts, make use of WhatIf analysis tools, learn how to use validation rules to control data input, and explore the fundamentals of spreadsheet design.

And we are just getting started!

You will also get to discover all the new features and functions available in Excel 2021, the latest standalone version of Microsoft Excel.


Get Instant Notification of New Courses on our Telegram channel.

Noteβž› Make sure your π”ππžπ¦π² cart has only this course you're going to enroll it now, Remove all other courses from the π”ππžπ¦π² cart before Enrolling!


Explore the exciting world of dynamic array functions and learn how to use XLOOKUP, XMATCH, FILTER, and so much more.

This Excel 2021 Intermediate course is designed for those with beginner-level knowledge of Excel who are looking to take advantage of the application’s more advanced features. It’s also suitable for individuals who have beginner to intermediate skills using an older version of Excel.

The only prerequisites for this course are beginner-level knowledge of Excel and a working copy of Excel 2021.

This course covers:

  • Designing better spreadsheets and controlling user input
  • How to use logical functions to make better business decisions
  • Constructing functional and flexible lookup formulas
  • How to use Excel tables to structure data and make it easy to update
  • Extracting unique values from a list
  • Sorting and filtering data using advanced features and new Excel formulas
  • Working with date and time functions
  • Extracting data using text functions
  • Importing data and cleaning it up before analysis
  • Analyzing data using PivotTables
  • Representing data visually with PivotCharts
  • Adding interactions to PivotTables and PivotCharts
  • Creating an interactive dashboard to present high-level metrics
  • Auditing formulas and troubleshooting common Excel errors
  • How to control user input with data validation
  • Using WhatIf analysis tools to see how changing inputs affect outcomes.

This course includes:

  1. 9+ hours of video tutorials
  2. 84 individual video lectures
  3. Course and exercise files to follow along
  4. Certificate of completion
English
language

Content

Introduction

Course Introduction
WATCH ME: Essential Information for a Successful Training Experience
DOWNLOAD ME: Course Exercise Files
DOWNLOAD ME: Course Instructor Files
Section Quiz

Designing Better Spreadsheets

The Golden Rules of Spreadsheet Design
Improving Readability with Cell Styles
Controlling Data Input
Adding Navigation Buttons
Section Quiz

Making Decisions with Logical Functions

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
Section Quiz

Looking Up Information

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
Section Quiz

Advanced Sorting and Filtering

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
Section Quiz

Working with Date and Time

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
Section Quiz

Preparing Data for Analysis

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
Section Quiz

PivotTables

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
Section Quiz

Pivot Charts

Creating a Pivot Chart
Formatting a Pivot Chart – Part 1
Formatting a Pivot Chart – Part 2
Using Map Charts
Exercise 07
Section Quiz

Adding Interaction to PivotTables and Charts

Inserting and Formatting Slicers
Inserting Timeline Slicers
Connecting Slicers to Pivot Charts
Updating PivotTable Data
Exercise 08
Section Quiz

Interactive Dashboards

What is a Dashboard?
Assembling a Dashboard – Part 1
Assembling a Dashboard – Part 2
Assembling a Dashboard – Part 3
Exercise 09
Section Quiz

Formula Auditing

Troubleshooting Common Errors
Tracing Precedents and Formula Auditing
Exercise 10
Section Quiz

Data Validation

Creating Dynamic Drop-down Lists
Other Types of Data Validation
Custom Data Validation
Exercise 11
Section Quiz

WhatIf Analysis Tools

Goal Seek and the PMT Function
Using Scenario Manager
Data Tables: One Variable
Data Tables: Two Variables
Exercise 12
Section Quiz

Course Close

Course Close
Enroll for Free

πŸ’  Follow this Video to Get Free Courses on Every Udemy Topics! πŸ’ 

Found It Free? Share It Fast!
Tags: , ,