Microsoft Excel 2016/2019/O365 - 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

VLOOKUP Review

Working with Multiple Worksheets and 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

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

VLOOKUP Review

Working with Multiple Worksheets and 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