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