Microsoft Office 2010 Excel - Part 2

Microsoft Office 2010 Excel - Part 2


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.

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


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

Microsoft Office 2010 Excel - Part 2
tag icon Price $49.99
tag icon Instrutor 2
tag icon Duration 4h 15m
tag icon Lessons 10
tag icon Access 12 Months
Add to Cart