search
Description

In this third course of our ‘Excel 2021’ series, students will build on the skills learned in the beginner and intermediate courses and fast-track their knowledge to ‘guru’ status. Students will be introduced to 6 brand-new dynamic array functions, learn how to build advanced Excel formulas, analyze data with advanced PivotTable skills, create macros to automate tasks, import, and clean data with Power Query, forecast data, and so much more!

Excel 2021 Advanced is designed to take advantage of the latest updates from Microsoft including the ‘game-changing’, LET and LAMBDA functions. These exciting functions allow students to create their own variables and even their own Excel functions.
This course is designed to help students think about problem-solving differently and encourage them to think about how to combine Excel’s myriad of functions together to complete practical tasks.

Excel 2021 Advanced is designed for students who have intermediate-level knowledge of Excel and are looking to build on those skills. It’s also perfect for students with skills in an older version of Excel.

The only pre-requisites for this course are a working copy of Excel 2021 and an intermediate-level knowledge of Excel.

What you will learn?
Use the NEW Dynamic Array functions to perform tasks
Create advanced and flexible lookup formulas
Use statistical functions to rank data and calculate the MEDIAN and MODE
Produce accurate results when working with financial data using math functions
Create variables and functions with LET and LAMBDA
Analyze data with advanced PivotTable and PivotChart hacks
Add interaction to reports and dashboards by incorporating form controls
Import and clean data using Power Query
Predict future values using forecast functions and forecast sheets
Record and run macros to automate repetitive tasks.
Understand and make minor edits to VBA code.
Combine functions together to create practical formulas to complete specific tasks.
schedule Duration
10 hours
emoji_events Certificate
Available
bar_chart Level
Advanced
error_outline Prerequisites
No
check_circle Preparation
No
lock Access
One year

attach_money Price
US$116.22

emoji_events 100% moneyback guarantee
If you cancel within 10 days and you’re not completely satisfied, we’ll give you all your money back. No questions asked.

Features

emoji_events
Verifiable
We make it easy for you to stay compliant with your organization's requirements by providing completion certificates that you can download anytime.
monitor
Online
You're busy - we get that. That's why we've recruited the top experts in your field to provide courses that you can take on your own schedule.
smart_display
Anytime
Learn at your leisure. You can start, pause, and restart the courses whenever you like. They are mobile optimized for extra convenience.
Learn the Easy Way
Access the course for a full year
Start and finish on your own schedule
Download completion certificates
Share your certificate on LinkedIn
Curriculum
schedule 10
hours
1
Guide
|
12
Quiz
|
86
Video
Curriculum
1 Guide
12 Quiz
86 Video
schedule 10
hours
guide
Section 1. Introduction-Part1
videocam Video
3 m
Course Introduction

videocam Video
2 m
Exercise 01

quiz Quiz
Review Questions
Section 2. Dynamic Arrays-Part1
videocam Video
2 m
What are Dynamic Arrays?

videocam Video
11 m
Introduction to Spills and Arrays

videocam Video
6 m
Extract and Count Unique Entries

videocam Video
3 m
Unique vs Distinct

videocam Video
3 m
Extract Unique Values with Multiple Criteria

videocam Video
4 m
Extract Unique Values by Column

videocam Video
9 m
The SORT Function

videocam Video
6 m
The SORT Function - Horizontal SORT

videocam Video
6 m
The SORTBY Function

videocam Video
3 m
The SORTBY Function - Horizontal SORT

videocam Video
10 m
Simple SEQUENCE and Unstacking Records

videocam Video
7 m
The FILTER Function

videocam Video
5 m
FILTER with Logic: + Operator (OR)

videocam Video
6 m
FILTER with Logic: * Operator (AND)

videocam Video
4 m
FILTER with Logic: = Operator (Both or Neither)

videocam Video
5 m
FILTER with Logic: - Operator (One or the Other)

videocam Video
10 m
Using RANDARRAY and RANDBETWEEN to Randomize Data

videocam Video
12 m
Using XLOOKUP to Perform Complex Lookups

videocam Video
6 m
Using XMATCH

videocam Video
13 m
Exercise 02

quiz Quiz
Review Questions
Section 3. Advanced Lookup and Reference Functions-Part1
videocam Video
8 m
Performing 2-Way Lookups

videocam Video
10 m
Using the CHOOSE Function

videocam Video
5 m
Using the SWITCH Function

videocam Video
7 m
Exercise 03

quiz Quiz
Review Questions
Section 4. Statistical Functions-Part1
videocam Video
6 m
Using MEDIAN, MODE.SNGL and MODE.MULT

