Unit 01 - Using Multiple Worksheets and Workbooks

Topic A: Using Multiple Workbooks
Switch Between Workbooks
Demo - A-1: Switching Between Workbooks
The Move or Copy Dialog Box
Demo - A-2: Copying a Worksheet to Another Workbook
Topic B: Linking Worksheets with 3-D Formulas
Inserting a 3-D Reference
Demo - B-1: Creating 3-D Formulas
Adding a Watch Window
Demo - B-2: Adding a Watch Window
Topic C: Linking Workbooks
Demo - C-1: Examining External Links in a Worksheet
Syntax for External Links
Creating External Links
Demo - C-2: Creating External Links in a Worksheet
Redirecting Links
Demo - C-3: Editing Links
Topic D: Managing Workbooks
Creating a Workspace
Demo - D-1: Creating a Workspace
Unit 01 Review

Unit 02 - Advanced Formatting

Topic A: Using Special Number Formats
The Format Cells, Number Tab
Demo - A-1: Applying Special Formats
Hiding Zero Values
Demo - A-2: Controlling the Display of Zero Values
Customizing Number Formats
Custom Number Formats
Demo - A-3: Creating Custom Formats
Topic B: Using Functions to Format Text
Text Functions
Demo - B-1: Using PROPER, UPPER, and LOWER
The SUBSTITUTE Function
Demo - B-2: Using SUBSTITUTE
Topic C: Working with Styles
The Cell Styles Gallery
Creating Styles
Demo - C-1: Creating and Applying Styles
Modifying Styles
Demo - C-2: Modifying Styles
Topic D: Working with Themes
Theme Colors
Demo - D-1: Changing to a Different Theme
Creating New Theme Colors
Topic E: Other Advanced Formatting
Merging Cells
Wrap Text in a Cell
Changing Orientation of Text
Demo - E-1: Merging Cells
Demo - E-2: Changing the Orientation of Text in a Cell
Demo - E-3: Splitting Cells
Transposing Data
Demo - E-4: Transposing Data During a Paste
Use Paste Special to Add Values
Demo - E-5: Using Paste Special to Add Values
Adding Backgrounds
Demo - E-6: Adding and Deleting Backgrounds
Adding a Watermark
Demo - E-7: Adding a Watermark
Unit 02 Review

Unit 03 - Outlining and Subtotals

Topic A: Outlining and Consolidating Data
The Expanded Outline Form
The Collapsed Outline Form
Demo - A-1: Creating an Outline
Create a Custom View
Switch Among Custom Views
Demo - A-2: Creating Custom Views
The Consolidate Dialog Box
Demo - A-3: Using the Consolidate Command
Topic B: Creating Subtotals
The Subtotal Dialog Box
Demo - B-1: Creating Subtotals in a List
Demo - B-2: Using Multiple Subtotal Functions
Unit 03 Review

Unit 04 - Cell and Range Names

Topic A: Creating and Using Names
Defining Names
Demo - A-1: Naming and Selecting Ranges
Using Names in Formulas
Demo - A-2: Using Names in Formulas
Using Create from Selection
Demo - A-3: Using the Create from Selection Command
Applying Names
Demo - A-4: Applying Names to Existing Formulas
Topic B: Managing Names
The Name Manager Dialog Box
Demo - B-1: Modifying and Deleting Named Ranges
Creating a 3-D Name
Demo - B-2: Defining and Applying 3-D Names
Unit 04 Review

Unit 05 - Tables

Topic A: Sorting and Filtering Data
Structure of Organized Data
Sorting Data Based on a Cell
Sorting by Multiple Columns
Demo - A-2: Sorting Data
Filtering Data
Sort and Filter by Color
Demo - A-3: Filtering Data by Using AutoFilter
Topic B: Advanced Filtering
Custom AutoFilter Dialog Box
Demo - B-1: Using Custom AutoFilter Criteria
Creating a Criteria Range
Demo - B-2: Using the Advanced Filter Dialog Box
Copying the Filtered Data
Topic C: Working with Tables
Creating a Table
Table Tools | Design Tab
Adding to a Table
Demo - C-1: Creating a Table
Demo - C-2: Formatting a Table
Demo - C-3: Adding and Deleting Rows and Columns
Structured References
Demo - C-4: Using Structured References
Table Names
The [@] Argument
Unit 05 Review

Unit 06 - Web and Sharing Features

