Training: Microsoft Office Specialist (MOS) – Excel Expert

Module 1: 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

Module 2: 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

Module 3: Create advanced formulas and macros

• Perform logical operations in formulas
  • perform logical operations by using nested functions including the IF(), IFS(), SWITCH(), SUMIF(), AVERAGEIF(), COUNTIF(), SUMIFS(), AVERAGEIFS(), COUNTIFS(), MAXIFS(), 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

Module 4: 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