The H. M. Rowe Company

Practical Excel 2000 is designed to teach students all of the elements necessary to obtain an entry-level position as an Office Assistant, Information Processing Assistant, or Data and Financial Assistant in a business office.

Beginning with basic worksheet label and value entry, editing, and formatting the instruction continues through using multiple worksheets, calculating with formulas and functions, chart building, database, macros, scenarios, filters, pivot tables, and statistical analysis. The book is peppered with helpful worksheet, icon, and dialog box illustrations.

Each of the Exercises have complete and uniform step-by-step instructions, with specific keystrokes, to complete the lesson. All applications are given in increasingly difficult order. At the end of the chapter, students are asked to demonstrate their knowledge in a series of Projects. Finally, students are asked to apply this new knowledge to create a real world application in the Chapter Challenge.

An Instructor's Resource CD is free upon request with a class-sized order. Unit tests are available in the Instructor's Resource CD.

Contents

CHAPTER 1: INTRODUCTION TO EXCEL 2000
Chapter Objectives
INTRODUCTION
WORKSHEET BASICS
LAUNCHING EXCEL
Start Excel
THE EXCEL APPLICATION WINDOW
Workbooks and Worksheets
Application Window
Title Bar
Menu Bar
Standard Toolbar
Formatting Toolbar
Formula Bar
Worksheet Frame
Worksheet Area
Cells
Status Bar
Scroll Bars
Exercise 1-1: Launch Excel 2000, and Explore the Application Window
ENTERING DATA AND WORKSHEET NAVIGATION
Entering Data into a Worksheet
Data Alignment
Editing Cell Entries
Navigating the Worksheet
Undo and Redo
WORK WITH WORKBOOKS
Save a Workbook
Print a Worksheet
Close a Workbook
Create a New Workbook
Exercise 1-2: Create, Save, Navigate, Print, and Close a Workbook
Open a Workbook
Zoom
Exercise 1-3: Open a Workbook, and Change the Zoom Setting
GETTING HELP WITH EXCEL
Office Assistant
Contents, Answer Wizard, and Index
What's This? Feature
Exercise 1-4: Using Help Features
CHAPTER SUMMARY
CHAPTER PROJECTS
Project 1-1: Identifying Worksheet Components
Project 1-2: Build, Save, and Print a Worksheet
Project 1-3: Open, Save, Edit, Print, and Save a Workbook
Project 1-4: Use Help
Project 1-5: Build, Save, and Print a Worksheet
CHAPTER CHALLENGE

CHAPTER 2: FORMULAS AND FUNCTIONS
Chapter Objectives
INTRODUCTION
RANGES
Exercise 2-1: Select Ranges
FORMAT DATA
Fonts, Font Size, and Font Style
Methods of Formatting Data
NUMBER FORMATS
General Format
Number Format
Currency Style
Comma Style
Percent Style
Fraction
Decimal Places
Date and Time
Rounding Values
Automatic Formatting
Data Alignment
Merge and Center
Data Rotation
Exercise 2-2: Format Data Using the Formatting Toolbar
Exercise 2-3: Format Data Using the Format Cells Dialog Box
Format Painter
Exercise 2-4: Use the Format Painter
WORKSHEET FORMATTING
Column Width
Row Height
Inserting and Deleting Columns and Rows
Hiding Columns and Rows
Exercise 2-5: Format Columns and Rows
Exercise 2-6: Insert and Delete Columns, Rows, and Cells
Change Cell Background Color and Cell Borders
Exercise 2-7: Background Color and Borders - Part 1
Exercise 2-8: Background Color and Borders - Part 2
FORMULAS AND FUNCTIONS
Formulas
Order of Operations
Complex Formulas
Copying Formulas
Display Cell Formulas
Exercise 2-9: Copy and Display Cell Formulas - Part 1
Exercise 2-10: Copy and Display Cell Formulas - Part 2
Relative Cell References
Absolute Cell References
Exercise 2-11: Cell References and Order of Operations
FUNCTIONS
Function Structure
SUM
AVERAGE
MAX
MIN
Exercise 2-12: Use the SUM, AVERAGE, MAX, and MIN Functions
TODAY
NOW
COUNT
COUNTA
Formula Palette
Enter an Argument by Dragging
Exercise 2-13: TODAY, NOW, COUNT, COUNTA, and the Formula Palette
DATA ENTRY SHORTCUTS
AutoFill
Exercise 2-14: Use AutoFill
AutoComplete
Pick From List
Exercise 2-15: Use AutoComplete and Pick From List
AutoCorrect
Replace
Exercise 2-16: Use Replace
CHAPTER SUMMARY
CHAPTER 2 PROJECTS
Project 2-1: Build a Worksheet with Formulas and Functions
Project 2-2: Build a Worksheet with Formulas and Functions
Project 2-3: Shade and Apply Borders to a Range
Project 2-4: Enter Formulas and Functions
Project 2-5: Inserting, Deleting, and Hiding Columns and Rows
CHAPTER CHALLENGE

CHAPTER 3: ADVANCED FUNCTIONS AND TEMPLATES
Chapter Objectives
INTRODUCTION
ADVANCED FUNCTIONS
PMT
Exercise 3-1: Use the PMT Function
SLN
Exercise 3-2: Use the SLN Function
SYD
Exercise 3-3: Use the SYD Function
FV
Exercise 3-4: Use the FV Function
IF
Exercise 3-5: Use the IF and COUNTIF Function
VLOOKUP
Exercise 3-6: Use the VLOOKUP Function - Part 1
Exercise 3-7: Use the VLOOKUP Function - Part 2
HLOOKUP
Exercise 3-8: Use the HLOOKUP Function
AND
Exercise 3-9: Nested Function with IF and AND
TEMPLATES
Custom Templates
Exercise 3-10: Create and Save a Template
Spreadsheet Solutions
Exercise 3-11: Use Spreadsheet Solutions
CHAPTER SUMMARY
CHAPTER 3 PROJECTS
Project 3-1: Use the PMT and SLN Functions
Project 3-2: Use the IF and COUNTIF Functions
Project 3-3: Use the VLOOKUP Function
Project 3-4: Use the SYD Function
Project 3-5: Use the HLOOKUP and VLOOKUP Functions
Project 3-6: Use the IF, AND, and TODAY Functions
Project 3-7: Create and Save a Template
Project 3-8: Use Spreadsheet Solutions
CHAPTER CHALLENGE

CHAPTER 4: LARGE WORKSHEETS, MULTIPLE WORKSHEETS AND WORKBOOKS
Chapter Objectives
INTRODUCTION
RANGE NAMES
Exercise 4-1: Use Range Names
LARGE WORKSHEETS
Sorting
Freeze Panes
Exercise 4-2: Work with a Large Worksheet, Sorting, and Freeze Panes
Filtering
Exercise 4-3: Use AutoFilter
Exercise 4-4: Use Advanced Filter
Subtotals
Exercise 4-5: Use Subtotals
MULTIPLE WORKSHEETS
Navigate Worksheets
Rename Worksheets
Insert Worksheets
Rearrange Worksheets
Copy Worksheets
Delete Worksheets
Exercise 4-6: Use Multiple Worksheets
Group Worksheets
Exercise 4-7: Group Worksheets
Internal Reference Formulas
Exercise 4-8: Group Worksheets
MULTIPLE WORKBOOKS
Exercise 4-9: Open and Close Multiple Workbooks
External Reference Formulas
Exercise 4-10: Create External Reference Formulas
Viewing, Editing, and Updating Links
Exercise 4-11: View and Update Links
CHAPTER SUMMARY
CHAPTER 4 PROJECTS
Project 4-1: Range Names, Sorting, Filtering, and Subtotals
Project 4-2: Name, Insert, Delete, and Rearrange Worksheets
Project 4-3: Link Worksheets with 3-D References
CHAPTER CHALLENGE

CHAPTER 5: ADVANCED FORMATTING AND PRINTING
Chapter Objectives
INTRODUCTION
ADVANCED PRINTING
Set and Clear a Print Area
Print a Selection
Print Preview
Exercise 5-1: Print Preview, Set a Print Area, and Print a Selection
Inserting and Removing Page Breaks
Print Orientation and Scaling
Exercise 5-2: Insert/Remove Page Breaks and Set Print Orientation/Scaling
Margins, Worksheet Centering, and Headers/Footers
Headers and Footers
Exercise 5-3: Margins, Worksheet Centering, and Headers/Footers
Print Titles
Exercise 5-4: Set Print Titles
ADVANCED FORMATTING
Cut
Copy
Paste
Exercise 5-5: Cut, Copy, and Paste Data
Conditional Formatting
Exercise 5-6: Conditional Formatting
Exercise 5-7: Data Validation
AutoFormat
Exercise 5-8: AutoFormat
Styles
Exercise 5-9: Create, Apply, Modify, and Delete a Style
CHAPTER SUMMARY
CHAPTER 5 PROJECTS
Project 5-1: Add a Header and Foote, and Print a Selection
Project 5-2: Set Print Options, and Data Validation
Project 5-3: Create and Apply Styles
Project 5-4: Enter Formulas, and Apply Conditional Formatting
CHAPTER CHALLENGE

CHAPTER 6: CHARTS
Chapter Objectives
INTRODUCTION
CHART BASICS
Chart Title
Chart Area
Plot Area
Legend
Axes
Embedded Charts and Chart Sheets
Default Chart
Chart Wizard
Chart Types
Line Charts
Column Charts
Bar Charts
Pie Charts
Chart Options
Exercise 6-1: Create an Instant Chart Based on Selected Data
Exercise 6-2: Create a Chart Using the Chart Wizard
Resize, Move, and Copy a Chart
Exercise 6-3: Resize, Move, Copy, and Delete a Chart
Exercise 6-4: Create Charts Based on Noncontiguous Ranges
Chart Formatting
Exercise 6-5: Modify and Format Chart Elements
Print Charts
Exercise 6-6: Print Charts
CHAPTER SUMMARY
CHAPTER 6 PROJECTS
Project 6-1: Create and Print a Chart
Project 6-2: Create, Modify, Print, and Delete a Chart
CHAPTER CHALLENGE

