Exploring Microsoft Office Excel 2007 Comprehensive

Chapter 1 - Introduction to Excel

 

Section 1: Introduction to Spreadsheets

    Defining Worksheets and Workbooks

    Using Spreadsheets Across Disciplines

    Planning for Good Workbook and Worksheet Design

    Identifying Excel Window Components

    Entering and Editing Data in Cells

        Hands-On Exercise #1:  Introduction to Microsoft Excel

 

Section 2: Mathematics and Formulas

    Describing and Using Symbols and the Order of Precedence

    Displaying Cell Formulas

    Inserting and Deleting Rows and Columns

    Using Cell Ranges, Excel Move, Copy, Paste and Paste Special, and AutoFill

        Hands-On Exercise #2:  Jakes Gym Continued

 

Section 3: Workbook and Worksheet Enhancements

    Managing Worksheets

    Formatting Worksheets

        Hands-On Exercise #3: Formatting Jake's Gym Worksheet

 

Section 4: Page Setup and Printing

    Selecting Page Setup Options for Printing

    Managing Cell Comments

        Hands-On Exercise #4: Printing Jake's Gym Worksheet

 

Summary

Key Terms

Multiple Choice

Practice Exercises

Mid-Level Exercises

Capstone Exercise

Mini Cases

 

Chapter 2 - Microsoft Excel: Formulas and Functions 

 

Section 1: Formula Basics         

    Creating and Copying Formulas  

    Using Relative and Absolute Cell Addresses        

        Hands-On Exercise #1:  Smithtown Hospital Radiology Department Payroll           

           

Section 2: Function Basics        

    Using AutoSum

    Inserting Basic Statistical Functions       

    Using Date Functions    

        Hands-On Exercise #2:  Completing the Smithtown Hospital Radiology Department

 

Section 3: Logical and Lookup Functions

    Using the IF Function

    Using the VLOOKUP Function

        Hands-On Exercise #3:  Athletic Department Eligibility Gradebook

           

Section 4: Financial Functions   

    Using the PMT Function

    Using the FV Function   

        Hands-On Exercise #4: Purchasing a Van for the School for Exceptional Children  

 

 

Summary         

Key Terms       

Multiple Choice 

Practice Exercises        

Mid-Level Exercises      

Capstone Exercise        

Mini Cases

 

Chapter 3 - Microsoft Excel: Charts

 

Section 1: A Picture is the Message

    Choosing A Chart Type

    Creating a Chart

        Hands-on Exercise 1: The First Chart

 

Section 2: Chart Enhancements

    Modifying a Chart

    Enhancing Charts with Graphic Shapes

        Hands-on Exercise 2: Multiple Data Series

 

Section 3: Chart Distribution

    Embedding Charts

    Printing Charts

        Hands-on Exercise 3: Embedding, Printing, And Saving Chart as a Web Page

 

Summary

Key Terms

Multiple Choice

Practice Exercises

Mid-Level Exercises

Capstone Exercise

Mini Cases

 

Chapter 4: Microsoft Excel: Working with Large Worksheets and Tables

 

Section 1: Large Worksheet Preparation

    Freezing Rows and Columns

    Hiding and Unhiding Rows, Columns, and Worksheets

    Protecting a Cell, a Worksheet and a Workbook

    Controlling Calculation

    Printing Large Worksheets

        Hands-On Exercise #1: Marching Band Roster

 

Section 2: Excel Data Tables

    Exploring Basic Table Management

    Sorting Data

    Filtering and Totaling Data

        Hands-On Exercise #2: Marching Band Roster Revisited

 

Summary

Key Terms

Multiple Choice

Practice Exercises

Mid-Level Exercises

Capstone Exercise

Mini Cases

 

Chapter 5 - Data to Information

 

Section 1 Table Management

    Designing Tables Based on Table Data Theory

    Importing Data from Text Files and Other Sources

   Applying Conditional Formatting

        Hands-on Exercise 1:  Gee Airlines Human Resources Department

 

Section 2 Data Management

    Applying Advanced Filtering and Sorting Methods

    Creating and Using Range Names

    Using Database Functions

        Hands-on Exercise 2: Gee Airlines Human Resources Department Revisited

 

Section 3 Data Analysis with PivotTables and PivotCharts

    Creating and Deleting PivotTables and PivotChart Reports

    Formatting, Sorting, Filtering, Subtotaling, and Refreshing a PivotTable

        Hands-on Exercise 3: Eye First Advertising Department Sales

 

    Summary

    Key Terms

    Multiple Choice

    Practice Exercises

    Mid-Level Exercises

    Capstone Exercise

    Mini Cases

 

