Participants will gain an advanced level of understanding for the Microsoft Excel environment, and the ability to guide others to the proper use of the program's full features - critical skills for those in roles such as accountants, financial analysts, and commercial bankers.Participants will create, manage, and distribute professional spreadsheets for a variety of specialized purposes and situations. They will customize their Excel 2016 environments to meet project needs and increase productivity. Expert workbook examples include custom business templates, multi-axis financial charts, amortization tables, and inventory schedules. Module One: Manage Workbook Options and Settings• Manage Workbookso Save a workbook as a templateo Copy macros between workbookso Mange Document Versionso Reference data in another workbooko Reference data by using structured referenceso Enable macros in a workbooko Display hidden ribbon tabs• Manage Workbook Reviewo Restrict editingo Protect a worksheeto Configure formula calculation optionso Protect workbook structureo Mange workbook versionso Encrypt workbooks with a passwordModule Two: Apply Custom Data Formats and Layouts• Apply Custom Data Formats and Validationo Create custom number formatso Populate cells by using advanced Fill Series optionso Configure data validation• Apply Advanced Conditional Formatting and Filteringo Create custom conditional formatting ruleso Create conditional formatting rules that use formulaso Manage conditional formatting rules• Create and Modify Custom Workbook Elementso Create custom color formatso Create and modify cell typeso Create and modify custom themeso Create and modify simply macroso Insert and configure form controls• Prepare a Workbook for Internationalizationo Display data in multiple international formatso Apply international currency formatso Manage multiple options for +Body and +Heading fontsModule Three: Create Advanced Formulas• Apply Functions in Formulaso Perform logical operations by using AND, OR, and NOT functionso Perform logical operations by using nested functionso Perform statistical operations by using SUMIFS, AVERAGEIFS, AND COUNTIFS functions• Look up data using Functionso Look up data by using the VLOOKUPo Look up data by using the HLOOKUP functiono Look up data by using the MATCH functiono Look up data by using the INDEX function• Apply Advanced Date and Time Functionso Reference the date and time by using the NOW and TODAY functionso Serialize numbers by using date and time functions• Perform Data Analysis and Business Intelligenceo Import, transform, combine, display, and connect to datao Consolidate datao Perform what-if analysis by using Goal Seek and Scenario Managero Use cube functions to get data out of the Excel data modelo Calculate data by using financial functions• Troubleshoot Formulaso Trace precedence and dependenceo Monitor cells and formulas by using the Watch Windowo Validate formulas by using error checking valueso Evaluate formulaso Calculate data by using financial functions• Define Named Ranges and Objectso Name cellso Name data rangeso Name tableso Mange named ranges and objectsModule Four: Create Advanced Charts and Tables• Create Advanced Chartso Add trend lines to chartso Create dual axis chartso Save a chart as a template• Create and Manage Pivot Tableso Create PivotTableso Modify field selections and optionso Create slicerso Group PivotTable datao Reference data in a PivotTable by suing the GETPRIVOTDATA functiono Add calculated fieldso Format data• Create and Manage PivotChartso Create PivotChartso Manipulate options in existing PivotChartso Apply styles to PivotChartso Apply Styles to PivotChartso Manipulate options in existing PivotChartso Apply styles to PivotChartso Drill down into PivotChart details