CHAPTER 7: GRAPHICS
Chapter Objectives
INTRODUCTION
GRAPHICS
Clip Art
Move and Copy an Image
Exercise 7-1: Insert, Size, Move and Copy an Image
Multiple Clip Art Images
Exercise 7-2: Work with Multiple Clip Art Images and Format Images
Graphic Files
Exercise 7-3: Insert Graphic Files into a Worksheet
WordArt
Text Boxes
Exercise 7-4: Insert WordArt and Text Boxes
AutoShapes
Exercise 7-5: Add and Format AutoShapes
Rotate and Flip AutoShapes
Exercise 7-6: Format, Rotate, and Flip AutoShapes
CHAPTER SUMMARY
CHAPTER 7 PROJECTS
Project 7-1: Insert and Format Clip Art
Project 7-2: Drawing Elements and Pictures from Files
Project 7-3: Format AutoShapes
Project 7-4: Format Worksheet
CHAPTER CHALLENGE

CHAPTER 8: ADVANCED TOPICS
Chapter Objectives
INTRODUCTION
GOAL SEEK
Exercise 8-1: Use Goal Seek
TOOLBAR AND WORKSHEET CUSTOMIZATION
Customize Toolbars
Exercise 8-2: Customize Toolbars
Create a Custom Toolbar
Exercise 8-3: Create a Toolbar
Workbook Defaults
Exercise 8-4: Change Workbook/Worksheet Defaults
PROTECTION
Protect a Workbook
Protecting a Worksheet
Exercise 8-5: Protect a worksheet and Unlock Cells
Read Only
Password Protection
Exercise 8-6: Save as Read-Only File and Password-Protect a Workbook
WORKBOOK MANAGEMENT
Renaming Workbooks
Exercise 8-7: Rename a Workbook
File System
Create a Folder
Back up a Workbook
Move Workbooks
Exercise 8-8: Move and Back-up Workbooks, Create a Folder
WORKBOOKS AND THE WORLD WIDE WEB
Save a Workbook as a Web Page
Web Page Preview
Exercise 8-9: Save a Workbook as a Web Page
Hyperlinks
Exercise 8-10: Hyperlinks
Send a Worksheet as E-mail
Exercise 8-11: Send a Worksheet as an E-mail Message
CHAPTER SUMMARY
CHAPTER 8 PROJECTS
Project 8-1: Use Goal Seek and Protect a Worksheet
Project 8-2: Create a Folder, Goal Seek, and Protect a Worksheet
Project 8-3: Goal Seek, Create a Folder, and Move a File
Project 8-4: Insert Hyperlinks
CHAPTER CHALLENGE

CHAPTER 9: PIVOT TABLES AND AUDITING FEATURES
Chapter Objectives
INTRODUCTION
PIVOT TABLE
Create a Pivot Table
Exercise 9-1: Create a Pivot Table
Exercise 9-2: Modify an Existing Pivot Table
Exercise 9-3: Create a Pivot Table from an External Data Source
SPELLING AND AUDITING FEATURES
Spell Check
Exercise 9-4: Fix Formulas in a Worksheet and Spell Check
AUDITING FEATURES
Trace Precedents
Trace Dependents
Trace Errors
Exercise 9-5: Trace Precedents and Dependents, and Spell Check
CHAPTER SUMMARY
CHAPTER PROJECTS
Project 9-1: Create and Modify Pivot Tables
Project 9-2: Analyze and Repair a Worksheet
Project 9-3: Create and Format a Pivot Table
Project 9-4: Analyze and Repair a Worksheet
CHAPTER CHALLENGE

CHAPTER 10: MACROS AND SCENARIOS
Chapter Objectives
INTRODUCTION
MACROS
Create a Macro
Steps to Creating a Macro
Run a Macro
Macro Environment
Exercise 10-1: Create a Macro
Edit Macros
Exercise 10-2: Edit and Delete a Macro
Macro Buttons
Exercise 10-3: Create a Macro and Assign it to a Command Button
SCENARIOS
Exercise 10-4: Create a Scenario
Scenario Summary Report
Exercise 10-5: Create a Scenario Summary Report
CHAPTER SUMMARY
CHAPTER 10 PROJECTS
Project 10-1: Design a Basic Macro
Project 10-2: Create, Run, and Edit a Macro
Project 10-3: Create a Chart and a Scenario
Project 10-5: Create a Scenario
CHAPTER CHALLENGE