Course outline for
Microsoft Excel 2010: Part Three

About This Course

Course Prerequisites

Course Overview

Course Objectives

How To Use This Book

Lesson 1: Automating Worksheet Functionality

TOPIC A: Update Workbook Properties

Workbook Properties

Activity 1-1

TOPIC B: Create and Edit a Macro

Macros

The Record Macro Dialog Box

Macro Naming

Visual Basic for Applications

Macro Security Settings

Activity 1-2

TOPIC C: Apply Conditional Formatting

Conditional Formatting

Conditional Formats

The Conditional Formatting Rules Manager Dialog Box

The New Formatting Rule Dialog Box

Clear Rules

Activity 1-3

TOPIC D: Add Data Validation Criteria

Data Validation

The Data Validation Dialog Box

Activity 1-4

Summary

Review Questions

Lesson 2: Auditing Worksheets

TOPIC A: Trace Cells

The Trace Cells Feature

Tracer Arrows

Activity 2-1

TOPIC B: Troubleshoot Invalid Data and Formula Errors

Invalid Data

The Error Checking Command

Error Types

Activity 2-2

TOPIC C: Watch and Evaluate Formulas

The Watch Window

Formula Evaluation

Activity 2-3

TOPIC D: Create a Data List Outline

Outlines

The Outline Group

Activity 2-4

Summary

Review Questions

Lesson 3: Analyzing Data

TOPIC A: Create Sparklines

Sparklines

Types of Sparklines

The Sparkline Tools – Design Tab

Activity 3-1

TOPIC B: Create Scenarios

Scenarios

About What-If Analysis

The Scenario Manager Dialog Box

Activity 3-2

TOPIC C: Perform a What-If Analysis

Add-In Types

The Goal Seek Tool

The Solver Tool

Activity 3-3

TOPIC D: Perform a Statistical Analysis with the Analysis ToolPak

Analysis ToolPak

The Data Analysis Dialog Box

Anova

Correlation

Random Number Generator

Rank and Percentile

Activity 3-4

Summary

Review Questions

Lesson 4: Working with Multiple Workbooks

TOPIC A: Create a Workspace

Workspaces

Activity 4-1

TOPIC B: Consolidate Data

Data Consolidation

The Consolidate Dialog Box

Consolidation Functions

Activity 4-2

TOPIC C: Link Cells in Different Workbooks

External References

Activity 4-3

TOPIC D: Merge Workbooks

The Compare and Merge Workbooks Feature

Activity 4-4

Summary

Review Questions

Lesson 5: Exporting Excel Data

TOPIC A: Export Excel Data

Export Formats

Sharing Workbooks

Activity 5-1

TOPIC B: Import a Delimited Text File

Importing Process

The Get External Data Group

Delimited Text Files

Methods of Importing Text Files

Activity 5-2

TOPIC C: Integrate Excel Data with the Web

The File Publishing Process

Publish as Web Page Dialog Box

Activity 5-3

TOPIC D: Create a Web Query

Web Queries

The New Web Query Dialog Box

Activity 5-4

Summary

Review Questions

Lesson 6: Importing and Exporting XML Data

TOPIC A: Import and Export XML Data

XML

XML Components

XML Schemas

XML Maps

The XML Source Task Pane

Import and Export XML Data

Activity 6-1

Summary

Review Questions

Lesson 7: Creating Excel Forms

TOPIC A: Create Excel Forms

What is a Form?

Types of Controls

Create Excel Forms

Activity 7-1

Summary

Review Questions

Lesson Labs

Lesson 1

Lesson Lab 1-1

Lesson Lab 1-2

Lesson 2

Lesson Lab 2-1

Lesson Lab 2-2

Lesson 3

Lesson Lab 3-1

Lesson Lab 3-2

Lesson 4

Lesson Lab 4-1

Lesson Lab 4-2

Lesson 5

Lesson Lab 5-1

Lesson Lab 5-2

Lesson 6

Lesson Lab 6-1

Lesson 7

Lesson Lab 7-1

Course Wrap-Up

Post-Course Assessment

Course Summary

Appendices

Keyboard Shortcut Quick Reference Sheet

Mapping to Microsoft Office Excel 2010, Exam 77-882

Mapping to Microsoft Office Excel 2010 Expert, Exam 77-888

Glossary

Index