Course Outline:

Excel 2016 Expert:

Module One: Manage Workbook Options and Settings

•Manage Workbooks

oSave a workbook as a template

oCopy macros between workbooks

oMange Document Versions

oReference data in another workbook

oReference data by using structured references

oEnable macros in a workbook

oDisplay hidden ribbon tabs

•Manage Workbook Review

oRestrict editing

oProtect a worksheet

oConfigure formula calculation options

oProtect workbook structure

oMange workbook versions

oEncrypt workbooks with a password

Module Two: Apply Custom Data Formats and Layouts

•Apply Custom Data Formats and Validation

oCreate custom number formats

oPopulate cells by using advanced Fill Series options

oConfigure data validation

•Apply Advanced Conditional Formatting and Filtering

oCreate custom conditional formatting rules

oCreate conditional formatting rules that use formulas

oManage conditional formatting rules

•Create and Modify Custom Workbook Elements

oCreate custom color formats

oCreate and modify cell types

oCreate and modify custom themes

oCreate and modify simply macros

oInsert and configure form controls

•Prepare a Workbook for Internationalization

oDisplay data in multiple international formats

oApply international currency formats

oManage multiple options for +Body and +Heading fonts

Module Three: Create Advanced Formulas

•Apply Functions in Formulas

oPerform logical operations by using AND, OR, and NOT functions

oPerform logical operations by using nested functions

oPerform statistical operations by using SUMIFS, AVERAGEIFS, AND COUNTIFS functions

•Look up data using Functions

oLook up data by using the VLOOKUP

oLook up data by using the HLOOKUP function

oLook up data by using the MATCH function

oLook up data by using the INDEX function

•Apply Advanced Date and Time Functions

oReference the date and time by using the NOW and TODAY functions

oSerialize numbers by using date and time functions

•Perform Data Analysis and Business Intelligence

oImport, transform, combine, display, and connect to data

oConsolidate data

oPerform what-if analysis by using Goal Seek and Scenario Manager

oUse cube functions to get data out of the Excel data model

oCalculate data by using financial functions

•Troubleshoot Formulas

oTrace precedence and dependence

oMonitor cells and formulas by using the Watch Window

oValidate formulas by using error checking values

oEvaluate formulas

oCalculate data by using financial functions

•Define Named Ranges and Objects

oName cells

oName data ranges

oName tables

oMange named ranges and objects

Module Four: Create Advanced Charts and Tables

•Create Advanced Charts

oAdd trend lines to charts

oCreate dual axis charts

oSave a chart as a template

•Create and Manage Pivot Tables

oCreate PivotTables

oModify field selections and options

oCreate slicers

oGroup PivotTable data

oReference data in a PivotTable by suing the GETPRIVOTDATA function

oAdd calculated fields

oFormat data

•Create and Manage PivotCharts

oCreate PivotCharts

oManipulate options in existing PivotCharts

oApply styles to PivotCharts

oApply Styles to PivotCharts

oManipulate options in existing PivotCharts

oApply styles to PivotCharts

oDrill down into PivotChart details