Excel 2019 Expert

Video Mapping to MO-201

Video or article title Topics covered
Duration Exam Objectives
Copy a macro module N/A • Overview of Macros and VBA 1.1.1
to another workbook • Copying a module from one
workbook to another
Create an external N/A • Create an external reference 1.1.2
reference (link) to a between cells in different
cell range in another workbooks
workbook • Create an external reference to
a defined name in another
workbook
• Define a name that contains an
external to cells in another
workbook
• Learn more about external
references
• Where external references can
be used effectively
• Ways to create external
references
• What an external reference to
another workbook looks like
Enable or disable N/A • Enable macros when the 1.1.3
macros in Office files message bar appears
• Enable macros in the backstage
view
• Enable macros for one time
when the security warning
appears
• Change macros setting in the
trust center
• Macro setting explained
• What is a macro, who makes
them, and what is the security
risk?
View previous N/A • View historical versions 1.1.4
versions of Office files
Restrict changes to N/A • Restrict changes in Word 1.2.1
files in Word and • Restrict changes in Excel
Excel
Lock or unlock N/A • Lock only specific cells and 1.2.2
specific areas of a ranges in a protected worksheet
protected worksheet • Unlock ranges on a protected
worksheet for users to edit
Protect a workbook N/A • Protect the workbook structure 1.2.3
Change formula N/A • Change when a worksheet or 1.2.4
recalculation, workbook recalculates 1.2.5
iteration, or precision • Recalculate a worksheet or
in Excel workbook manually by using
keyboard shortcuts
• Change the number of times
Excel iterates a formula
• Change the precision of
calculations in a workbook
• Learn more about precision in
Excel
• Change the number of
processors used to calculate
formulas
• Learn about calculating
workbooks that were created in
an earlier version of Excel
Annotate a worksheet [Link] • Comments vs Notes 1.2.6
by using comments • Add a comments or notes
• Review comments or notes
• Show/Hide all comments or
notes
• Move a note
• Resize a note
• Delete a comments or notes
• Resolve a thread
• Convert to comments

Add an editing N/A • Set office language preferences 1.3.1

language or set • Add a language
language preferences • Set the default display and help
in Office languages
• Set the ScreenTip language
Switch between N/A • Using the language bar to 1.3.2
languages using the switch keyboard layouts
Language bar • I don’t see the language bar
• Check spelling and grammar in a
different language

Using Flash Fill in [Link] • Use flash fill 2.1.1

Excel
Automatically N/A • Fill a column with a series of 2.1.2
number rows numbers
• Use the ROW function to
number rows
 • Display or hide the fill handle