Chapter 6 — Data Tables and Amortization Tables      

 

Section 1 Tables

    Separating and Combining Text

    Manipulating Text with Functions

        Hands-on Exercise 1: IT Department String Manipulation

 

Section 2 Data Analysis and Windows

    Identifying and Removing Duplicate Rows

    Grouping and Subtotaling Data

    Working with Windows

        Hands-on Exercise 2: Ajax College Band

 

Section 3 Conditional and Logical Functions

    Using Conditions in Functions

    Creating Nested IF Functions

    Using AND, OR, NOT and IFERROR Functions

        Hands-on Exercise 3: Classic Cars and Security

 

Section 4 Amortization Table

    Defining the Amortization Table

    Using Functions in Amortization Tables

        Hands-on Exercise 4: Purchase a New House

 

    Summary         

    Key Terms       

    Multiple Choice 

    Practice Exercises        

    Mid-Level Exercises      

    Capstone Exercise        

    Mini Cases

 

Chapter 7 — Data Consolidation, Links, and Formula Auditing

 

Section 1 Data Consolidation

    Consolidating Data from Multiple Worksheets

    Defining the Three-Dimensional Workbook

    Creating Three-Dimensional References in Formulas

        Hands-on Exercise 1: Consolidating Best Restaurant Corporate Sales

 

Section 2 Workbook Linking and Documentation

    Linking Workbooks

    Creating the Documentation Worksheet

        Hands-on Exercise 2: Consolidating Workbooks for Corporate Sales and Adding Documentation

 

Section 3 Workbook Auditing

    Restricting Values to a Drop-Down List

    Validating Data

    Auditing Formulas

    Setting up a Watch Window

        Hands-on Exercise 3: JAS Manufacturing

 

    Summary

    Key Terms

    Multiple Choice

    Practice Exercises

    Mid-Level Exercises

    Capstone Exercise

    Mini Cases

 

Chapter 8 — What-If Analysis

 

Section 1 One- and Two- Variable Data Tables

    Creating a One-Variable Data Table

    Creating a Two-Variable Data Table

        Hands-on Exercise 1: Can You Afford It?

 

Section 2 Goal Seek and Scenario Manager

    Defining Forecasting

    Solving Problems with Goal Seek

    Using Scenario Manager

        Hands-on Exercise 2: Purchase Golf Cars and Manufacture Golf Clubs

 

Section 3 Solver

    Loading the Solver Add-In

    Solving Problems with Solver

        Hands-on Exercise 3: FAL Computing Manufacturing

 

    Summary

    Key Terms

    Multiple Choice

    Practice Exercises

    Mid-Level Exercises

    Capstone Exercise

    Mini Cases

 

Chapter 9 - Collaboration and Workbook Distribution

 

Section 1 Excel Collaboration and Workbook Sharing

    Tracking Changes

    Enabling Simultaneous Changes by Multiple Users

    Saving Workbooks in Different Formats

    Sending a Workbook by E-mail

    Copying Data to Word and PowerPoint

        Hands-on Exercise 1: Collaborate and Share

 

Section 2 Workbook Distribution

    Finalizing Documents

    Preparing Workbooks for Distribution

        Hands-on Exercise 2: Distribute and Secure Workbooks

 

Section 3 Excel Options and Add-ins

    Understanding Excel Options

    Working with the Quick Access Toolbar

    Adding Add-ins

        Hands-on Exercise 3: Customizing Excel

 

    Summary

    Key Terms

    Practice Exercises

    Mid-Level Exercises

    Capstone Project

    Mini Cases

 

Chapter 10 — Templates, Styles, Web, and Macros

 

Section 1 Worksheet Automation

    Working with Existing Templates, Themes, and Styles

    Using Styles for Advanced Formatting

    Creating a Template

        Hands-on Exercise 1: Use and Create Themes, Styles, and Templates

 

Section 2 Web Pages and Queries

    Creating a Web Page

    Creating Web Queries

        Hands-on Exercise 2: Query the Web and Create a Web Page

 

Section 3 Repetitive Tasks

    Working with Macros

    Creating Macro Buttons

    Understanding the Basics of VBA

        Hands-on Exercise 3: Work with Macros and Visual Basic for Applications

 

    Summary

    Key Terms

    Multiple Choice

    Practice Exercises

    Mid-Level Exercises

    Capstone Exercise

    Mini Cases