GO! with Excel 2007 Volume 1

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 2B 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 Tabl        

        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 Using Word Art to Add a Company Logo to a Template

            Activity 4.3 Adding ClipArt to the Company Logo

            Activity 4.4 Grouping Graphic Objects

            Activity 4.5 Adding a Formula to a Template

            Activity 4.6 Adding Comments to a Cell

        Objective 3: Save a Workbook as a Template

            Activity 4.7 Saving the Workbook as a Template

        Objective 4: Use a Customized Template

            Activity 4.8 Opening and Completing a Customized 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.9 Naming a Range

            Activity 4.10 Modifying a Named Range

            Activity 4.11 Using Row and Column Labels to Name a Range

            Activity 4.12 Deleting a Range Name

        Objective 6: Ensure Data Integrity (1.2)

            Activity 4.13 Creating a Validation List (1.2.1)

        Objective 7: Use Lookup Functions

            Activity 4.14 Using a Named Range in a VLOOKUP Formula to Retrieve Data

            Activity 4.15 Using IFERROR Function in a Nested IF Statement to Remove Error

            Codes

            Activity 4.16 Creating an HLOOKUP Table

            Activity 4.17 Using an HLOOKUP Formula to Retrieve Data

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

            Activity 4.18 Hiding Rows Containing Lookup Tables

            Activity 4.19 Unlocking Cells for Data Entry and Protecting a Worksheet

            Activity 4.20 Hiding Formulas in the Worksheet

            Activity 4.21 Setting a Print Area

            Activity 4.23 Saving the Order Form as a Template

 

Chapter 5 Importing Data and Using Database Tools to Extract

Information

 

    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 CSV File

    Objective 2: Clean-up Data from External Sources

            Activity 5.5 Consolidate Data

            Activity 5.6 Converting Text to Columns

            Activity 5.7 Using TRIM Function to Remove Spaces

            Activity 5.8 Use Conditional Format to Highlight Duplicate Values

            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 Using Custom Filters

            Activity 5.12 Creating Criteria Using Advanced Filter Criteria

            Activity 5.12 Extracting Data to a New Area on the Worksheet

            Activity 5.13 Using Subtotals

            Activity 5.14 Adding a Calculated Field to a Filtered List

            Activity 5.15 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 DCOOUNT

            Activity 5.19 Using DGET

 

Chapter 6–Summarizing Data with Pivot Tables and Conditional Summary

Formulas

 

    Project A: Grocery Sales data by region/store/department

        Objective 1: Create Pivot Table Report

            Activity 6.1 Organizing Data to Display in a PivotTable

            Activity 6.2 Naming the Table Range

            Activity 6.3 Using the PivotTable and PivotChart Wizard

            Activity 6.4 Creating a PivotTable from Data in the Existing Table

            Activity 6.5 Limiting PivotTable Data

        Objective 2: Modify a Pivot Table Report

            Activity 6.6 Rearrange a PivotTable Report

            Activity 6.7 Displaying Pivot Table Details in a New Workbook

            Activity 6.8 Displaying PivotTable Data on Separate Pages

            Activity 6.9 Changing Calculations in a PivotTable Report

            Activity 6.10 Formatting a PivotTable

            Activity 6.11 Updating PivotTable Report Data

        Objective 3: Create a Pivot Chart Report

            Activity 6.12 Creating a PivotChart Report from a PivotTable Report

            Activity 6.13 Modifying a PivotChart Report

 

    Project B

        Objective 4: Use Logical Operators to Make Decisions

            Activity 6.14 Using the And Logical Operator

            Activity 6.15 Using the OR Logical Operator

            Activity 6.16 Using the NOT Logical Operator

            Activity 6.17 Creating a Nested Statement Using Logical Operators

        Objective 5: Use the Auditing Toolbar to Analyze a Worksheet

            Activity 6.18 Using the Audit Toolbar to Analyze a Worksheet

        Objective 6: Set Workbook Properties

            Activity 6.19 Setting Workbook Properties