Advanced Microsoft Excel for Excel 2016, 2019, and O365

Name: Advanced Microsoft Excel for Excel 2016, 2019, and O365
Rating: 4.6 (4 reviews)

Acquire the skills for Exam MO201

Created bySamuel Osei-Tutu

Last updated 5/2021

English

What you'll 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)

Course content

13 sections • 46 lectures • 7h 30m total length

Getting started with the Lookup functions22:25
You will be able to look up operations in a data range using the VLOOKUP and the HLOOKUP functions. Overcome the limitations of the VLOOKUP/HLOOKUP functions by using the MATCH and INDEX functions together
How to move macros between workbooks9:41
You receive a workbook from a friend that contains a macro. You want what the macro does, but instead of reinventing the wheel by rewriting the macro, you learn how to move the macro from your friend's workbook into your own workbook
Understanding the Personal Macro Workbook7:48
If you want some particular macro to be available at all times irrespective of which workbook you are working on currently, place those macros in the Personal Macro Workbook (PMW). You learn how to do this
Referencing cells on other workbooks11:41
Learn to set up links to your data in another workbook to provide automatic updates from the source workbook. Configure update protocols to ensure currency of data. Learn to break links when no longer needed
Manage workbook versions Plus Section1 Exercises10:07
Learn to configure your workbook to recover data that cannot be retrieved with the UNDO feature. Then perform the exercises for this section of the course

Learn how to protect your data by restricting editing of worksheets4:29
Learn to configure worksheet protection to prevent accidental changes to data
Locking and unlocking cells: A demonstration14:58
Learn to lock cells to prevent unauthorized users from editing cells in a range
Protect worksheets with password6:58
Use a password to protect cells in a range. Configure the password to not apply to some specified group of users
Protect workbook structure3:24
Disable some functions on the ribbon and other areas of your workbook by protecting the structure of the whole workbook not just worksheets
Configure formula calculation options10:47
Learn to configure your formula calculation options to be automatic or manual to control. Learn how these options affect some categories of data and functions such as the RAND() function
More on formula calculation options10:23
More information and examples of formula calculation options
Managing worksheet comments6:44
Comments in Excel 2019 are not like the previous ones which were attached to cells. Here you learn how to manage comment threads, resolve comments, and delete them. Section exercises

Review of the previous two sections and an introduction to this section1:59
A quick review of the previous sections and an introduction to this one
Fill cells by using Flash Fill6:28
Use Flash Fill to help structure your data for your purpose, such as separating first name from last name into separate columns with only a couple of mouse-clicks. Learn the undocumented problems with these and how to solve them
Fill cells by using advanced Fill Series options13:36
Learn to use the Fill Series options to quickly fill cell ranges using a specified pattern such as Linear, Growth, and Dates. Section 3 Exercises

Introduction to this section0:41
An introduction to this section
Create custom number format12:39
Learn to create custom number formats to handle positive, negative, zero, and text entries into cells. Learn the four parts of custom data entry. Learn other formatting such as colour and others
Configure data validation20:47
Learn to configure data validation to avoid entering wrong data into cells. Configure alerts and warnings, then perform the section exercises

Review of the last lecture and an introduction to this lecture2:54
A review of the last section and an introduction to this section
Creating conditional formatting rules14:51
Create conditional formatting rules using the in-built rules.
Creating conditional formatting: A demonstration7:30
Examples of custom conditional formation
Creating custom conditional formatting with formulas12:15
Create conditional formatting using formulas

Review of last section and introduction to this section3:55
Review of previous section and introduction to this section
Inserting functions into a formula3:53
Learn to insert functions into formulas
Perform logical operations by using the IF, AND, OR, and NOT functions14:06
Perform logical operations using the logical functions
Performing nested logical operations7:37
Learn to perform logical operations with the logical functions such as IF, AND, OR, and NOT. Learn effective technique to break down complex multiple conditions such as A or (B and (C or D) and ((E and F) or G))
Nested functions demonstration10:39
Examples of the use of the logical functions
Perform multiple logical tests with the IFS functions4:36
Use the IFS function to perform multiple logical tests instead of multiple IF functions
Perform logical operations with criteria11:08
Perform calculations (SUM, AVERAGE, COUNT etc.) on only the cells in a range that meet some criterion (e. g. only Sale Reps who come from city X)
Perform logical operations with multiple criteria17:03
Perform multi-criteria operations with the *IFS function (SUMIF, COUNTIF...)
Apply multiple exact match criteria to a value4:08
Use the SWITCH() function to lookup data in a list

Requirements

At least six months of Excel 2016, 2019, or Office 365
Be able to perform the following functions: Create worksheets; Add worksheets to existing workbooks; Copy and move worksheets
Be able to insert and delete cells, columns and rows; Customize the Quick Access Toolbar (QAT) and the ribbon; Freeze panes and split windows
Be able to set print areas and add header and footers; Change fonts and cell styles; Wrap text within cells; Create and edit tables
Be able to use relative, mixed, and absolute cell references; Use functions; Create and edit charts, and add data series to charts
Be able to insert text boxes, SmartArt, and images to worksheet; Apply style and effects to objects; Position objects

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)

Who this course is for:

This course is NOT for basic excel users. We do not teach how to copy and paste in Excel. We do not teach navigation in Excel. We do not teach how to write simple formulas
Ideal for Excel users who need guidance and help to explore the more advanced features of Excel such as Pivot Tables, Conditional formatting, Data Validation etc.
Ideal for users interested in using advanced Excel formulas such as INDEX, MATCH, VLOOKUP and how to combine these functions to solve difficult data analysis tasks
Apart from Pivot Charts, we treat all the topics required by exam MO-201: Excel and Excel 2019 Expert

Advanced Microsoft Excel for Excel 2016, 2019, and O365

What you'll learn

Explore related topics

Course content

Starting with Lookup Functions5 lectures • 1hr 2min

Prepare workbook for collaboration7 lectures • 58min

Managing and formatting data: A look at the Flash Fill and Fill Series3 lectures • 22min

Format and validate data3 lectures • 34min

Apply advanced conditional formatting and filtering4 lectures • 38min

Create advanced formulas and macros9 lectures • 1hr 17min

Lookup data by using functions: VLOOKUP, HLOOKUP, INDEX, and MATCH functions2 lectures • 42min

Apply advanced date and time functions1 lecture • 18min

Perform data analysis3 lectures • 26min

Troubleshoot formulas4 lectures • 27min

Requirements

Description

Who this course is for: