Learning Microsoft Excel 2007 Student Edition

Introduction

Lesson 1

Getting Started with Microsoft

Office 2007

Exercise 1

About Business Technology

About Microsoft Office 2007

Use the Mouse

Use the Keyboard

Start Windows Programs

Identify Common Screen Elements

Conventions Used in This Book

Exercise 2

About Commands

About the Ribbon

Use the Ribbon

Use Access Keys

Use the Office Button

Use the Quick Access Toolbar

Use a Mini Toolbar

Use Shortcut Menus

Use Dialog Box Options

Use Task Panes

Exercise 3

Use Window Controls

Zoom

Scroll

Use Multiple Windows

Exercise 4

Use the Help Program

Search for Help

Use the Help Table of Contents

Recover a File



Lesson 2

Getting Started with

Excel 2007

Exercise 5

Start Excel

The Excel Window

Excel Interface

Navigate the Worksheet

Change Between Worksheets

Worksheet Views

Exit Excel

Exercise 6

Create a New (Blank) Workbook

Create a Workbook from a Template or Another Workbook

Enter Labels

Make Simple Corrections

Undo and Redo

Delete (Clear) Cell Contents

Save a Workbook

Close a Workbook

Exercise 7

Open Workbooks

Change from Workbook to Workbook

Compare Workbooks

AutoComplete

Pick From List

AutoCorrect

Spell Check

Exercise 8

Enter Numeric Labels and Values

Enter Dates

Create a Series

Change Data Alignment

Change Column Width

Exercise 9

Enter a Formula

Edit a Formula

Exercise 10: Critical Thinking

Exercise 11: Curriculum Integration



Lesson 3

Copying Data, Formulas, and

Formatting

Exercise 12

Select Ranges

Range Entry Using Collapse Button

Exercise 13

Choose a Theme

Apply Cell Styles (Quick Styles)

Apply Font Formats

Apply Number Formats

Exercise 14

Copy and Paste Data

Copy Formats

Relative Reference

Absolute Reference

Preview and Print a Worksheet

Exercise 15

Insert and Delete Columns and Rows

Move Data (Cut/Paste)

Drag-and-Drop Editing

Exercise 16

Insert, Delete, Copy, Move, and Rename Worksheets

Change the Color of a Worksheet Tab

Hide Sheets

Group Sheets

Exercise 17: Critical Thinking

Exercise 18: Curriculum Integration



Lesson 4

Working with Functions,Tables,

and Charts

Exercise 19

Use Functions

Common Functions

Insert a Function

AutoCalculate

Exercise 20

Excel Tables

Comments

Exercise 21

Named Ranges

Exercise 22

Understand IF Functions

Nested IF Functions

=SUMIF() and SUMIFS() Functions

=COUNTIF() and COUNTIFS() Functions

Exercise 23

Chart Basics

Select Chart Data

Chart Elements

Create Charts

Change Chart Types

Select a Chart

Resize, Copy, Move, or Delete a Chart

Exercise 24

Change Data Series Orientation

Format a Chart

Resize, Move, or Delete a Chart Object

Change Chart Text

Enhance Chart Background

Format Category and Value Axes

Exercise 25: Critical Thinking

Exercise 26: Curriculum Integration



Lesson 5

More on Printing and Editing

Exercise 27

Print a Workbook

Print Multiple Copies

Modify Page Setup

Insert Headers and Footers

Page Layout View

Exercise 28

Insert Page Breaks

Page Break Preview

Set Print Area

Repeat Row and Column Labels

Other Sheet Tab Options

Exercise 29

Copy and Paste Special

Transpose Data

Exercise 30

Combine Data with Copy and Paste Special

Exercise 31

Freeze Labels While Scrolling

Split a Worksheet into Panes

Exercise 32

Drag-and-Drop Data Between Workbooks

Link Workbooks

Exercise 33

3-D Formulas

Duplicate Workbook Window

Exercise 34: Critical Thinking

Exercise 35: Curriculum Integration



Lesson 6

More on Entering Data, Saving,

and Printing

Exercise 36

Use Go To

Use Name Box

Exercise 37

Enter Labels on Multiple Lines

Enter Fractions and Mixed Numbers

Smart Tags

Insert and Delete Selected Cells

Copy or Move Data with the Office Clipboard

Exercise 38

Convert Text to Columns

Exercise 39

Link Documents with Hyperlinks

Add a Hyperlink to a Worksheet

Exercise 40

Hide Data Temporarily

Hide Worksheet Gridlines

Hide Row and Column Headings

Custom Views

Exercise 41

More Alignment Options

Format Dates and Times

Create Custom Number Formats

Adjust Row Height

Delete (Clear) a Cell's Formats

Exercise 42

Change Cell Borders

Fill Cells with Pattern

Modify and Save a Theme

Conditional Formatting

