Course Overview
This course teaches students about working with multiple worksheets and workbooks, using
lookup functions and formula auditing, sharing and protecting workbooks, automating
workbook functionality, creating Sparklines and mapping data, and forecasting data.
Course Introduction 3m
Course Introduction
Chapter 01 – Working with Multiple Worksheets and Workbooks 24m
Topic A: Use Links and External References
Linked Cells
The Edit Links Dialog Box
External References in Formulas and Functions
Demo – Creating Links
Topic B: Use 3-D References
Grouped Worksheets
3-D References
3-D References in Summary Functions
Demo – Using 3-D References
Topic C: Consolidate Data
Data Consolidation
The Consolidate Dialog Box
Demo – Consolidating Data
Chapter 01 Review
Chapter 02 – Using Lookup Functions and Formula Auditing 31m
Topic A: Use Lookup Functions
The VLOOKUP Function
Demo – Using Lookup Functions
Topic B: Trace Cells
Precedent and Dependent Cells
The Trace Precedents and Trace Dependents Commands
Trace Arrows
The Go To Dialog Box and Trace Arrows
The Go To Special Dialog Box
Demo – Tracing Precedent and Dependent Cells
Topic C: Watch and Evaluate Formulas
The Watch Window
The Evaluate Formula Dialog Box
Demo – Watching and Evaluating Formulas
Chapter 02 Review
Chapter 03 – Sharing and Protecting Workbooks 28m
Topic A: Collaborate on a Workbook
Comments
Shared Workbooks
Change Tracking
The Highlight Changes Dialog Box
The Select Changes to Accept or Reject Dialog Box
The Accept or Reject Changes Dialog Box
The Compare and Merge Workbooks Command
The Share Options
Microsoft Accounts and OneDrive
Excel Online
The Accessibility Checker
Demo – Collaborating on a Workbook
Topic B: Protect Worksheets and Workbooks
Worksheet and Workbook Element Protection
The Protect Sheet Command
The Protect Workbook Command
The Protect Workbook Options
The Document Inspector
Demo – Protecting Worksheets and Workbooks
Chapter 03 Review
Chapter 04 – Automating Workbook Functionality 36m
Topic A: Apply Data Validation
The Data Validation Dialog Box
Data Validation Criteria
The Input Message Tab
The Error Alert Tab
Demo – Applying Data Validation
Topic B: Search for Invalid Data and Formulas with Errors
The Circle Invalid Data Command
The Error Checking Dialog Box
Topic C: Work with Macros
Macro Security Settings
Microsoft Visual Basic for Applications
The Record Macro Dialog Box
The Macro Dialog Box
The Personal Workbook
Demo – Creating and Editing a Macro
Chapter 04 Review
Chapter 05 – Creating Sparklines and Mapping Data 15m
Topic A: Create Sparklines
Sparklines
The Create Sparklines Dialog Box
The Sparkline Tools Contextual Tab
Demo – Creating Sparklines
Topic B: Map Data
3D Maps
3D Map Layer Pane
Launch 3D Maps Dialog Box
Demo – Creating a 3-D Map
Chapter 05 Review
Chapter 06 – Forecasting Data 28m
Topic A: Determine Potential Outcomes Using Data Tables
What-If Analysis
One-Variable Data Tables
Two-Variable Data Tables
The Data Table Dialog Box
Demo – Determining Potential Outcomes Using Data Tables
Topic B: Determine Potential Outcomes Using Scenarios
Scenarios
The Scenario Manager Dialog Box
The Add Scenario Dialog Box
The Scenario Values Dialog Box
The Scenario Command
Demo – Determining Potential Outcomes by Using Scenarios
Topic C: Use the Goal Seek Feature
The Goal Seek Dialog Box
Demo – Using Goal Seek Feature
Topic D: Forecasting Data Trends
The Create Forecast Worksheet Dialog Box
Demo – Forecasting Data with the Forecast Sheet
Chapter 06 Review
Course Closure
Total Duration: 2h 45m