MOS EXCEL EXPERT 2016: Course information

  • Syllabus
  • Introduction
  • Who this course is for
  • How this course is organized
  • Download the practice files
  • Prerequisites

1 Manage workbook options and settings

1.1: Manage workbooks

  • Save a workbook as a template
  • Hide or display ribbon tabs
  • Enable macros in a workbook
  • Copy macros between workbooks
  • Reference data in another workbook
  • Reference table data by using structured references

1.2: Manage workbook review

  • Restrict editing
  • Protect workbook structure
  • Encrypt a workbook with a password
  • Manage workbook versions
  • Configure formula calculation options

2 Apply custom data formats and layouts

2.1: Apply custom data formats and validation

  • Create custom data formats
  • Populate cells by using advanced Fill Series options
  • Configure data validation

2.2: Apply advanced conditional formatting and filtering

  • Create custom conditional formatting rules
  • Create conditional formatting rules that use formulas
  • Manage conditional formatting rules

2.3: Create and modify custom workbook elements

  • Create and modify cell styles
  • Create custom themes and theme elements
  • Create and modify simple macros
  • Insert and configure form controls

2.4: Prepare a workbook for internationalization

  • Change the Windows regional data format
  • Display a specific date or time in a different international format
  • Apply an international currency format


3 Create advanced formulas

3.1: Apply functions in formulas

  • Insert functions into a formula
  • Perform logical operations by using the IF, AND, OR, and NOT functions
  • Perform logical operations by using nested functions
  • Perform statistical operations by using the SUMIFS, AVERAGEIFS, and COUNTIFS functions

3.2: Look up data by using functions

  • The VLOOKUP function
  • The HLOOKUP function
  • The MATCH and INDEX functions

3.3: Apply advanced date and time functions

  • Reference the date and time by using the NOW and TODAY functions
  • Serialize numbers by using date and time functions

3.4: Perform data analysis and business intelligence

  • Import, transform, combine, display, and connect to data
  • Consolidate data
  • Perform what-if analysis by using Goal Seek and Scenario Manager
  • Use cube functions to get data out of the Excel data model
  • Calculate data by using financial functions

3.5: Troubleshoot formulas

  • Trace precedence and dependence
  • Monitor cells and formulas by using the Watch Window
  • Validate formulas by using error-checking rules
  • Evaluate formulas

3.6: Define named ranges and objects

  • Name a cell or range
  • Name a table
  • Manage named ranges and objects

4 Create advanced charts and tables

4.1: Create advanced charts

  • Add trendlines to charts
  • Create dual-axis charts
  • Save a chart as a template

4.2: Create and manage PivotTables

  • Create PivotTables
  • Modify PivotTable field selections and options
  • Create slicers
  • Group PivotTable data
  • Reference data in a PivotTable by using the GETPIVOTDATA function
  • Add calculated fields
  • Format data

4.3: Create and manage PivotCharts

  • Create PivotCharts
  • Modify PivotCharts
  • Drill down into PivotChart details