GO! with Microsoft Excel 2007, Volume 1

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–Create a Worksheet and Chart Data

        Objective 1: Create, Save, and Navigate an Excel Workbook

            Activity 1.1 Starting Excel and Naming and Saving a Workbook

            Activity 1.2 Navigating a Worksheet and a Workbook

            Activity 1.3 Selecting Parts of a Worksheet

        Objective 2: Enter and Edit Date in a Worksheet

            Activity 1.4 Entering Text, Using AutoComplete, Filling a Series with AutoFill, and Using Spelling Checker and Undo to Correct Typing Errors

            Activity 1.5 Aligning Text and Adjusting the Size of Columns and Rows

            Activity 1.6 Entering Numbers

            Activity 1.7 Inserting and Deleting Rows and Columns and Using the Insert Options Button

        Objective 3: Construct and Copy Formulas, Use the Sum Function, and Edit Cells

            Activity 1.8 Constructing a Formula, Using the Sum Function and Editing Numbers in Cells

            Activity 1.9 Copying a Formula by Using the Fill Handle

        Objective 4: Format Data, Cells, and Worksheets

            Activity 1.10 Formatting Financial Numbers, Using Column AutoFit, and Using Format Painter

            Activity 1.11 Formatting Text and Using Merge and Center

        Objective 5: Close and Reopen a Workbook

            Activity 1.12 Closing and Reopening an Existing Workbook

        Objective 6: Chart Data

            Activity 1.13 Charting Data

        Objective 7: Use Page Layout View, Prepare a Worksheet for Printing and Close Excel

            Activity 1.14 Changing Views, Creating a Footer and Using Print Preview

            Activity 1.15 Deleting Unused Sheets in a Workbook

            Activity 1.16 Printing a Worksheet

            Activity 1.17 Displaying, Printing, and Hiding Formulas

    Project 1B–Perform Calculations and Make Comparisons by Using a Pie Chart

        Objective 8: Design a Worksheet

            Activity 1.18 Setting Column Widths and Creating Row and Column Titles

            Activity 1.19 Entering Data by Range

        Objective 9: Construct Formulas for Mathematical Operations

            Activity 1.20 Using Arithmetic Operators

            Activity 1.21 Copying Formulas Containing Absolute Cell References

        Objective 10: Format Percentages and Move Formulas

            Activity 1.22 Formatting Cells with the Percent Style Button

            Activity 1.23 Inserting Rows in a Worksheet Containing Formulas and Wrapping Text in a Cell

        Objective 11: Create a Pie Chart and Chart Sheet

            Activity 1.24 Creating a Pie Chart and a Chart Sheet

        Objective 12: Use the Excel Help System

            Activity 1.25 Using the Excel Help System

 

Chapter 2–Managing Workbooks and Analyzing Data

    Project 2A Create a Summary Sheet from Multiple Worksheets

        Objective 1: Create and Save a Workbook from an Existing Workbook

            Activity 2.1 Creating and Saving a Workbook from an Existing Workbook

        Objective 2: Navigate a Workbook and Rename Worksheets

            Activity 2.2 Navigating Among Worksheets, Renaming Worksheets, and Changing the Tab Color of a Worksheet

        Objective 3: Enter Dates, Clear Contents, and Clear Formats

            Activity 2.3 Entering and Formatting Dates

            Activity 2.4 Clearing Cell Contents and Formats

        Objective 4: Move, Copy, and Paste Cell Contents

            Activity 2.5 Copying, Pasting, and Moving Cell Contents

        Objective 5: Edit and Format Multiple Worksheets at the Same Time

            Activity 2.6 Wrapping Text in a Cell in Several Worksheets at the Same Time

            Activity 2.7 Entering Data and Constructing Formulas on Multiple Worksheets

        Objective 6: Create a Summary Sheet

            Activity 2.8 Constructing Formulas that Refer to Cells in Another Worksheet

        Objective 7: Format and Print Multiple Worksheets in a Workbook

            Activity 2.9 Moving and Formatting Worksheets in a Workbook

            Activity 2.10 Printing All the Worksheets in a Workbook

    Project 2 B Growth Projection

        Objective 8: Design a Worksheet for What-If Analysis

            Activity 2.11 Using Parentheses in a Formula

            Activity 2.12 Formatting as You Type

            Activity 2.13 Calculating a Value After an Increase

        Objective 9: Perform What-If Analysis

            Activity 2.14 Performing What-If Analysis and Using Paste Special

        Objective 10: Compare Data with a Line Chart

            Activity 2.15 Creating a Line Chart

 

Chapter 3–Using Functions and Tables

    Project 3A–Track Inventory by Using Math, Logical, and Statistical Functions and by Creating an Excel Table

        Objective 1: Use SUM, AVERAGE, MEDIAN, MIN, and MAX Functions

            Activity 3.1 Using the SUM, AVERAGE, and MEDIAN Functions

            Activity 3.2 Using the MIN and MAX Functions

            Activity 3.3 Moving Data, Adding Borders, and Rotating Text

        Objective 2: Use COUNTIF and IF Functions, and Apply Conditional Formatting

            Activity 3.4 Using the COUNTIF Functions

            Activity 3.5 Using the IF Function and Applying Conditional Formatting

            Activity 3.6 Applying Conditional Formatting Using Custom Formats and Data Bars

            Activity 3.7 Using Find and Replace

        Objective 3: Use a Date Function

            Activity 3.8 Using the NOW Function

        Objective 4: Freeze Panes and Create an Excel Table

            Activity 3.9 Freezing and Unfreezing Panes

            Activity 3.10 Sorting and Filtering in an Excel Table

            Activity 3.11 Inserting a Second Table in a Worksheet

            Activity 3.12 Converting a Table to a Range of Data

        Objective 5: Format and Print a Large Worksheet

            Activity 3.13 Printing Large Worksheets

    Project 3B–Make Financial Decisions by Using Financial Functions and What-If Analysis

        Objective 6: Use Financial Functions

            Activity 3.14 Designing a Loan Worksheet

            Activity 3.15 Inserting the PMT Financial Function

        Objective 7: Use Goal Seek

            Activity 3.16 Using Goal Seek to Produce the Desired Result

            Activity 3.17 Using Goal Seek to Find an Increase Period

        Objective 8: Create a Data Table

            Activity 3.18 Designing a Two-Variable Data Table

            Activity 3.19 Using a Data Table to Calculate Options

 