Add a Watermark

Format Worksheet Background

Exercise 43

Save a Workbook in a Different Format

Use Compatibility Checker

Create a New Folder for Saving Workbooks

Enable Macros in a Workbook

Create Original Templates

Exercise 44

E-mail a File

Display and Print Formulas

Set Precise Margins for Printing

Exercise 45: Critical Thinking

Exercise 46: Curriculum Integration



Lesson 7

Advanced Chart Techniques

Exercise 47

Print a Chart

Publish Chart to the Internet/Intranet

Exercise 48

Paste a Picture of a Chart

Link a Chart

Embed a Chart into a Word Document

Edit a Linked or Embedded Chart

Exercise 49

Create Organization Charts

Create SmartArt

Exercise 50: Critical Thinking

Exercise 51: Curriculum Integration



Lesson 8

Advanced Functions, PivotCharts,

and PivotTables

Exercise 52

Create Predictions and Estimations

Use FORECAST

Use TREND

Use GROWTH

Exercise 53

Use the PMT Function

Create What-If Data Tables

Solve a Problem with Goal Seek

Use Solver to Resolve Problems

Exercise 54

Create Lookup Functions

Exercise 55

Create PivotTables and PivotCharts

Use the PivotTable Field List

Exercise 56: Critical Thinking

Exercise 57: Curriculum Integration



Lesson 9

Internet and Integration

Exercise 58

Save a Worksheet as a Web Page

Exercise 59

Copy Data from a Web Page

Create a Web Query

Exercise 60

Link and Embed Excel Data

Paste a Picture

Link Data

Edit Linked Data

Embed Data

Edit Embedded Data

Exercise 61: Critical Thinking

Exercise 62: Curriculum Integration



Lesson 10

Getting Tricky with Formulas

and Graphics

Exercise 63

Conditional Sum Wizard

Conditionally Summarize Data with AVERAGEIF

and AVERAGEIFS

Exercise 64

Format Text with Formulas

Replace Text

Exercise 65

Formula Error Checking

Error Messages

Audit Formulas

Use the Watch Window

Evaluate a Formula

Suppress Errors While Printing

Exercise 66

Draw Shapes

Resize, Group, Align, and Arrange Shapes

Exercise 67

Format Shapes

Add Shape Effects

Exercise 68

Add Text to a Text Box, Callout, or Other Shape

Add 3-D Effects

Rotate Shapes

Exercise 69

Insert WordArt

Exercise 70

Insert Clips

Insert Pictures

Format Graphics

Exercise 71: Critical Thinking

Exercise 72: Curriculum Integration



Lesson 11

Performing Analyses

Exercise 73

Filter the Items in a Table

Filter Items without Creating a Table

Sum, Average, and Count the Items in a Filtered Table

Exercise 74

Advanced Filtering

Guidelines for Entering Criteria

Examples of Advanced Criteria

Remove an In-place Advanced Filter

Edit Extracted Records

Exercise 75

Sort Excel Items

Rules for Sorting

Undo a Sort

Restore Original Record Order

Exercise 76

Add Subtotals

Create Nested Subtotals

Remove a Subtotal

Hide/Display Details

Manually Outline and Add Subtotals

Exercise 77

Use Database Functions

Excel's Database Functions

Exercise 78

Find and Replace

Find Cells That Match Particular Criteria

Exercise 79

Finding Exact Matches with VLOOKUP and HLOOKUP

Logical Functions

Exercise 80

Create Scenarios

Exercise 81

Data Consolidation

Create Consolidation Tables

Exercise 82: Critical Thinking

Exercise 83: Curriculum Integration



Lesson 12

Protect and Share Worksheets

and Create Special Charts

Exercise 84

Add or Remove Data in a Chart

Set Data Label Options

Set Data Table Options

Format a Data Series

Exercise 85

Create a Stock Chart

Add a Secondary Value Axis to a Chart

Change Data Marker Format

Exercise 86

Create an Exploded Pie Chart

Size the Plot Area or Legend in a Chart

Create a Stacked Area Chart

Exercise 87

Create a 3-D Chart

Change the View of a 3-D Chart

Display or Hide Chart Gridlines

Exercise 88

Lock/Unlock Cells in a Worksheet

Protect a Range

Protect a Worksheet

Protect Workbooks

Exercise 89

Share Workbooks

Work with a Shared Workbook

Track Changes

Merge Changes

Exercise 90

Control Data Entry

Copy and Paste Validation Rules

Circle Invalid Data

Remove Duplicate Data

Exercise 91

Use Document Inspector to Remove Private Information

from Workbooks

Restrict Access to Workbooks with IRM

Identify Workbooks Using Keywords

Add Digital Signatures

Mark Workbooks as Final

Exercise 92: Critical Thinking

Exercise 93: Curriculum Integration

Index