videocam Video
7 m
Using LARGE and SMALL

videocam Video
8 m
Ranking Data with Sorting using RANK.EQ and RANK.AVG

videocam Video
5 m
The COUNTBLANK Function

videocam Video
7 m
Exercise 04

quiz Quiz
Review Questions
Section 5. Math Functions-Part1
videocam Video
7 m
Rounding Values using ROUND, ROUNDUP and ROUNDDOWN

videocam Video
5 m
Specialized Rounding (MROUND, CEILING.MATH and FLOOR.MATH)

videocam Video
5 m
Using the AGGREGATE Function

videocam Video
6 m
Exercise 05

quiz Quiz
Review Questions
Section 6. New Functions - LET and LAMBDA-Part1
videocam Video
15 m
The LET Function

videocam Video
14 m
The LAMBDA Function

videocam Video
12 m
Exercise 06

quiz Quiz
Review Questions
Section 7. Advanced PivotTables and Pivot Charts-Part1
videocam Video
10 m
Creating a Custom PivotTable Style

videocam Video
10 m
Applying Custom Number Formatting

videocam Video
5 m
Sorting Using Custom Lists

videocam Video
5 m
Applying Multiple Filters to Data

videocam Video
12 m
Adjusting Slicer Settings

videocam Video
3 m
Locking Workbooks but not Slicers

videocam Video
8 m
Creating a Calculated Field

videocam Video
7 m
Creating a Calculated Item

videocam Video
8 m
Solve Order

videocam Video
12 m
GETPIVOTDATA

videocam Video
9 m
Using GETPIVOTDATA - Dependent Data Validation Lists

videocam Video
11 m
Creating a Dynamic Pivot Chart Title

videocam Video
13 m
Adding a Dynamic Series to a Pivot Chart

videocam Video
6 m
Adding Totals to a Stacked Column Chart

videocam Video
10 m
Exercise 07

quiz Quiz
Review Questions
Section 8. Form Controls-Part1
videocam Video
4 m
Introduction to Form Controls

videocam Video
7 m
Combo Box - Select from a List

videocam Video
7 m
Check Box - Check/Uncheck Option

videocam Video
7 m
Option Button - Choose an Option

videocam Video
6 m
Spin Button - Move Up and Down a List

videocam Video
5 m
List Box - Select from a List of Items

videocam Video
5 m
Scroll Bar - Scroll Through a List

videocam Video
6 m
Exercise 08

quiz Quiz
Review Questions
Section 9. Working with Power Query-Part1
videocam Video
2 m
Power Query Overview

videocam Video
7 m
Importing Data Using Get and Transform

videocam Video
5 m
The Power Query Editor

videocam Video
5 m
Combining Files

videocam Video
10 m
Transforming Files

videocam Video
4 m
Loading Files into Excel

videocam Video
6 m
Exercise 09

quiz Quiz
Review Questions
Section 10. Forecasting-Part1
videocam Video
6 m
Getting Forecast Data with FRED

videocam Video
5 m
Creating a Sales Forecast with Seasonality

videocam Video
7 m
Adding Confidence Levels

videocam Video
7 m
Using Forecast Sheets

videocam Video
2 m
Exercise 10

quiz Quiz
Review Questions
Section 11. Macros and VBA-Part1
videocam Video
7 m
Using Macros to Automate Tasks

videocam Video
10 m
Recording, Saving and Running Macros

videocam Video
7 m
Recording a Macro with Relative Referencing

videocam Video
7 m
Multi-step Macros

videocam Video
8 m
The VBA Editor

videocam Video
7 m
Assigning Macros to Buttons

videocam Video
7 m
Creating a Custom Macro Ribbon

videocam Video
6 m
Exercise 11

quiz Quiz
Review Questions
Section 12. Practical Excel - Advanced Formulas-Part1
videocam Video
8 m
Finding the last occurrence of a value

videocam Video
6 m
Finding the closest value

videocam Video
9 m
Extract Data from the Middle of a String

videocam Video
6 m
Exercise 12

quiz Quiz
Review Questions
Section 13. Course Close-Part1
videocam Video
3 m
Course Close
Learn from the best
Simon Sez IT
Online Learning Expert & B2C Marketing Specialist

The “IT" in Simon Sez IT stands for “Information Technology." We offer instruction to help learners win when it comes to computer and software technology. Learning software skills in an easy, self-paced way for an affordable price is what Simon Sez IT is all about.

The company was founded in 2008 by Chris “Simon" Calder, Director of IT operations for one of Los Angeles' most...

Read More
Start learning now
Course
Microsoft Excel 2021/365 - Advanced Training Course
Master Microsoft Excel 2021 and fast-track your knowledge to ‘guru’ status with this advanced course!
Format: Online
Level: Advanced
Certificate: Available
Duration: 10 hours
TOTAL
US$116.22
You’ll be charged this price and get immediate access to all learning materials. Taxes are calculated at checkout.
Frequently Asked Questions
Can't find the answer you are looking for? Contact our support team

Each profession and regulatory body has its own requirement. These requirements are in a state of flux. As a result of the changing nature of professional development requirements it’s best to visit your association’s website.

Each course is created by instructor(s). The main part of each course is a set of videos that you can watch on-demand. Courses can also include quizzes, articles and supporting resources like PDF, Excel and other documents.

After completion of each course or package you will be able to download a certificate with the course title and number of hours. Certificates, as well as your study records, are stored for life and you can access them anytime.

The course starts as soon as you sign up - you will have full access to all course content and materials immediately and have up to 365 days to complete it.

Our Packages and Custom Package are subscription-based. That means after the end of the subscription period (usually a year) you will be automatically enrolled in a new Package with a new set of courses and charged the same amount. You can cancel the subscription anytime before the renewal date or within 10 days after the renewal and get a refund. No questions asked.

A Custom Package is a subscription-based service that allows you to select any courses from our platform within a specified number of total hours. Any courses that are available on the platform can be added to your Package. The Custom Package provides you with the freedom and flexibility of choosing courses for a discounted price compared to buying individual courses separately.

Unfortunately, we cannot share personal instructor information. If you have questions about a course please contact our support team.

Information is accurate as of the date the video was posted. We make every effort to ensure that videos are up to date.

In order to reset your password please use the following link: https://learnformula.com/forgetpassword. Enter the email address you used to sign up for our platform and a reset password email will be sent to this email address.

We strive to provide a broad range of content to professionals and are continually sourcing and publishing new content as it becomes available.

You will receive a “Certificate of Completion” upon viewing a course. We store your certificate on our database, so you don’t have to worry about losing it.

For your convenience, you can easily watch our videos on your phone and tablet as well as on your desktop or laptop computer. Anywhere you have an Internet connection can be a place to complete your professional development.

To register for a course, visit the course landing page, click ‘Add to Cart’. In your cart, enter your Payment Details, and click “Pay”. You can then view and access your purchased courses in the “My courses” tab in the top right header.

To be awarded the completion certificate, you must complete the course in its entirety, by viewing all videos, completing all interactive tasks and supplementary reading, and finishing all quizzes. Certain governing or accreditation bodies have further requirements. For example, to earn a IRS-registered certificate, you must complete all of the above, and additionally receive a minimum grade of 70% on the Final Exam. You should always check with your governing body to see specific requirements for your accreditation.

If you cancel within 10 days, and you’re not completely satisfied, we’ll give you all your money back. No questions asked. For more information regarding refund, complaint, and/or program cancellation policies please contact our offices at 888-987-0858. You may also reach out for support to Marionne Sumalinog at support@learnformula.com

Once you have completed all the sections of the course, the course progress will show as 100% complete, and will now appear under the Completed tab in your My Courses page. Under the course thumbnail, click "Get a certificate" to view your certificate and download it, if you wish. The certificate is also stored in our database so you don't have to worry about losing it. Certificates become available immediately after you complete the course, and are stored in our systems indefinitely.

Every course is arranged uniquely by the instructor, but they're all easy to navigate through. Once you click on your purchased course, you'll see the curriculum, made up of videos, quizzes, and/or supplementary materials. To begin the course, simply click on the first video. From there, you can click on ""Next Activity"" to move through the course, or navigate through the course using the left-hand menu. Please note that you must view all videos in their entirely, and complete all quizzes, final exams, and supplementary materials, as applicable, for the course to register as complete, and to receive your certificate.

To purchase a Team License, first, locate a package you wish to purchase for your team. This may be either a Regular Package, or a Custom Package, to allow each member of your team to curate their own package. Once you have selected a package to purchase, please enter your information and click "Sign Up". In your Shopping Cart, click "Team License (2+)" and select the number of team members you would like to purchase the package for. Enter any coupon codes, and proceed through Checkout as usual. Once you have purchased the Team License, invite all your team members! You can check up on your team's progress by clicking "Team Courses" in the top right corner of your screen.
Have Questions?
help_outline Requirements
We have compiled the requirements for various professional associations to help you identify the right courses for you.
chat Support Center
Still seeking your answer?
Let us help you directly.