Course Summary:

Welcome to the Excel 2016 Specialist workshop. Excel can help you interpret data for insights.

This course covers workbook options and settings, custom data formats and layouts, advanced formulas and advanced charts and tables that can help you become an Excel expert.

Course Objectives:

Research has consistently demonstrated that when clear goals are associated with learning, it occurs more easily and rapidly. With that in mind, let’s review our goals for today. 
At the end of this workshop, participants should be able to:

• Save a workbook as a template, with colors, fonts, cell styles and themes
• Reference data using structured references or data in another workbook
• Protect a workbook from further editing
• Prepare a workbook for internationalization
• Apply custom data formats and validation
• Apply advanced conditional formatting and filtering
• Use form controls
• Work with macros
• Define and use named ranges
• Apply functions in formulas using logical functions, math & trig functions, statistical functions and date & time functions
• Get & transform data
• Perform what if analysis
• Troubleshoot formulas
• Create advanced charts
• Create and manage PivotTables
• Create and manage PivotCharts

Course Outline

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 Formulas 
• 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