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.

Completing this course will confer 2 CPD Credits towards internationally recognised Continuous Professional Development Requirements within organisations operating this kind of staff development methodology.

Course Outline

Module One: Manage Workbook Options and Settings

• Manage Workbooks
  • Save a workbook as a template
  • Copy macros between workbooks
  • Mange Document Versions
  • Reference data in another workbook
  • Reference data by using structured references
  • Enable macros in a workbook
  • Display hidden ribbon tabs
• Manage Workbook Review
  • Restrict editing
  • Protect a worksheet
  • Configure formula calculation options
  • Protect workbook structure
  • Mange workbook versions
  • Encrypt workbooks with a password

Module Two: Apply Custom Data Formats and Layouts

• Apply Custom Data Formats and Validation
  • Create custom number formats
  • Populate cells by using advanced Fill Series options
  • Configure data validation
• Apply Advanced Conditional Formatting and Filtering
  • Create custom conditional formatting rules
  • Create conditional formatting rules that use formulas
  • Manage conditional formatting rules
• Create and Modify Custom Workbook Elements
  • Create custom color formats
  • Create and modify cell types
  • Create and modify custom themes
  • Create and modify simply macros
  • Insert and configure form controls
• Prepare a Workbook for Internationalization
  • Display data in multiple international formats
  • Apply international currency formats
  • Manage multiple options for +Body and +Heading fonts

Module Three: Create Advanced Formulas

• Apply Functions in Formulas
  • 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
• Look up data using Functions
  • Look up data by using the VLOOKUP
  • Look up data by using the HLOOKUP function
  • Look up data by using the MATCH function
  • Look up data by using the INDEX function
• 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
• Perform Data Analysis and Business Intelligence
  • Import, transform, combine, display, and connect to data
  • Consolidate data
  • Perform what-if analysis buy suing Goal Seek and Scenario Manager
  • Use cube functions to get data out of the Excel data model
  • Calculate data by using financial functions
• Troubleshoot Formulas
  • Trace precedence and dependance
  • Monitor cells and formulas by using the Watch Window
  • Validate formulas by using error checking values
  • Evaluate formulas

• Calculate data by using financial functions

• Define Named Ranges and Objects
  • Name cells
  • Name data ranges
  • Name tables
  • Mange named ranges and objects

Module Four: Create Advanced Charts and Tables

• Create Advanced Charts
  • Add trendlines to charts
  • Create dual axis charts
  • Save a chart as a template
• Create and Manage Pivot Tables
  • Create PivotTables
  • Modify field selections and options
  • Create slicers
  • Group PivotTable data
  • Reference data in a PivotTable by suing the GETPRIVOTDATA function
  • Add calculated fields
  • Format data
• Create and Manage PivotCharts
  • Create PivotCharts
  • Manipulate options in existing PivotCharts
  • Apply styles to PivotCharts
  • Apply Styles to PivotCharts
  • Manipulate options in existing PivotCharts
  • Apply styles to PivotCharts
  • Drill down into PivotChart details