GO! with Excel 2007 Comprehensive

Shelley Gaskin, Series Editor for the GO! Series, is a professor of business and computer technology at Pasadena City College in Pasadena, California. She holds a master's degree in business education from Northern Illinois University and a doctorate in adult and community education from Ball State University. Dr. Gaskin has 15 years of experience in the computer industry with several Fortune 500 companies and has developed and written training materials for custom systems applications in both the public and private sector. She is also the author of books on Microsoft Outlook and word processing.

Chapter 1 Creating a Worksheet and Charting Data

 

Project 1A Seattle Payroll

 

Objective 1 Start Excel and Navigate a Worksheet

                Activity 1.1 Starting Excel and Naming and Saving a Workbook

                Activity 1.2 Navigating a Worksheet

Objective 2 Select Parts of a Worksheet

                Activity 1.3 Selecting Cells and Ranges and Entering Data 1

Objective 3 Enter Data, Construct a Formula, and Use the SUM Function

                Activity 1.4 Opening an Existing Workbook, Entering Text, and Using

                AutoComplete

                Activity 1.5 Entering Numbers and Adjusting Column Width

                Activity 1.6 Entering a Formula and Using the Sum Function

Objective 4 Format Data, Cells, and Worksheets

                Activity 1.7 Using Font Styles and Centering Text

                Activity 1.8 Using Merge and Center

Objective 5 Insert a Footer into a Worksheet

                Activity 1.9 Adding a Footer to a Worksheet

Objective 6 Delete Unused Worksheets and Preview and Print

a Worksheet

                Activity 1.10 Deleting Unused Worksheets

                Activity 1.11 Previewing and Printing a Worksheet

Objective 7 Print Formulas, Close a Workbook, and Exit Excel

                Activity 1.12 Displaying and Printing Formulas, Closing a Workbook, and Exiting Excel

 

Project 1B Annual Income

 

Objective 8 Check Spelling and Edit a Worksheet

                Activity 1.13 Opening and Saving an Existing Workbook

                Activity 1.14 Using Wrap Text and the Undo Command

                Activity 1.15 Checking for Spelling Errors in a Worksheet

                Activity 1.16 Editing Text in a Worksheet

Objective 9 Enter Data by Range

                Activity 1.17 Selecting Cells and Entering Data by Range

Objective 10 Create and Copy Formulas

                Activity 1.18 Using the Point-and-Click Method and Calculation Operators

                to Create a Formula

                Activity 1.19 Using the Fill Handle to Copy a Formula

                Activity 1.20 Determining Percentages

Objective 11 Use Format Painter and Chart Data

                Activity 1.21 Using Format Painter

                Activity 1.22 Charting Data

                Activity 1.23 Printing the Worksheet and its Formulas

                Activity 1.24 Using Help




 

Chapter 2 Using Multiple-Sheet Workbooks

 

Project 2A Income From Lodging

 

Objective 1 Use a Multiple-Sheet Workbook

                Activity 2.1 Inserting a Worksheet and Entering and Formatting Data in a Multiple Sheet   Workbook

                Activity 2.2 Changing the Format of Worksheet Tabs and Using the Tab

                Scrolling Buttons

Objective 2 Enter a Series

                Activity 2.3 Entering a Series

Objective 3 Copy and Paste Cell Contents

                Activity 2.4 Using Copy and Paste

                Activity 2.5 Copying Data Between Worksheets

Objective 4 Use the Office Clipboard

                Activity 2.6 Using the Office Clipboard to Collect and Paste Data Between

                Workbooks

Objective 5 Total the Worksheet Range and Enter a Grand Total

                Activity 2.7 Totaling the Worksheet in One Step and Correcting Formula Errors

                Activity 2.8 Creating a Grand Total in One Step

Objective 6 Format a Multiple-Sheet Workbook Group

                Activity 2.9 Formatting a Worksheet Group and Using Print Preview

Objective 7 Insert Columns and Rows in Multiple Worksheets

                Activity 2.10 Inserting Columns in a Worksheet

                Activity 2.11 Inserting Rows That Adjust Relative References and Reviewing

                Error Messages

 

Project 2B Hotel Taxes

 

Objective 8 Copy a Worksheet

                Activity 2.12 Copying a Worksheet within a Workbook

 

Objective 9 Create Formulas with Absolute Cell References and Copy Formats

                Activity 2.13 Creating and Copying a Formula That Uses an Absolute Cell

                Reference

Objective 10 Find and Replace Text and Hide and Unhide Columns

                Activity 2.14 Using Find and Replace

                Activity 2.15 Hiding and Unhiding Columns

