Autoplay
Autocomplete
Previous Lesson
Complete and Continue
Microsoft Office Excel 2019: Advanced
Course Introduction
Course Introduction (2:22)
Instructor Introduction (1:21)
Courseware (0:15)
Working with Multiple Worksheets and Workbooks
Section Introduction (0:53)
Topic A: Use Links and External References (0:39)
Linked Cells (0:45)
External Link Syntax (1:29)
The Edit Links Dialog Box (1:14)
External References in Formulas and Functions (0:46)
Demo - Creating Links (8:01)
Topic B: Use 3-D References (0:53)
Grouped Worksheets (0:49)
3-D References (1:11)
3-D References in Summary Functions (1:19)
Demo - Using 3-D References (2:15)
Topic C: Consolidate Data (0:50)
Data Consolidation (1:07)
Consolidated Dataset with Linked Source Data (0:18)
The Consolidate Dialog Box (1:02)
Demo - Consolidating Data (5:34)
Chapter 01 Review (0:41)
Chapter 01 Quiz
Sharing and Protecting Workbooks
Section Introduction (1:05)
Topic A: Collaborate on a Workbook (0:47)
Comments (0:44)
The Compare and Merge Workbooks Command Part1 (0:56)
The Compare and Merge Workbooks Command Part2 (0:42)
The Share Options (1:19)
Microsoft Accounts and OneDrive (1:20)
The PDF and XPS File Formats (1:38)
Accessibility Options (1:23)
The Accessibility Checker Part1 (0:33)
The Accessibility Checker Part2 (0:23)
Demo - Collaborating on a Workbook (6:54)
Topic B: Protect Worksheets and Workbooks (0:36)
Worksheet and Workbook Element Protection (1:17)
The Protect Sheet Command (0:17)
The Protect Workbook Command (0:27)
The Protect Workbook Options (1:19)
Metadata (0:26)
The Document Inspector (0:36)
Demo - Protecting Worksheets and Workbooks (3:43)
Chapter 02 Review (0:25)
Chapter 02 Quiz
Automating Workbook Functionality
Section Introduction (0:51)
Topic A: Apply Data Validation (0:43)
Data Validation (1:05)
The Data Validation Dialog Box (0:23)
Data Validation Criteria (1:56)
The Input Message Tab (0:33)
The Error Alert Tab (1:00)
Demo - Applying Data Validation (5:03)
Topic B: Search for Invalid Data and Formulas with Errors (0:38)
Invalid Data (0:16)
The Circle Invalid Data Command (0:28)
The Error Checking Dialog Box (1:27)
Error Types (2:27)
Demo - Searching for Invalid Data and Formulas with Errors (2:01)
Topic C: Work with Macros (0:47)
Macros (1:20)
Macro Security Settings (1:20)
VBA (0:42)
VBE (0:50)
The Record Macro Dialog Box (0:25)
The Macro Dialog Box (0:33)
Macro Names (0:30)
The Personal Workbook (0:35)
Demo - Creating a Macro (3:33)
Demo - Editing a Macro (1:58)
Chapter 03 Review (0:29)
Chapter 03 Quiz
Using Lookup Functions and Formula Auditing
Section Introduction (1:19)
Topic A: Use Lookup Functions (0:32)
Lookup Functions (1:09)
The VLOOKUP Function (2:08)
The HLOOKUP Function (2:40)
The MATCH Function (2:01)
The INDEX Function (1:46)
The TRANSPOSE Function (1:23)
Demo - Using Lookup Functions (7:25)
Topic B: Trace Cells (0:52)
Precedent and Dependent Cells (1:08)
The Trace Precedents and Trace Dependents Commands (0:50)
Trace Arrows (1:00)
The Go To Dialog Box and Trace Arrows (0:36)
The Go To Special Dialog Box (0:22)
The Remove Arrows Options (0:31)
Demo - Tracing Precedent and Dependent Cells (2:54)
Topic C: Watch and Evaluate Formulas (0:59)
The Watch Window (1:14)
Formula Evaluation (0:47)
The Evaluate Formula Dialog Box (0:41)
Demo - Watching and Evaluating Formulas (3:24)
Chapter 04 Review (0:44)
Chapter 04 Quiz
Forecasting Data
Section Introduction (1:15)
Topic A: Determine Potential Outcomes Using Data Tables (0:36)
What-If Analysis (0:51)
Data Tables (1:19)
One-Variable Data Tables (0:56)
Two-Variable Data Tables (1:00)
The Data Table Dialog Box (0:22)
Demo - Determining Potential Outcomes Using Data Tables (4:39)
Topic B: Determine Potential Outcomes Using Scenarios (0:29)
Scenarios (1:17)
The Scenario Manager Dialog Box (1:02)
The Add Scenario Dialog Box (0:29)
The Scenario Values Dialog Box (0:30)
The Scenario Command (0:29)
Demo - Determining Potential Outcomes Using Scenarios (7:07)
Topic C: Use the Goal Seek Feature (0:24)
The Goal Seek Feature (0:49)
The Goal Seek Dialog Box (0:16)
Iterative Calculations (0:39)
Demo - Using the Goal Seek Feature (2:11)
Topic D: Forecast Data Trends (0:25)
The Create Forecast Worksheet Dialog Box (1:35)
Demo - Forecasting Data with the Forecast Sheet (1:37)
Chapter 05 Review (0:30)
Chapter 05 Quiz
Creating Sparklines and Mapping Data
Section Introduction (0:54)
Topic A: Create Sparklines (0:31)
Sparklines (1:09)
Sparkline Types (0:43)
The Create Sparklines Dialog Box Part1 (0:17)
The Create Sparklines Dialog Box Part2 (0:56)
Excel2019P3_06.01.05 (2:33)
Topic B: Map Data (0:26)
3D Maps (0:36)
Data Structuring (0:46)
3D Map Layer Pane (1:01)
Tours (0:20)
Demo - Creating a 3D Map (6:21)
Chapter 06 Review (0:25)
Chapter 06 Quiz
Importing and Exporting Data
Section Introduction (0:26)
Topic A: Import and Export Data (0:14)
Importing Data (0:41)
Exporting Data (0:39)
Methods of Importing Text Files (0:30)
The Text Import Wizard
The New Web Query Dialog Box (0:18)
XML (0:25)
XML Schemas (0:27)
XML Maps (0:35)
The XML Source Task Pane (0:16)
The XML Maps Dialog Box (0:19)
Chapter 07 Review (0:23)
Chapter 07 Quiz
Internationalizing Workbooks
Section Introduction (0:45)
Topic A: Internationalize Workbooks (0:17)
Worksheet and Worksheet Tab Direction (0:36)
The Symbol Dialog Box (0:19)
International Number Formatting (0:31)
Chapter 08 Review (0:18)
Chapter 08 Quiz
Working with Power Pivot
Section Introduction (0:33)
Topic A: Work with Power Pivot (0:18)
Power Pivot (1:11)
The Power Pivot Tab (0:31)
The Data Model (0:41)
The Power Pivot User Interface (1:00)
The Data and Diagram Views (0:30)
The Create Relationship Dialog Box (0:25)
Calculated Columns Part1 (1:07)
Calculated Columns Part2 (0:14)
Chapter 09 Review (0:27)
Chapter 09 Quiz
Advanced Customization Options
Section Introduction (0:18)
Topic A: Customize Advanced Options (0:15)
Formulas Options (0:41)
Data Options (0:25)
Proofing Options (0:30)
The AutoCorrect Dialog Box (0:46)
Save Options (0:40)
Language Options (0:20)
Ease of Access Options (0:29)
Advanced Options (1:33)
Custom Lists (0:34)
Add-Ins (0:26)
The Add-Ins Tab (0:17)
The Trust Center (1:12)
Chapter 10 Review (0:28)
Chapter 10 Quiz
Working with Forms and Controls
Section Introduction (0:45)
Topic A: Work with Forms and Controls (0:16)
Forms (0:40)
Form Types (1:29)
The Developer Tab (0:36)
Form Controls (0:38)
Control Properties (0:27)
Demo - Adding and Editing Data Using a Data Form (3:58)
Demo - Adding Form Controls (3:42)
Chapter 11 Review (0:24)
Chapter 11 Quiz
Course Closure (1:55)
Excel2019P3_06.01.05
Lesson content locked
If you're already enrolled,
you'll need to login
.
Enroll in Course to Unlock