Advanced Microsoft Excel for Excel 2016, 2019, and O365

Acquire the skills for Exam MO201

4.50 (4 reviews)
Udemy
platform
English
language
IT Certification
category
17
students
7.5 hours
content
May 2021
last update
$54.99
regular price

What you will learn

Get the advanced skills you need to interpret data for insight

Apply custom data formats and validation (With practice tasks)

Apply advanced conditional formatting and filtering (with practice tasks)

Create and modify custom workbook elements (with practice tasks)

Apply functions in formulas (with practice tasks)

Lookup data by using functions (with practice tasks)

Apply advanced date and time functions (with practice tasks)

Perform data analysis and business intelligence (with practice tasks)

Troubleshoot formulas (with practice tasks)

Define named ranges and objects (with practice tasks)

Create advanced charts (with practice tasks)

Create and manage pivot tables (with practice tasks)

Fill cells based on existing data (with practice tasks)

Perform logical operations in formulas (with practice tasks)

Description

Demonstrate your expert-level skills in Microsoft Excel 2016, 2019, and O365 versions and receive the skills you need to pass the MO201 exam. We provide detailed procedure and covers the objectives of the MO201 exam and give you the skills to become the "Go-To" person among your colleagues. The last lecture of each section includes hands-on exercises to reinforced what you have learned. A review of the previous section materials before the commencement of the current section. Sharpen your skills in:

  • Pivot Tables

  • Advanced charts

  • Master conditional formatting with formulas and manage conditional formatting rules

  • Understand the management of workbook options and settings

  • Troubleshoot your formulas and protect your valuable data with cell locking. Learn to use the different troubleshooting techniques including formula evaluation, cell precedence and dependence as well as the Watch Window. Understand the fundamental principles of cell locking

  • Enhance your skills in using various functions, including the new MAXIFS() and MINIFS() functions

  • Conquer VLOOKUP/HLOOKUP functions, and see how the MATCH() and the INDEX() functions are made easy to understand

  • Master nested logical functions. Learn a simple technique to break down nested logical function with letter substitutions for complex, wordy conditions/criteria

We show you how to create Combo charts to enable you distinguish data series of on the same chart. We explore the new Map Filled Geographic chart type

  • Learn from an author of Pivot Table and Excel books (the "Get Your Hands Dirty" series)


Content

Starting with Lookup Functions

Getting started with the Lookup functions
How to move macros between workbooks
Understanding the Personal Macro Workbook
Referencing cells on other workbooks
Manage workbook versions Plus Section1 Exercises

Prepare workbook for collaboration

Learn how to protect your data by restricting editing of worksheets
Locking and unlocking cells: A demonstration
Protect worksheets with password
Protect workbook structure
Configure formula calculation options
More on formula calculation options
Managing worksheet comments

Managing and formatting data: A look at the Flash Fill and Fill Series

Review of the previous two sections and an introduction to this section
Fill cells by using Flash Fill
Fill cells by using advanced Fill Series options
Introduction to this section
Create custom number format
Configure data validation
Review of the last lecture and an introduction to this lecture
Creating conditional formatting rules
Creating conditional formatting: A demonstration
Creating custom conditional formatting with formulas
Review of last section and introduction to this section
Inserting functions into a formula
Perform logical operations by using the IF, AND, OR, and NOT functions
Performing nested logical operations
Nested functions demonstration
Perform multiple logical tests with the IFS functions
Perform logical operations with criteria
Perform logical operations with multiple criteria
Apply multiple exact match criteria to a value
Use the VLOOKUP and the HLOOKUP functions
Use the MATCH() and INDEX() functions to perform lookup operations
Advanced date and time functions
Consolidate data from multiple sources
Perform what-if analysis by using Goal Seek and Scenario Manager
Create Scenarios
Troubleshoot formulas
Add the Watch Window to monitor referenced cells
Perform error checking of formulas
Evaluate formulas to highlight errors
Create macros
Create Dual-Axis charts
Create Map charts (Geographic)
Create Pivot Tables
Create Slicers, filter data, format pivot, create calculated columns and manage

Screenshots

Advanced Microsoft Excel for Excel 2016, 2019, and O365 - Screenshot_01Advanced Microsoft Excel for Excel 2016, 2019, and O365 - Screenshot_02Advanced Microsoft Excel for Excel 2016, 2019, and O365 - Screenshot_03Advanced Microsoft Excel for Excel 2016, 2019, and O365 - Screenshot_04

Reviews

Richard
July 15, 2021
I thoroughly enjoyed this course. The lessons and materials are very easy to follow. The course has expanded my skills in excel.

Charts

Price

Advanced Microsoft Excel for Excel 2016, 2019, and O365 - Price chart

Rating

Advanced Microsoft Excel for Excel 2016, 2019, and O365 - Ratings chart

Enrollment distribution

Advanced Microsoft Excel for Excel 2016, 2019, and O365 - Distribution chart
4058580
udemy ID
5/18/2021
course created date
5/30/2021
course indexed date
Bot
course submited by