Create a custom [Link] • Custom number formats 2.2.1
number format
Apply data validation [Link] • Validate cell data 2.2.2
to cells
Outline (group) data N/A • Create an outline of rows 2.2.3
in a worksheet • Create an outline of columns
• Show or hide outlined data
• Customize an outline with styles
• Copy outlined data
• Hide or remove an outline
• Create a summary report with a
chart
Insert subtotals in a N/A • Subtotals and grand totals 2.2.4
list of data in a • Insert subtotals
worksheet • Remove subtotals
Filter for unique N/A • Learn about filtering for unique 2.2.5
values or remove values or removing duplicate
duplicate values values
• Filter for unique values
• Remove duplicate values
• Problems removing duplicates
from outlined or subtotaled data
• Conditionally format unique or
duplicate values
Conditional [Link] • Create conditional formatting 2.3.1
formatting
Use formulas with N/A • Create conditional formatting 2.3.2
conditional rules with formula
formatting
Manage conditional N/A • Edit conditional formatting rule 2.3.4
formatting rule precedence
precedence • Learn about conditional
formatting rule precedence
• What happens when more than
one conditional formatting rule
evaluates to True
• How pasting, filling, and the
Format Painter affect
conditional formatting rules
• What happens when a
conditional format and a manual
format conflict
• Controlling when rule
evaluation stops by using the
Stop If True check box
Use nested functions N/A • Insert nested function 3.1.1
in a formula • Examples
Excel functions N/A • Alphabetical list of Excel 3.1.1
functions
VLOOKUP [Link] • VLOOPUP 3.2.1
Look up values in a N/A • Look up values vertically in a list 3.2.1
list of data by using an exact match
• Look up values vertically in a list
by using an approximate match
• Look up values vertically in a list
of unknown size by using an
exact match
• Look up values horizontally in a
list by using an exact match
• Look up values horizontally in a
list by using an approximate
match
• Create a lookup formula with
the Lookup Wizard (Excel 2007
only)
TIME function N/A • Description N/A
• Syntax
• Remark
• Example
Insert the current N/A • Insert a static date or time 3.3.1
date and time in a cell • Change the date or time format
• Insert a date or time whose
value is updated
NOW function N/A • Description 3.3.1
• Syntax
• Remarks
• Example
WEEKDAY function N/A • Description 3.3.1
• Syntax
• Remarks
• Example
WORKDAY function N/A • Description 3.3.2
• Syntax
• Remark
• Example
Consolidate data in N/A • Overview 3.4.1
multiple worksheets • Ways to consolidate data
• How to consolidate
• Use a formula to consolidate
data
Introduction to What- N/A • Overview 3.4.2
If Analysis • Use scenarios to consider many
different variables
• Use Goal Seek to find out how
to get a desired result
• Use Data Tables to see the
effects of one or two variables
on a formula
• Prepare forecasts and advanced
business models
3.4.3
PMT function N/A • Description 3.4.4
• Syntax
• Remark
• Example
Display the N/A • Precedent cells 3.5.1
relationships • Dependent cells
between formulas
and cells
Watch a formula and N/A • Add cells to the Watch Window 3.5.2
its result by using the • Remove cells from the Watch
Watch Window Window
Detect errors in N/A • Correct common errors when 3.5.3
formulas entering formulas 3.5.4
• Correct common problems in
formulas
• Turn error checking rules on or
off
• Correct common formula errors
one by one
• Correct common formula errors
individually
• Correct a # error value
• Watch a formula and its result
by using the Watch Window
• Evaluate a nested formula one
step at a time
Quick start: Create a N/A • Before you record a macro 3.6.1
macro • Record a macro
• Take a closer look at the macro
Automate tasks with N/A • Record a macro 3.6.2
the Macro Recorder • Working with recorded macros
in Excel
• Working with recorded code in
the Visual Basic Editor
Edit a macro N/A • Change macro security settings 3.6.3
• Edit the macro
Create a PivotTable to [Link] • Create a PivotTable 4.2.1
analyze worksheet • Building out your PivotTable
data
Overview of N/A • Ways to work with a PivotTable 4.2.1
PivotTables • About PivotCharts
and PivotCharts • Differences
between PivotCharts and
standard charts
• Creating a PivotTable or
PivotChart from worksheet data
• Using an external data source to
create a PivotTable or
PivotChart
• Using another PivotTable as a
data source
• Changing the source data of an
existing PivotTable
Use the Field List to N/A • Work with PivotTable Fields 4.2.2
arrange fields in a
PivotTable
Make your first N/A • Hands-on practice in a 4.2.1
PivotTable template downloadable Excel spreadsheet 4.2.2
(download)
Use the Field List to • Add and rearrange field in the 4.2.2
arrange fields in a Field List
PivotTable
Use slicers to filter [Link] • Create a slicer to filter data 4.2.3
data • Slicer components

Filter data in a [Link] • Filter data in a PivotTable N/A

PivotTable • Filter data manually
Group or ungroup [Link] • Group data 4.2.4
data in a PivotTable • Group selected items
• Name a group
• Ungroup grouped data
Calculate values in a N/A • Create formulas in a PivotTable 4.2.5
PivotTable • View all formulas that are used
in a PivotTable
• Edit a PivotTable formula

Design the layout and N/A • Change the payout form of a 4.2.6
format of a PivotTable
PivotTable • Change the field arrangement in
a PivotTable
 • Change the layout of columns,
rows, and subtotals
• Change the display of blank
cells, blank lines, and errors
• Change or remove formatting
Create a PivotChart [Link] • Create a PivotChart 4.3.1
• Create a chart from a PivotTable
Pivot data in a N/A • Working with the PivotTable 4.3.2
PivotTable or Field List 4.3.3
PivotChart • How the PivotTable Field List
works
• Guidelines for moving fields in
the PivotTable Field List
• Changing the PivotTable Field
List view
• Add fields to a PivotTable or
PivotChart
• Add the fields you want to
display
• Add multiple copies of a field in
the Values area
• Filter data before you add fields
• Rearrange fields in the
PivotTable or PivotChart
• Remove fields from the
PivotTable or PivotChart
• Switch between automatic and
manual updating of the report
layout
Drill into PivotTable N/A • Drill down 4.3.4
data • Drill up to explore big picture
details