Course outline for
Microsoft Excel 2010: Part Two

About This Course

Course Prerequisites

Course Overview

Course Objectives

How To Use This Book

Lesson 1: Customizing the Excel Environment

TOPIC A: Configure Excel Options

The Excel Options Dialog Box

General Options

Formulas Options

Proofing Options

The AutoCorrect Dialog Box

Save Options

Language Options

Advanced Options

The Trust Center

Activity 1-1

TOPIC B: Customize the Ribbon and the Quick Access Toolbar

The Customize Ribbon Category

The Customize the Ribbon List Hierarchy

The Quick Access Toolbar Category

The Customize Quick Access Toolbar Menu

Activity 1-2

TOPIC C: Enable Excel Add-Ins

Add-Ins

The Add-Ins Category

Activity 1-3

Summary

Review Questions

Lesson 2: Creating Advanced Formulas

TOPIC A: Use Range Names in Formulas

Cell and Range Names

Names and the Name Box

The New Name Dialog Box

The Create from Selection Command

The Name Manager Dialog Box

Cell and Range Names in Formulas

Manually Entering Cell and Range Names

The Use in Formula Command

Formula AutoComplete

Activity 2-1

TOPIC B: Use Specialized Functions

Function Categories

The Excel Function Reference

Logical Operator Basics

Function Syntax

Automatic Workbook Calculation Considerations

Activity 2-2

TOPIC C: Use Array Formulas

Arrays

Array Formulas

Array Formula Syntax

Array Formula Rules

Activity 2-3

TOPIC D: Create Hyperlinks

Inserting Hyperlinks

Modifying Hyperlinks

Modifying Hyperlinked Cell Attributes

Using Hyperlinks in Excel

Removing a Hyperlink

Activity 2-4

Summary

Review Questions

Lesson 3: Analyzing Data with Functions and Conditional Formatting

TOPIC A: Analyze Data by Using Text and Logical Functions

Text Functions

The CONCATENATE Function

Logical Functions

Logical Values

Logical Operators

Logical Function Syntax

Start with Questions, End with Testing

Activity 3-1

TOPIC B: Apply Advanced Conditional Formatting

Data Bars

Color Scales

Icon Sets

Custom Conditional Formats

The New Formatting Rule Dialog Box

The Conditional Formatting Rules Manager Dialog Box

Rule Precedence

Activity 3-2

Summary

Review Questions

Lesson 4: Organizing and Analyzing Datasets and Tables

TOPIC A: Create and Modify Tables

Tables

Table Components

The Create Table Dialog Box

The Table Tools – Design Contextual Tab

Table Styles and Quick Styles

The New Table Quick Style Dialog Box

Activity 4-1

TOPIC B: Sort Data

Sorting

Multiple Column/Row Sorting

Quick Sorts

Custom Sorts

The Sort Dialog Box

The Sort Options Dialog Box

Activity 4-2

TOPIC C: Filter Data

Filtering

AutoFilters

Custom AutoFilters

The Custom AutoFilter Dialog Box

Advanced Filtering

The Criteria Range

Filter Operators

Activity 4-3

TOPIC D: Use Subtotal and Database Functions

SUBTOTAL Functions

The Subtotals Feature

The Subtotal Dialog Box

Outlines

Summary Functions

Database Functions

Database Function Syntax

Activity 4-4

Summary

Review Questions

Lesson 5: Visualizing Data with Basic Charts

TOPIC A: Create Charts

Charts

Chart Basics

Chart Types

Chart Insertion Methods

Activity 5-1

TOPIC B: Modify and Format Charts

Modification vs. Formatting

Chart Elements

Guidelines for Including Chart Elements

The Chart Tools Contextual Tabs

The Select Data Source Dialog Box

Activity 5-2

Summary

Review Questions

Lesson 6: Analyzing Data with PivotTables, Slicers, and PivotCharts

TOPIC A: Create a PivotTable

Pivoting

PivotTables

Transactional Data

The Create PivotTable Dialog Box

Activity 6-1

TOPIC B: Analyze PivotTable Data

Start with Questions, End with Structure

The PivotTable Field List Pane

The PivotTable Tools Contextual Tabs

The Value Field Settings Dialog Box

Summarize and Show Combinations

Activity 6-2

TOPIC C: Present Data with PivotCharts

PivotCharts

PivotChart Filters

Activity 6-3

TOPIC D: Filter Data by Using Slicers

Slicers

The Insert Slicers Dialog Box

The Slicer Tools – Options Contextual Tab

The PivotTable Connections Dialog Box

Activity 6-4

Summary

Review Questions

Lesson 7: Financial Functions

TOPIC A: Financial Functions

The IPMT Function

The PPMT Function

The NPV Function

The FV Function

Activity 7-1

Summary

Review Questions

Lesson 8: Date and Time Functions

TOPIC A: Date and Time Functions

The TODAY Function

The NOW Function

The DATE Function

The NETWORKDAYS Function

Activity 8-1

Summary

Review Questions

Lesson 9: Working with Graphical Objects

TOPIC A: Insert Graphical Objects

Graphical Objects

The Clip Art Pane

The Shapes Gallery

The Screenshot Command

Activity 9-1

TOPIC B: Modify Graphical Objects

Pictures and Drawings

The Picture Tools – Format Contextual Tab

The Image Editor

The Drawing Tools – Format Contextual Tab

The Selection and Visibility Pane

Activity 9-2

TOPIC C: Work with SmartArt

SmartArt Graphics

SmartArt Shapes

The Choose a SmartArt Graphic Dialog Box

The Text Pane

The SmartArt Tools Contextual Tabs

Activity 9-3

Summary

Review Questions

Lesson Labs

Lesson 1

Lesson Lab 1-1

Lesson 2

Lesson Lab 2-1

Lesson Lab 2-2

Lesson 3

Lesson Lab 3-1

Lesson 4

Lesson Lab 4-1

Lesson Lab 4-2

Lesson 5

Lesson Lab 5-1

Lesson 6

Lesson Lab 6-1

Lesson Lab 6-2

Lesson 7

Lesson Lab 7-1

Lesson 8

Lesson Lab 8-1

Lesson 9

Lesson Lab 9-1

Course Wrap-Up

Post-Course Assessment

Course Summary

Next Steps

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