Objective 11 Conduct a What-If Analysis and Use Statistical Functions

                Activity 2.16 Performing a What-If Analysis and Using Statistical Functions

                Activity 2.17 Planning and Preparing a Worksheet

Objective 12 Create Accurate Worksheets with Accuracy Tools

                Activity 2.18 Inserting a Worksheet into a Workbook

                Activity 2.19 Using Undo and Redo

                Activity 2.20 Identifying and Correcting Errors in Formulas

                Activity 2.21 Formatting Page Placement and Adding Footers

                for Multiple Worksheets




 

Chapter 3 Working with IF Functions and Large Worksheets

 

Project 3A Payroll

 

Objective 1 Construct an IF Function

                Activity 3.1 Preparing Regular Hours Worked in a Payroll Report Using an

                IF Statement

                Activity 3.2 Reporting Overtime Hours Worked with an IF Formula

                Activity 3.3 Determining Payroll Deductions Using Absolute References

                in Formulas

                Activity 3.4 Formatting the Worksheet and Merging and Centering a Vertical

                Range of Cells

Objective 2 Link Data in Workbooks

                Activity 3.5 Constructing Formulas That Refer to Cells in Another Worksheet

Objective 3 Create IF Functions That Return Text

                Activity 3.6 Copying Cells and Creating an IF Formula to Enter Text Using

                Drag-and-Drop

Objective 4Emphasize Data Using Conditional Formatting

                Activity 3.7 Highlighting Results with a Conditional Format

                Activity 3.8 Correcting an Error and Reviewing Results

Objective 5 Format with Themes

                Activity 3.9 Formatting a Worksheet Using a Theme

                Activity 3.10 Changing a Theme and Font

Objective 6Add Information in the Header and Footer

                Activity 3.11 Editing the Footer and Header

 

Project 3B Enrollments

 

Objective 7 Enter Dates

                Activity 3.12 Entering a Date as Text

Objective 8 Format Large Worksheets

                Activity 3.13 Indenting Cell Contents and Using Zoom Control

                Activity 3.14 Adding Vertical Borders

                Activity 3.15 Freezing Panes

Objective 9 Apply Number Formats

                Activity 3.16 Formatting Numbers

                Activity 3.17 Using a Theme on Large Worksheets

Objective 10 Control Print Options

                Activity 3.18 Printing Gridlines and Headings

                Activity 3.19 Previewing and Modifying Page Breaks

                Activity 3.20 Setting Margins

                Activity 3.21 Inserting Page Numbers in a Header

                Activity 3.22 Setting the Print Area




 

Chapter 4 Creating charts and Tables, Sorting and Filtering Data

 

Project 4A Census

 

Objective 1 Use Text Orientation

                Activity 4.1 Rotating Text in Cells

Objective 2 Create a Column Chart

                Activity 4.2 Creating a 3-D Column Chart

Objective 3 Create a Chart Sheet and Edit the Chart

                Activity 4.3 Creating a Chart Sheet

                Activity 4.4 Changing Chart Type and Style

                Activity 4.5 Editing and Formatting a Chart Title

                Activity 4.6 Editing and Formatting a Chart Legend

                Activity 4.7 Adding Axis Titles and Editing Worksheet Data

Objective 4 Create and Modify a Pie Chart

                Activity 4.8 Creating a Pie Chart

                Activity 4.10 Rotating a Pie Chart

                Activity 4.11 Inserting a Text Box in a Chart

Objective 5 Apply a Theme to a Chart

                Activity 4.12 Changing the Theme in a Chart

Objective 6 Print Charts

                Activity 4.13 Inserting Headers and Footers for Chart Sheets

                Activity 4.14 Printing a Chart

 

Project 4B Employees

 

Objective 7 Sort Data

                Activity 4.15 Sorting Alphabetical Data

                Activity 4.16 Sorting by Numbers

                Activity 4.17 Sorting by Date

Objective 8 Convert Text into Columns

                Activity 4.18 Splitting Cell Contents into Multiple Cells and Copy and Paste

                Activity 4.19 Using the TRIM Function

Objective 9 Apply Conditional Formatting Using Data Bars

and Color Scales

                Activity 4.20 Highlighting Cell Rules

                Activity 4.21 Using Top/Bottom Rules

                Activity 4.22 Formatting and Editing Data Bars

                Activity 4.23 Formatting and Editing Color Scales