Topic A: Saving Workbooks as Web Pages
Customizing the Quick Access Toolbar
Saving a Workbook as a Web Page
Demo - A-2: Saving a Workbook as a Web Page
Publishing a Web Page
The Publish as Web Page Dialog Box
Topic B: Using Hyperlinks
Inserting a Hyperlink
Demo - B-1: Inserting a Hyperlink
Modify a Hyperlink
Demo - B-2: Modifying and Deleting a Hyperlink
Topic C: Sharing Workbooks
File Save & Send Page Options
Demo - C-1: Examining Workbook Sharing Options
Save & Send Options
Sharing Workbooks by Email
Unit 06 Review

Unit 07 - Advanced Charting

Topic A: Chart Formatting Options
Format Axis: Axis Options
Demo - A-1: Adjusting the Scale of a Chart
Labeling a Data Point
Demo - A-2: Formatting a Data Point
Topic B: Combination Charts
Combination Chart: Changing Chart Type
Combination Chart: Adding a Secondary Axis
Demo - B-1: Creating a Combination Chart
Adding a Trendline
Demo - B-2: Creating a Trendline
Inserting Sparklines
Sparklines in a Worksheet
Demo - B-3: Inserting Sparklines
Downloading a Chart Template
Topic C: Graphical Elements
Adding Shapes to Charts
Demo - C-1: Adding Graphical Elements
Formatting Graphical Elements
Demo - C-2: Formatting a Graphical Element
Inserting a Picture from a File
Demo - C-3: Adding a Picture to a Worksheet
The Adjust Group
Demo - C-4: Modifying a Picture
Unit 07 Review

Unit 08 - Documenting and Auditing

Topic A: Auditing Features
Dependent and Precedent Cells
Demo - A-1: Tracing Precedent and Dependent Cells
Tracing Errors in a Worksheet
Demo - A-2: Tracing Errors
Topic B: Comments in Cells and Workbooks
Viewing Comments
Demo - B-1: Viewing Comments in a Worksheet
Adding Cell Comments
Demo - B-2: Adding a Comment to a Cell
The Document Panel
Topic C: Protection
Password-Protect a Worksheet
Demo - C-1: Password-Protecting a Worksheet
Protect Parts of a Worksheet
Demo - C-2: Unlocking Cells and Protecting Part of a Worksheet
Protect the Workbook Structure
Demo - C-3: Protecting the Workbook Structure
Using Digital Signatures
Topic D: Workgroup Collaboration
Sharing a Workbook
Demo - D-1: Sharing a Workbook
Share Workbook: Advanced Tab
Tracking Changes
Accepting and Rejecting Changes
Demo - D-3: Tracking Changes in a Workbook
Using the Document Inspector
Marking a Workbook as Final
Demo - D-5: Marking a Workbook as Final
Unit 08 Review

Unit 09 - Templates and Settings

Topic A: Application Settings
The Excel Options Dialog Box
Demo - A-1: Changing Application Settings
The Customize Ribbon Page
Demo - A-2: Customizing the Ribbon
Topic B: Built-in Templates
Available Templates
Using the Sales Invoice Template
Demo - B-1: Using a Downloaded Template
Topic C: Creating and Managing Templates
Creating a Template
Demo - C-1: Creating a Template
Modifying a Template
Demo - C-2: Modifying a Template
Using an Alternate Template Location
Unit 09 Review

Unit 10 - PivotTable and PivotCharts

Topic A: Working with PivotTables
A Sample PivotTable
Create a PivotTable
Demo - A-1: Creating a PivotTable
Add Fields
Demo - A-2: Adding Fields to a PivotTable
Insert a Slicer
A PivotTable with Slicers
Demo - A-3: Using Slicers to Filter PivotTable Data
Topic B: Rearranging PivotTables
Moving Fields
Demo - B-1: Moving Fields
Hide Details
Expand a Collapsed Range
Demo - B-2: Collapsing and Expanding Fields
Refresh Data
Demo - B-3: Refreshing the Data in a PivotTable
Topic C: Formatting PivotTables
PivotTable Styles
Demo - C-1: Applying a PivotTable Style
Change Field Settings
Value Field Settings Dialog Box
Demo - C-2: Changing Field Settings
Topic D: Using PivotCharts
Create a PivotChart
A Sample PivotChart
Demo - D-1: Creating a PivotChart
Unit 10 Review
Course Closure