GO! with Microsoft Office 2007, Intermediate

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

Word - Working with Templates, Styles, and Charts

    PROJECT 1A Templates and Styles

        Objective 1: Create a Document from an Existing Template

            Activity 1.1  Locating and Opening a Word Template

            Activity 1.2  Replacing Content Controls in a Template

            Activity 1.3  Replacing Content Control Text with Text from Another Document

        Objective 2: Apply and Modify Existing Styles and Create New Styles

            Activity 1.4  Displaying Existing Styles

            Activity 1.5  Formatting Text and Paragraphs Using Existing Styles

            Activity 1.6  Creating a New Paragraph Style

            Activity 1.7  Modifying Styles

        Objective 3: Apply a Theme and Create a New Template from an Existing Document

            Activity 1.8  Adding a Theme to a Document

            Activity 1.9  Saving an Existing Document as a Template

    PROJECT 1B Charts

        Objective 4: Create a Chart

            Activity 1.10            Inserting a Chart into a Document

            Activity 1.11            Adding Data and Data Labels to the Worksheet

            Activity 1.12            Changing the Data in a Chart

            Activity 1.13            Adding New Data to the Chart

        Objective 5: Format a Chart

            Activity 1.14            Changing the Chart Type

            Activity 1.15            Adding a Chart Title

            Activity 1.16            Changing the Vertical Axis Scale

            Activity 1.17            Repositioning the Chart Legend

            Activity 1.18            Adding Data Labels and Axis Labels

            Activity 1.19            Changing the Chart Style

        Objective 6: Work with Sections

            Activity 1.20            Adding Section Breaks

            Activity 1.21            Applying Different Formats to Document Sections

            There’s More You Can Do!

            Creating a Template Using Content Controls

 

Chapter 2

Word - Creating Form Letters and Working in Groups

    PROJECT 2A Mail Merge

        Objective 1: Create a Form Letter

            Activity 2.1  Setting Up a Form Letter

            Activity 2.2  Selecting a Data Source

            Activity 2.3  Editing, Filtering, and Sorting a Data Source

            Activity 2.4  Inserting Merge Fields

        Objective 2: Merge Letters with Records from the Data Source

            Activity 2.5  Previewing Merged Data

            Activity 2.6  Printing Form Letters

            Activity 2.7  Merging Form Letters into a Single Word Document

    PROJECT 2B Outlines and Collaborative Editing

        Objective 3: Work with a Document Outline

            Activity 2.8  Using Styles to Define Outline Levels

            Activity 2.9  Expanding and Collapsing Outline Sections

            Activity 2.10            Moving Blocks of Text Using the Outline

            Activity 2.11            Apply a List Format to Outline Headings

        Objective 4: Track Changes in a Document

            Activity 2.12            Turning on Track Changes

            Activity 2.13            Locating Changes in a Document

            Activity 2.14            Accepting or Rejecting Tracked Changes

        Objective 5: Use Comments in a Document

            Activity 2.15            Adding a Comment to a Document

            Activity 2.16            Editing and Deleting Document Comments

        Objective 6: Compare and Combine Documents

            Activity 2.17            Comparing Two Documents

            There’s More You Can Do!

            Protecting a Document and Adding a Watermark

 

Chapter 3

Excel - Using Templates, Range Names, and Lookup Functions

    Project 3A: Using Excel Templates

        Objective 1: Open an Excel Template

            Activity 3.1 Opening an Excel Template

        Objective 2: Customize a Template

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

            Activity 3.3 Adding ClipArt to the Company Logo

            Activity 3.4 Grouping Graphic Objects

            Activity 3.5 Adding a Formula to a Template

        Objective 3: Save a Workbook as a Template

            Activity 3.6 Saving a Workbook as a Template

        Objective 4: Enter Data in a Customized Template

            Activity 3.7 Opening and Using a Template

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

        Objective 5: Create Range Names (3.1.3)

            Activity 3.8 Naming a Range

            Activity 3.9 Modifying a Named Range

            Activity 3.10 Using Row and Column Titles to Name a Range

            Activity 3.11 Deleting a Range Name

        Objective 6: Ensure Data Integrity (1.2)

            Activity 3.12 Creating a Validation List (1.2.1)

        Objective 7: Use Lookup Functions

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

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

            Activity 3.15 Creating an HLOOKUP Table

            Activity 3.16 Retrieving Data Using an HLOOKUP Formula

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

            Activity 3.17 Hiding Rows Containing Lookup Tables

            Activity 3.18 Unlocking Cells for Data Entry and Protecting a Worksheet

            Activity 3.19 Hiding Formulas in the Worksheet

            Activity 3.20 Setting a Print Area

            Activity 3.21 Saving the Order Form as a Template

 

Chapter 4

Excel -  Importing Data and Using Excel as a Database

    PROJECT 4A: 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 4.1 Importing Data from a Word Table

            Activity 4.2 Importing Data from a TXT File

            Activity 4.3 Importing Data from Access

            Activity 4.4 Importing Data from a Comma Delimited File

        Objective 2: Clean-up Data from External Sources

            Activity 4.5 Converting Text to Columns

            Activity 4.6 Using TRIM Function to Remove Spaces

            Activity 4.7 Consolidating Data

            Activity 4.8 Highlight Duplicate Values with Conditional Formatting

            Activity 4.9 Removing Duplicate Records

    Project 4B Table of Real Estate Sales Listings

        Objective 3: Analyze Data with Excel Database Tools

            Activity 4.10 Creating a Table and Printing the Results

            Activity 4.11 Adding Data and Using Custom AutoFilters

            Activity 4.12 Creating Criteria Using Advanced Filter Criteria

            Activity 4.13 Extracting Data to a New Area on the Worksheet

            Activity 4.14 Using Dates in a Calculated Field

            Activity 4.15 Subtotaling, Grouping, and Outlining Data

        Objective 4: Use Database Functions (3.3)

            Activity 4.16 Using the DAVERAGE Database Function

            Activity 4.17 Using the DSUM Database Function

            Activity 4.18 Using DCOOUNTA Function

            Activity 4.19 Using DGET Function

            Activity 4.20 Controlling Print Options

 

Chapter 5

Excel - Summarizing Data and Making Business Decision

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

        Objective 1: Create and Modify PivotTable Reports

            Activity 5.1 Organizing Data to Display in a PivotTable

            Activity 5.2 Creating a PivotTable

            Activity 5.3 Changing the Value Summary

            Activity 5.4 Creating a PivotTable with Subheadings

            Activity 5.5 Formatting a PivotTable Report

        Objective 2: Create PivotChart Reports

            Activity 5.6 Creating a PivotChart Report

            Activity 5.7 Modifying and Moving PivotChart

            Activity 5.8 Printing PivotTables and PivotCharts

    Project 5B

        Objective 3: Compare Dates and Round Numbers

            Activity 5.9 Determining the Number of Years Between Two Dates

            Activity 5.10 Creating Whole Numbers with ROUND Functions

        Objective 5: Apply Business Decisions with Logical Operators

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

            Activity 5.12 Applying the NOT Function

            Activity 5.13 Applying the OR Function

            Activity 5.14 Writing a Nested IF Statement to Determine the Results

        Objective 6: Insert Comments and Document a Workbook

            Activity 5.15 Inserting Comments

            Activity 5.16 Changing the Workbook Properties

            Activity 5.17 Printing Comments

 

Chapter 6

Access - Designing and Building a Relational Database

    PROJECT 6A Classes

        Objective 1: Customize Table Fields

            Activity 6.1 Adding Captions to Fields

            Activity 6.2 Creating a Yes/No Field and Set a Default Value

            Activity 6.3 Creating and populating an Attachment Field

        Objective 2: Structure Data Input

             Activity 6.4 Creating a Lookup Field from a Typed List

             Activity 6.5 Creating a Lookup Field from a Table

             Activity 6.6 Creating an Input Mask Using a Wizard

             Activity 6.7 Creating a Custom Input Mask

        Objective 3: Validate Data Input

            Activity 6.8 Classifying Fields as Required

            Activity 6.9 Assigning Default Values to Fields

            Activity 6.10 Setting Validation Properties

            Activity 6.11 Testing a Validation System in a For

    PROJECT 6B Registration

        Objective 4: Create Indexes and Change Sort Order

            Activity 6.12 Creating an Index to Optimize Sorting

            Activity 6.13 Creating an Index to Maintain Data Integrity

        Objective 5: Create Relationships between Tables

            Activity 6.14 Establishing a One-to-One Relationship

            Activity 6.15 Working with Related Tables

            Activity 6.16 Creating a One-to-Many Relationship

            Activity 6.17 Creating a Many-to-Many Relationship

        Objective 6: Create Forms and Reports with Related Tables

            Activity 6.18 Creating a Relationships Report

            Activity 6.19 Creating a Query Using Related Tables

            Activity 6.20 Creating a Form Using Related Tables

 

Chapter 7  

Access - Automate Data Entry with Forms

    PROJECT 7A - Sales Force

        Objective 1: Create Forms with Subforms

            Activity 7.1 Creating One-to-Many Relationships

            Activity 7.2 Creating a Form and Subform using the Form Wizard

            Activity 7.3 Creating a Form Using the Blank Form Tool

            Activity 7.4 Adding a Subform using the Subform Control

            Activity 7.5 Formatting a Form and Subform in Layout View

        Objective 2: Create Forms with Special Views

            Activity 7.6 Creating a Split Form

            Activity 7.7 Creating a Multiple Items Form

            Activity 7.8 Creating a From with Tabbed Pages

    PROJECT 5B - Opportunities

        Objective 4: Build a Form in Design View

            Activity 7.9 Building a Form in Design View

            Activity 7.10 Aligning Controls Using Arrange Tools

            Activity 7.11 Creating a Combo Box with Data from a Related Table

            Activity 7.12 Adding a List Box and a Combo Box

            Activity 7.13 Adding a Calculated Control

            Activity 7.14 Adding an Option Button Group

            Activity 7.15 Adding a Command Button

            Activity 7.16 Inserting a Chart

        Objective 5: Use Advanced Formatting Tools

            Activity 7.17 Applying Conditional Formatting

            Activity 7.18 Applying Rich Text Formatting to Data

 