Objective 10 Insert a Table and Filter Data

                Activity 4.24 Filtering a Table

                Activity 4.25 Filtering and Sorting on Two Tables

                Activity 4.26 Filtering by Using Text and Number Filters

                Activity 4.27 Chart Filtered Data




 

Chapter 5 Making Decisions with Functions

 

Project 5A Building Permits

 

Objective 1 Create Text Functions

                Activity 5.1 Using the PROPER Function

Objective 2 Create Statistical Functions

                Activity 5.2 Using the MEDIAN and MODE Functions and Formula AutoComplete 462

                Activity 5.3 Counting Cells

Objective 3 Insert Date and Time Functions

                Activity 5.4 Using Date and Time Functions

                Activity 5.5 Using Dates in a Formula

Objective 4 Create Logical Functions and Insert a Comment

                Activity 5.6 Using the AND Function

                Activity 5.7 Using the OR Function

                Activity 5.8 Inserting a Comment

                Activity 5.9 Printing Comments

 

Project 5B City Financial

 

Objective 5 Insert Financial Functions

                Activity 5.10 Creating an Amortization Table

                Activity 5.11 Using the PMT (Payment) Function

                Activity 5.12 Using the IPMT (Interest Payment) Function

                Activity 5.13 Using the PPMT (Principal Payment) Function and Determining

                the Ending Balance

                Activity 5.14 Complete the Amortization Schedule

Objective 6 Create What-If Analysis with Goal Seek

                Activity 5.15 Using Goal Seek to Determine Length of Loan

Objective 7 Determine Future Value and Present Value

                Activity 5.16 Determining Present Value

                Activity 5.17 Determining Future Value




 

Chapter 6 Using Named Ranges, Templates, Lookup Values and 3-D References

 

Project 6A Third Quarter

 

Objective 1 Create Formulas Using Named Ranges

                Activity 6.1 Defining and Using a Named Cell

                Activity 6.2 Creating Names from Row and Column Titles

                Activity 6.3 Creating Formulas Using Named Ranges

                Activity 6.4 Inserting Rows Within a Range and Editing the Worksheet

                Activity 6.5 Using Named Cells to Create Quarterly Totals

Objective 2 Utilize Lookup Lists

                Activity 6.7 Creating a Lookup List

                Activity 6.8 Looking Up Text in a Worksheet Using VLOOKUP

                Activity 6.9 Editing the Lookup List

                Activity 6.10 Determining Sales Amounts Using the COUNTIF Function

Objective 3 Customize and Use Microsoft-Created Templates

                Activity 6.11 Downloading and Personalizing a Template

                Activity 6.12 Completing a Worksheet Using a Template

 

Project 6B Quarter 2 Sales

 

Objective 4 Transpose Data in a Worksheet and Apply Cell Styles

                Activity 6.13 Transposing Data in a Worksheet

                Activity 6.14 Applying Cell Styles to a Worksheet

                Activity 6.15 Linking Worksheet Data Using a 3-D Reference

                Activity 6.16 Linking Workbook Data with a 3-D Reference

                Activity 6.17 Saving a Workspace and Editing Linked Data

Objective 6 Create Hyperlinks

                Activity 6.18 Inserting a Hyperlink in a Worksheet

                Activity 6.19 Inserting a Hyperlink in Workbooks




 

Chapter 7 Importing Data, Expanding a Table, and utilizing Database Features

 

Project 7A Closed Cases

 

Objective 1 Create and Expand a Table and Insert a Calculated Column

                Activity 7.1 Creating and Expanding a Table

                Activity 7.2 Naming a Table

                Activity 7.3 Inserting a Calculated Column into the Table

Objective 2 Create and Sort a Custom List

                Activity 7.4 Creating a Custom List

Objective 3 Filter by Using Advanced Criteria

                Activity 7.5 Filtering on Two Number Criteria

                Activity 7.6 Filtering on a Calculated Field

Objective 4 Evaluate Data with Database Functions

                Activity 7.7 Determining the Average using the DAVERAGE Database Function

                Activity 7.8 Determining the Sum of Certain Contingencies Using the DSUM Function

                Activity 7.9 Counting Specific Records in a Database

 

Project 7B Clients

 

Objective 5 Import Data to Excel

                Activity 7.10 Importing an Access Database into Excel

                Activity 7.11 Importing a Word Document to Excel

                Activity 7.12 Copying an Excel Worksheet and Editing the Worksheet

Objective 6 Create Lookup Tables in Another Workbook

                Activity 7.14 Using an External Lookup

Objective 7 Enter Subtotals and Outline the Worksheet

                Activity 7.15 Subtotaling the Worksheet