Chapter 4 Using Templates, Range Names, and Lookup Functions     Project 4A: Using Excel Templates

        Objective 1: Open an Excel Template

            Activity 4.1 Opening an Excel Template

        Objective 2: Customize a Template

            Activity 4.2 Adding a Company Logo to a Template Using Word Art

            Activity 4.3 Adding ClipArt to the Company Logo

            Activity 4.4 Grouping Graphic Objects

            Activity 4.5 Adding a Formula to a Template

        Objective 3: Save a Workbook as a Template

            Activity 4.6 Saving a Workbook as a Template

        Objective 4: Enter Data in a Customized Template

            Activity 4.7 Opening and Using a Template

    Project 4B: Create a Protected Order Form Template Using Range Names and Lookup Functions

        Objective 5: Create Range Names (3.1.3)

            Activity 4.8 Naming a Range

            Activity 4.9 Modifying a Named Range

            Activity 4.10 Using Row and Column Titles to Name a Range

            Activity 4.11 Deleting a Range Name

        Objective 6: Ensure Data Integrity (1.2)

            Activity 4.12 Creating a Validation List (1.2.1)

        Objective 7: Use Lookup Functions

            Activity 4.13 Retrieving Data Using a Named Range in a VLOOKUP Formula

            Activity 4.14 Removing Error Codes Using IFERROR Function in a Nested IF Statement

            Activity 4.15 Creating an HLOOKUP Table

            Activity 4.16 Retrieving Data Using an HLOOKUP Formula

        Objective 8: Protect the Worksheet and Save it as a Template

            Activity 4.17 Hiding Rows Containing Lookup Tables

            Activity 4.18 Unlocking Cells for Data Entry and Protecting a Worksheet

            Activity 4.19 Hiding Formulas in the Worksheet

            Activity 4.20 Setting a Print Area

            Activity 4.21 Saving the Order Form as a Template

 

Chapter 5 Importing Data and Using Excel as a Database

    Project A: Real Estate Company — Import list of customers, and massage list to send Holiday letters, notices of new houses on the market etc.

        Objective 1: Import Data into Excel

            Activity 5.1 Importing Data from a Word Table

            Activity 5.2 Importing Data from a TXT File

            Activity 5.3 Importing Data from Access

            Activity 5.4 Importing Data from a Comma Delimited File

        Objective 2: Clean-up Data from External Sources

            Activity 5.5 Converting Text to Columns

            Activity 5.6 Using TRIM Function to Remove Spaces

            Activity 5.7 Consolidating Data

            Activity 5.8 Highlight Duplicate Values with Conditional Formatting

            Activity 5.9 Removing Duplicate Records

    Project B Table of Real Estate Sales Listings

        Objective 3: Analyze Data with Excel Database Tools

            Activity 5.10 Creating a Table and Printing the Results

            Activity 5.11 Adding Data and Using Custom AutoFilters

            Activity 5.12 Creating Criteria Using Advanced Filter Criteria

            Activity 5.13 Extracting Data to a New Area on the Worksheet

            Activity 5.14 Using Dates in a Calculated Field

            Activity 5.15 Subtotaling, Grouping, and Outlining Data

        Objective 4: Use Database Functions (3.3)

            Activity 5.16 Using the DAVERAGE Database Function

            Activity 5.17 Using the DSUM Database Function

            Activity 5.18 Using DCOOUNTA Function

            Activity 5.19 Using DGET Function

            Activity 5.20 Controlling Print Options

 

Chapter 6–Summarizing Data and Making Business Decision     Project A: Grocery Sales data by region/store/department

        Objective 1: Create and Modify PivotTable Reports

            Activity 6.1 Organizing Data to Display in a PivotTable

            Activity 6.2 Creating a PivotTable

            Activity 6.3 Changing the Value Summary

            Activity 6.4 Creating a PivotTable with Subheadings

            Activity 6.5 Formatting a PivotTable Report

        Objective 2: Create PivotChart Reports

            Activity 6.6 Creating a PivotChart Report

            Activity 6.7 Modifying and Moving PivotChart

            Activity 6.8 Printing PivotTables and PivotCharts

    Project B

        Objective 3: Compare Dates and Round Numbers

            Activity 6.9 Determining the Number of Years Between Two Dates

            Activity 6.10 Creating Whole Numbers with ROUND Functions

        Objective 5: Apply Business Decisions with Logical Operators

            Activity 6.11 Applying a Logical Test to a Cell Value and Using the AND Function

            Activity 6.12 Applying the NOT Function

            Activity 6.13 Applying the OR Function

            Activity 6.14 Writing a Nested IF Statement to Determine the Results

        Objective 6: Insert Comments and Document a Workbook

            Activity 6.15 Inserting Comments

            Activity 6.16 Changing the Workbook Properties

            Activity 6.17 Printing Comments