MOS Excel Expert E-Learning

• Save a workbook as a template
• Copy macros between workbooks
• Reference data in another workbook
• Reference data by using structured references
• Enable macros in a workbook
• Display hidden ribbon tabs

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

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

Create & Modify Custom Workbook Elements

• Create custom color formats
• Create and modify cell styles
• Create and modify custom themes
• Create and modify simple macros
• Insert and configure form controls

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

Prepare a Workbook for Internationalisation

• Display data in multiple international formats
• Apply international currency formats
• Manage multiple options for +Body and +Heading fonts

• Perform logical operations by using AND, OR, and NOT functions
• Perform logical operations by using nested functions
• Perform statistical operations by using SUMIFS, AVERAGEIFS, and COUNTIFS functions

Perform Data Analysis & Business Intelligence

• Reference the date and time by using the NOW and TODAY functions
• 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

Apply Advanced Date & Time Functions 

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

• Look up data by using the VLOOKUP function
• Look up data by using the HLOOKUP function
• Look up data by using the MATCH function
• Look up data by using the INDEX 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

Define Named Ranges & Objects

• Name cells
• Name data ranges
• Name tables
• Manage named ranges and objects

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

Create & Manage Pivot Charts 

• Create PivotCharts
• Manipulate options in existing PivotCharts
• Apply styles to PivotCharts
• Drill down into PivotChart details

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