Objective 8 Link and Embed a Worksheet and Chart into Word Documents

                Activity 7.16 Embedding a Worksheet into a Word Memo

                Activity 7.17 Linking a Worksheet and a Chart into a Word Memo

                Activity 7.18 Editing Linked and Embedded Data




 

Chapter 8 Creating Macros, Using Depreciation and Conditional Functions, and Creating PivotTables ad PivotChart Reports

 

Project 8A Depreciation

 

Objective 1 Create and Run a Macro

                Activity 8.1 Accessing the Developer Tab

                Activity 8.2 Creating a Macro

                Activity 8.3 Running the Macro

                Activity 8.5 Opening a Document that Contains a Macro

                Activity 8.6 Editing a Macro with VBA Code

Objective 2 Apply Depreciation Functions

                Activity 8.8 Depreciating an Asset Using the Sum-of-Years’ Digits Method

                Activity 8.9 Depreciating an Asset Using the Declining Balance Method

                Activity 8.10 Preparing a Schedule of Depreciation

                Activity 8.11 Create a Macro Using the Relative Reference Feature

 

Project 8B New Car Sales

 

Objective 3 Evaluate Worksheet Data with Conditional IF Functions

                Activity 8.12 Determining Individual Cars Sold Using the COUNTIF Function

                Activity 8.13 Determining Individual Sales Using the SUMIF Function

                Activity 8.14 Determining Individual Sales Using the AVERAGEIF Function

                Activity 8.15 Counting the Number of Hybrids Sold Using the COUNTIFS Function

                Activity 8.16 Determining Sales of a Car Make Using the SUMIFS Function

Objective 4 Create a PivotTable and PivotChart

                Activity 8.17 Creating a PivotTable

                Activity 8.18 Filtering Data

                Activity 8.19 Rearranging the Filtered Data to Display Sales Information

                Activity 8.20 Removing Filters to Display More Data

                Activity 8.21 Refreshing the Pivot Table and Displaying the Table

                on Separate Pages

                Activity 8.22 Establishing a Custom Calculation and Formatting

                the PivotTable

                Activity 8.23 Creating a Pivot Chart




 

Chapter 9 Inserting Graphic Elements into Worksheets and Charts

 

Project 9A Quarter1 Sales

 

Objective 1 Format with Graphic Images Including WordArt to Enhance Worksheets

                Activity 9.1 Formatting with Patterns

                Activity 9.2 Inserting and Formatting a Shape

                Activity 9.3 Formatting Text Within a Shape

                Activity 9.4 Inserting and Formatting WordArt

Objective 2 Insert SmartArt Graphics

                Activity 9.5 Inserting a SmartArt Organization Chart

                Activity 9.6 Inserting a Picture as Background

                Activity 9.7 Formatting SmartArt

Objective 3 Create, Edit, and Publish a Web Page

                Activity 9.8 Preparing a Web Page

                Activity 9.9 Inserting and Formatting Graphic Elements into a Web Page

                Activity 9.10 Insert Hyperlinks into a Web Page

                Activity 9.11 Saving and Publishing a Web Page

 

Project 9B SoAmerica

 

Objective 4 Create Specialized Charts

                Activity 9.12 Inserting a Line Chart

                Activity 9.13 Copy a Chart and Change Chart Type to Area Chart

                Activity 9.14 Format Data Series and Chart Area

                Activity 9.15 Rearrange Data Markers 7

                Activity 9.16 Inserting a Scatter Chart

                Activity 9.17 Inserting a Trend line

                Activity 9.18 Inserting Axis Titles 7

                Activity 9.19 Editing the Axis of a Chart

                Activity 9.20 Creating a Stock Chart 7

Objective 5 Format Column Charts with Pictures

and Other Graphic Elements

                Activity 9.21 Using Pictures to Format a 2-D Column Chart

                Activity 9.22 Using Pictures to Format a 3-D Column Chart 7




 

Chapter 10 Creating Templates and Creating and Validating Form

 

10A Project 10A Payroll Report

 

Objective 1 Protect Worksheet Elements

                Activity 10.1 Inserting a Picture into a Header

                Activity 10.2 Inserting Formulas and Formats into a Report

                Activity 10.3 Completing a Chart for the Worksheet

                Activity 10.4 Protecting Elements of the Worksheet

Objective 2 Unprotect Elements and Hide Formulas

                Activity 10.5 Unprotecting Cells in a Protected Worksheet

                Activity 10.6 Hiding Formulas and Other Sensitive Information

