Microsoft Excel O365/2019 - Level 3 / Adv

Module 1: 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

Module 2: 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

Module 3: 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

Module 4: 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

Module 5: 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

Module 6: 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

Students will learn,

• Enhancing Workbooks
• Auditing Worksheets
• Working with Multiple Worksheets and Workbooks
• Exporting Excel Data
• Analyzing Data with PivotTables, Slicers, and PivotChart
• Automating Worksheet Functionality

To meet these prerequisites, students can take the following:

• Microsoft™ Office Excel™ Level 1 / Intro
• Microsoft™ Office Excel™ Level 2/ Intermediate

To ensure success, students should have practical, real-world experience creating and analyzing datasets using Excel 2019/O365. Specific tasks students should be able to perform include: creating formulas and using Excel functions; creating, sorting, and filtering datasets and tables and presenting data by using basic charts.