Курс MO-201: Microsoft Excel Expert (Excel and Excel 2019)



Manage workbook options and settings
Manage workbooks
• copy macros between workbooks
• reference data in other workbooks
• enable macros in a workbook
• manage workbook versions
Prepare workbooks for collaboration
• restrict editing
• protect worksheets and cell ranges
• protect workbook structure
• configure formula calculation options
• manage comments
Use and configure language options
• configure editing and display languages
• use language-specific features
Manage and format data
Fill cells based on existing data
• fill cells by using Flash Fill
• fill cells by using advanced Fill Series options
Format and validate data
• create custom number formats
• configure data validation
• group and ungroup data
• calculate data by inserting subtotals and totals
• remove duplicate records
Apply advanced conditional formatting and filtering
• create custom conditional formatting rules
• create conditional formatting rules that use formulas
• manage conditional formatting rules
Create advanced formulas and macros
Perform logical operations in formulas
• perform logical operations by using nested functions including the IF(), IFS(), SWITCH(),
MINIFS(), AND(), OR(), and NOT() functions
Look up data by using functions
• look up data by using the VLOOKUP(), HLOOKUP(), MATCH(), and INDEX() functions
Use advanced date and time functions
• reference date and time by using the NOW() and TODAY() functions
• calculate dates by using the WEEKDAY() and WORKDAY() functions
Perform data analysis
• summarize data from multiple ranges by using the Consolidate feature
• perform what-if analysis by using Goal Seek and Scenario Manager
• forecast data by using the AND(), IF(), and NPER() functions
• calculate financial data by using the PMT() function
Troubleshoot formulas
• trace precedence and dependence
• monitor cells and formulas by using the Watch Window
• validate formulas by using error checking rules
• evaluate formulas
Create and modify simple macros
• record simple macros
• name simple macros
• edit simple macros
Manage advanced charts and tables
Create and modify advanced charts
• create and modify dual axis charts
• create and modify charts including Box & Whisker, Combo, Funnel, Histogram, Map,
Sunburst, and Waterfall charts
Create and modify PivotTables
• create PivotTables
• modify field selections and options
• create slicers
• group PivotTable data
• add calculated fields
• format data
Create and modify PivotCharts
• create PivotCharts
• manipulate options in existing PivotCharts
• apply styles to PivotCharts
• drill down into PivotChart details