Objective 3 Protect a Workbook

                Activity 10.7 Protecting a Workbook's Structure and Windows

                Activity 10.8 Open a Protected Workbook

Objective 4 Save Worksheet and Chart Templates

                Activity 10.9 Saving a Workbook as a Template

                Activity 10.10 Creating a Chart to Save as a Template

Objective 5 Complete a Report with Worksheet and Chart Templates

                Activity 10.11 Creating a Worksheet and Charts Using Templates

 

10B Project 10B Time Card

 

Objective 6 Create a Form

                Activity 10.12 Inserting a VLOOKUP to Enter Data

                Activity 10.13 Inserting Formulas to Display Dates in a Form and Formatting the Input Area

                Activity 10.14 Creating the Output Area

                Activity 10.15 Completing the Formulas in the Output Area

Objective 7 Validate a Form

                Activity 10.16 Validating the Form

                Activity 10.17 Testing the Validation Rule

                Activity 10.18 Creating a Drop-Down List

                Activity 10.19 Hiding Formulas

Objective 8 Insert Macro Command Buttons

          Activity 10.20 Creating a Print Macro

                Activity 10.21 Creating a Macro to Clear the Input Area

                Activity 10.22 Inserting a Macro Command Button

                Activity 10.23 Protecting the Worksheet and Saving It as a Template

                Activity 10.24 Preparing Payroll Reports for a Week




 

Chapter 11 Nesting Functions and Consolidating Worksheets

 

11A Project 11A Server Bonus

 

Objective 1 Nest One Function within Another

                Activity 11.1 Creating a Nested Function

                Activity 11.2 Nesting IF Functions

                Activity 11.3 Nesting IF Functions to Determine the Incentive for Sales for one Month

                Activity 11.4 Nesting IF and AND Functions to Determine The Weekend Recipients

Objective 2 Use 3-D References and Nested Lookup

                Activity 11.5 Using Nested 3-D Reference

                Activity 11.6 Nesting a Lookup Function

Objective 3 Check Accuracy with Excel's Auditing Tools

Activity 11.7 Evaluating Formula

                Activity 11.8 Using Auditing Tools to Trace Precedents and Dependents

                Activity 11.9 Using Error Checking

                Activity 11.10 Using Watch Window

 

Project 11B Sales Report

 

Objective 4 Consolidate Workbooks

                Activity 11.11 Consolidating by Position

                Activity 11.12 Consolidating by Category

Objective 5 Share and Merge Workbooks

                Activity 11.13 Using a Shared Workbook

                Activity 11.14 Creating a Shared Workbook

                Activity 11.15 Entering Data into a Shared Workbook

                Activity 11.16 Display the Compare and Merge Button

                Activity 11.17 Merge Worksheets




 

Chapter 12 Working with Data Tables, Scenarios, Solver, XML, and the Document Inspector

 

12A Project 12A Analysis Tools

 

Objective 1 Create a Data Table

                Activity 12.1 Creating a One-variable Data Table

                Activity 12.2 Adding a Formula to One-Variable Data Table

                Activity 12.3 Creating a Two-Variable Data Table

                Activity 12.3 Creating a Two-Variable Data Table

Objective 2 Create a Scenario

                Activity 12.4 Creating and Displaying Scenario

                Activity 12.5 Creating a Scenario Summary Report

Objective 3 Use Solver

                Activity 12.6 Loading Solver

                Activity 12.7 Using Solver

 

12B Project 12B Price List

 

Objective 4 Prepare a Document for Distribution

                Activity 12.8 Changing the User Information

                Activity 12.9 Examine a File for Sensitive Information

                Activity 12.10 Using Document Properties

                Activity 12.11 Restricting Permissions Using Encryption and Information Rights Management

                Activity 12.12 Saving as PDF or XPS Formats

                Activity 12.13 Checking for Features Not Supported in Earlier Versions of Excel

                Activity 12.14 Using the Document Inspector

                Activity 12.15 Marking the Workbook as Final

Objective 5 Use XML to Enhance Security

                Activity 12.16 Renaming an Excel 2007 file and Displaying its Structure

                Activity 12.17 Identifying the Functions of Folders and Files

                Activity 12.18 Replacing a Picture in the Media Folder

                Activity 12.19 Identifying the Characteristics of an XML File

                Activity 12.20 Editing an XML File

                Activity 12.21 Checking XML Files for Valid Structure

                Activity 12.22 Replacing Parts and Zipping the Container File

                Activity 12.23 Renaming the File and Restoring Default Values