Microsoft Office Excel 2010: Intermediate (R2)

Course Overview
This course builds on the skills and concepts taught in Excel 2010: Basic, First Look Edition. Students
will learn how to use multiple worksheets and workbooks efficiently, and they will start working with
more advanced formatting options including styles, themes, and backgrounds. They will also learn how to
create outlines and subtotals, how to create and apply cell names, and how to work with tables. Students
will save workbooks as Web pages, insert and edit hyperlinks, and learn to share workbooks by email.
This course also covers advanced charting techniques, use of trendlines and sparklines, worksheet
auditing and protection, file sharing and merging, and workbook templates.
Course Outline
Course Introduction 2m
Unit 01 – Using Multiple Worksheets and Workbooks 24m
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 50m
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 20m
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 21m
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 28m
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 14m
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 24m
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 27m
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 17m
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 28m
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
Total Duration: 4hrs 15m

Leave Comment