Microsoft Excel 2016/2019 - Level 3 / Advanced

Enhancing Workbooks

• Customize Workbooks
  • Comments
  • Hyperlinks
  • Watermarks
  • Background Pictures
• Manage Themes
  • About Themes
  • Customizing Themes
• Create and Use Templates
  • Templates
  • Template Types
  • Creating a Template
  • Modifying a Template
• Protect Files
  • Recovering Lost Data
  • The Changes Group
  • Worksheet and Workbook Protection
  • The Protect Worksheet Option
  • The Protect Workbook Option
• Preparing a Workbook for Multiple Audiences
  • Displaying Data in Multiple International Formats
  • Utilize International Symbols
  • Modifying Worksheets Using the Accessibility Checker
  • Managing Fonts

Auditing Worksheets

• Trace Cells
  • The Trace Cells Feature
  • Tracer Arrows
• Troubleshoot Invalid Data and Formula Errors
  • Invalid Data
  • The Error Checking Command
  • Error Types
• Watch and Evaluate Formulas
  • The Watch Window
  • Formula Evaluation
• Create a Data List Outline
  • Outlines
  • The Outline Group

Working with Multiple Workbooks

• Consolidate Data
  
  • Data Consolidation
  • The Consolidate Dialog Box
  • Consolidation Functions
• Link Cells in Different Workbooks
  • External References
• Merge Workbooks
  • The Compare and Merge Workbooks Feature

Exporting Excel Data

• Export Excel Data
  • The Export Process
• Import a Delimited Text File
  • The Import Process
  • The Get External Data Group
  • Delimited Text Files
  • Methods of Importing Text Files
• Integrate Excel Data with the Web
  • The File Publishing Process
  • Publish as Web Page Dialog Box
• Create a Web Query
  • Web Queries
  • The New Web Query Dialog Box

Analyzing Data with PivotTables, Slicers, and PivotCharts

• Create a PivotTable
  • PivotTables
  • Start with Questions, End with Structure
  • The Create PivotTable Dialog Box
  • The PivotTable Fields Pane
  • Summarize Data in a PivotTable
  • The “Show Values As” Functionality of a PivotTable
  • External Data
  • PowerPivot
  • PowerPivot Functions
• Filter Data by Using Slicers
  • Slicers
  • The Insert Slicers Dialog Box
• Analyze Data with PivotCharts
  • PivotCharts
  • Creating PivotCharts
  • Applying a Style to a PivotChart

Automating Worksheet Functionality

• Update Workbook Properties
  
  • Workbook Properties
• Create and Edit a Macro
  • Macros
  • The Record Macro Dialog Box
  • Naming Macros
  • Visual Basic for Applications
  • Copying Macros Between Workbooks
  • Macro Security Settings
• Apply Conditional Formatting
  • Conditional Formatting
  • Conditional Formats
  • The Conditional Formatting Rules Manager Dialog Box
  • The New Formatting Rule Dialog Box
  • Clear Rules
• Add Data Validation Criteria
  • Data Validation
  • The Data Validation Dialog Box

• Enhancing Workbooks
• Auditing Worksheets
• Working with Multiple Worksheets and Workbooks
• Exporting Data
• Automating Worksheets
• Keyboard Shortcut Quick Reference Sheet