Course Overview
This course provides students with the knowledge and skills to automate common tasks, audit
workbooks to avoid errors, share your data with other people, analyze data, and use Excel data
in other applications.
Course Introduction 1m
Course Introduction
Lesson 01 – Automating Worksheet Functionality 42m
Topic A: Update Workbook Properties
Workbook Properties
Demo 1-1: Updating Workbook Properties
Demo 1-2: Modifying the Default Settings in Excel
Topic B: Create and Edit a Macro
Macros Icon
Record Macro Dialog Box
Naming Macros
Visual Basic for Applications
Demo 1-3: Creating a Macro
Demo 1-4: Editing a Macro
Topic C: Apply Conditional Formatting
Conditional Formatting
Conditional Formatting Rules Manager
Demo 1-5: Applying Conditional Formatting
Demo 1-6: Editing a Conditional Formatting Rule
Topic D: Add Data Validation Criteria
Data Validation
The Data Validation Dialog Box
Demo 1-7: Adding Data Validation Criteria
Lesson 01 Review
Lesson 02 – Auditing Worksheets 37m
Topic A: Trace Cells
Trace Cells
Tracer Arrows
Demo 2-1: Tracing Cell Precedents and Dependents
Topic B: Troubleshoot Invalid Data and Formula Errors
The Error Checking Command
Data Validation
Demo 2-2: Troubleshooting Invalid Data
Demo 2-3: Troubleshooting Invalid Formulas
Topic C: Watch and Evaluate Formulas
Watch Window
The Evaluate Formula Icon
Demo 2-4: Evaluating a Formula
Topic D: Create a Data List Outline
Outlines
Demo 2-5: Creating a Data List Outline
Demo 2-6: Adding Subtotals to Grouped Data
Lesson 02 Review
Lesson 03 – Analyzing and Presenting Data 1h 2m
Topic A: Create Sparklines
Sparkline
The SPARKLINE TOOLS DESIGN Tab
Demo 3-1: Adding Sparklines to Your Worksheet
Demo 3-2: Editing Sparkline Data
Topic B: Create Scenarios
Scenarios
The Scenario Manager Dialog Box
Demo 3-3: Adding a Scenario
Demo 3-4: Editing Scenario Data
Topic C: Perform a What-If Analysis
Goal Seek Dialog Box
Solver Tool Parameters
Demo 3-5: Using Goal Seek
Demo 3-6: Installing the Solver Tool Add-In
Demo 3-7: Using Solver to Perform a What-If Analysis
Topic D: Perform a Statistical Analysis with the Analysis ToolPak
Analysis ToolPak
Demo 3-8: Performing a Data Analysis with Analysis ToolPak-Descriptive Statistics
Topic E: Create Interactive Data with Power View
A Power View Worksheet
Demo 3-9: Creating a Power View Worksheet
Lesson 03 Review
Lesson 04 – Working With Multiple Workbooks 29m
Topic A: Consolidate Data
Data Consolidation
Demo 4-1: Consolidating Data
Topic B: Link Cells in Different Workbooks
Link Cells in Different Workbooks
Demo 4-2: Linking Cells in Different Workbooks
Demo 4-3: Editing Links
Topic C: Merge Workbooks
The Compare and Merge Workbooks Feature
Demo 4-4: Merging Workbooks
Lesson 04 Review
Lesson 05 – Exporting Excel Data 36m
Topic A: Export Excel Data
Export
Demo 5-1: Exporting Excel Data
Topic B: Import a Delimited Text File
Text Import Wizard
Get External Data Group
Delimited Text Files
Demo 5-2: Importing a Delimited Text File
Topic C: Integrate Excel Data with the Web
File Publish
Publish as a Web Page Dialog Box
Demo 5-3: Publishing a Worksheet to the Web
Demo 5-4: Importing Data from the Web
Topic D: Create a Web Query
Web Queries
The New Web Query Dialog Box
Demo 5-5: Creating a Web Query
Lesson 05 Review
Course Closure
Total Duration: 3h 27m