Microsoft Excel 2016 Expert

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.

‌‌‌ Online Course

Microsoft Excel 2016 Expert – Course Outline:

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: Getting Started

• Workshop Objectives

Module Two: Manage Workbook Options and Settings

• Manage Workbooks
• Save a Workbook as a Template
• Reference Data in Another Workbook
• Reference Data by Using Structured References
• Display Hidden Ribbon Tabs
• Manage Workbook Review
• Mark a Workbook as Final
• Protect a Workbook with a Password
• Protect a Worksheet to Restrict Editing
• Protect Workbook Structure
• Manage Workbook Versions
• Setting Calculation Options
• Module Two: Review Questions

Module Three: Apply Custom Data Formats and Layouts

• Prepare a Workbook for Internationalization
• Apply International Currency Formats
• Apply Locale to Date or Time Formats
• 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 Custom Font Sets
• Create and Modify Cell Styles
• Create and Modify Custom Themes
• Manage Multiple Options for Theme Fonts
• Insert and Configure Form Controls
• Work with Macros
• Enable Macros
• Record a Macro
• Run a Macro
• Edit a Macro
• Copy Macros from One Workbook to Another
• Module Three: Review Questions

Module Four: Create Advanced Formulas

• Define Named Ranges and Objects
• Name Cells
• Use Named Cells in a Formula
• Manage Named Ranges and Objects
• Apply Functions in Formula
• Perform the AND Function and the OR Function
• Perform NOT Function
• Perform Logical Operations by Using Nested Functions
• Perform SUMIFS, AVERAGEIFS, and COUNTIFS Functions
• Look Up Data by Using 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
• Apply Advanced Time and Date Functions
• Serialize Numbers by Using Date and Time Functions
• Reference the Date and Time Using the NOW and TODAY functions
• Perform Data Analysis and Business Intelligence
• Import, Transform, Combine, Display, and Connect to Data
• Consolidate Data
• Perform What-If Analysis by Using Goal Seek
• Perform What-If Analysis by Using Scenario Manager
• Troubleshoot Formulas
• Trace Precedence and Dependence
• Monitor Cells and Formulas using the Watch Window
• Validate Formulas by Using Error Checking Rules
• Evaluate Formulas
• Module Four Review Questions

Module Five: 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 PivotTables
• Create Slicers
• Create PivotTables
• Modify Field Selections and Options
• Group PivotTable Data
• Reference Data in a PivotTable by Using the GETPIVOTDATA Function
• Add Calculated Fields
• Format Data
• Create and Manage PivotCharts
• Create PivotCharts
• Drill Down into PivotChart Details
• Apply Styles to PivotCharts
• Manipulate Options in Existing PivotCharts
• Module Five Review Questions

Module Six: Wrapping Up

• Words from the Wise