Chapter 8

Access - Customize Data Output with Reports

    PROJECT 8A Orders

        Objective 1: Build Reports Based on Queries

            Activity 8.1 Building a Select Query

            Activity 8.2 Building a report Based on a Select Query

            Activity 8.3 Adding Line Numbers and a Record Count

            Activity 8.4 Optimizing a Report for Black and White Printing

            Activity 8.5 Building and Modifying Queries Using Copy and Paste

            Activity 8.6 Building and Modifying Reports Using Copy and Paste

        Objective 2: Export a Report and Create a Labels Report

             Activity 8.7 Exporting Reports as Web Pages

             Activity 8.8 Creating an Aggregate Function Query

             Activity 8.9 Creating a Labels Report

    PROJECT 8B Sales

        Objective 4: Create a Subreport Using Design Tools

             Activity 8.10 Creating a Report Using Report Design

             Activity 8.11 Creating a Subreport using the SubReport Wizard

             Activity 8.12 Formatting a Report and Subreport

             Activity 8.13 Adding Calculated Controls

        Objective 5: Summarize Report Data

            Activity 8.14 Creating a Summary Report

            Activity 8.15 Inserting a Chart

            Activity 8.16 Editing a Chart

            Activity 8.17 Filtering and Printing a Report

        Objective 6: Create a Report with an Interactive Filter

            Activity 8.18 Building a Daily Sales Report

            Activity 8.19 Creating a Filter That Asks for User Input

 

Chapter 9

Power Point -  Enhance a Presentation with Advanced Table, Chart, and Animation Techniques

    PROJECT 9A Recreation Analysis

        Objective 1:  Format a Table

            Activity 9.1 Creating a Table

            Activity 9.2 Merging Table Cells

            Activity 9.3 Adjusting Column Width and Table Size

            Activity 9.4 Changing Text Direction

        Objective 2:  Use the Draw Borders Feature to Modify a Table

            Activity 9.5 Inserting a Table on a Title Only Slide

            Activity 9.6 Applying Borders to a Table Using the Draw Table Feature

            Activity 9.7 Using the Eraser to Merge Cells

            Activity 9.8 Filling a Cell with a Picture

    PROJECT 9B

        Objective 3: Create and Modify a Pie Chart

            Activity 9.9 Creating a Pie Chart and Widening an Excel Column

            Activity 9.10 Modifying Chart Layout and Data Labels

            Activity 9.11 Selecting and Formatting Chart Elements

            Activity 9.12 Exploding a Pie Slice

        Objective 4: Apply Custom Animation Effects

            Activity 9.13 Adding Entrance and Emphasis Effects

            Activity 9.14 Adding Exit Effects

            Activity 9.15 Changing Animation Order and Setting Animation Timing

            Activity 9.16 Changing and Removing Animation Effects

 

Chapter 10

Power Point - Delivering Custom Presentations

    PROJECT 10A Learning Community

        Objective 1: Use Graphic Elements to Enhance a Slide

            Activity 10.1 Inserting and Recoloring a Picture

            Activity 10.2 Rotating a Picture

            Activity 10.3 Cropping a Picture

            Activity 10.4 Changing Object Order

        Objective 2: Work with Grouped Objects

            Activity 10.5 Grouping Objects

            Activity 10.6 Moving, Sizing, and Formatting Grouped Objects

            Activity 10.7 Save a Group as a Picture

    PROJECT 10B Business Certificate

        Objective 3: Insert Hyperlinks

            Activity 10.8 Inserting a Hyperlink to a Web Page

            Activity 10.9 Inserting a Hyperlink to a Slide

            Activity 10.10 Creating an Action Button to Link to Another Slide

            Activity 10.11 Hiding a Slide

        Objective 4: Create and Deliver Custom Shows

            Activity 10.12 Creating a Custom Show

            Activity 10.13 Creating a Hyperlink to a Custom Show

            Activity 10.14 Using Onscreen Navigation Tools

 

Chapter 11

Power Point - Create, Modify, and Share Presentations by Collaborating with Others

    PROJECT 11A

        Objective 1: Create a Template by Modifying Slide Masters

            Activity 11.1 Displaying and Editing Slide Masters

            Activity 11.2 Saving a Presentation as a Template

            Activity 11.2 Formatting a Slide Master with a Gradient Fill

            Activity 11.4 Formatting a Slide Master by adding Pictures and Shapes

            Activity 11.5 Modifying Placeholders on a Slide Master

            Activity 11.6 Applying a Template to a Presentation

        Objective 2: Edit a Presentation

            Activity 11.7 Adding Comments to a Presentation

            Activity 11.8 Using Find and Replacing

    PROJECT 11B Album

        Objective 3: Create a Photo Album

            Activity 11.9 Inserting a Photo Album

            Activity 11.10 Modifying a Photo Album

        Objective 4: Share Files with Other Users

            Activity 11.11 Importing a Word Outline

            Activity 11.12 Printing Handouts in Microsoft Word

            Activity 11.13 Saving a Presentation as a Web Page