GO! with Microsoft Excel 2010, 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.   Alicia Vargas is a faculty member in Business Information Technology at Pasadena City College.  She holds a master's and a bachelor's degree in business education from California State University, Los Angeles, and has authored several textbooks and training manuals on Microsoft Word, Microsoft Excel, and Microsoft PowerPoint.   Suzanne Marks is a faculty member in Business Technology Systems at Bellevue Community College, Bellevue, Washington. She holds a bachelor’s degree in business education from Washington State University, and was project manager for the first IT Skills Standards in the United States.

GO! with Excel 2010 Contents

 

Common Features

 

Chapter 1 Using the Common Features of Microsoft Office 2010

PROJECT 1A PowerPoint File

Objective 1 Use Windows Explorer to Locate Files and Folders

Activity 1.01 Using Windows Explorer to Locate Files and Folders

Objective 2 Locate and Start a Microsoft Office 2010 Program

Activity 1.02 Locating and Starting a Microsoft Office 2010 Program

Objective 3 Enter and Edit Text in an Office 2010 Program

Activity 1.03 Entering and Editing Text in an Office 2010 Program

Objective 4 Perform Commands from a Dialog Box

Activity 1.04 Performing Commands from a Dialog Box

Objective 5 Create a Folder, Save a File, and Close a Program

Activity 1.05 Creating a Folder, Saving a File, and Closing a Program

Objective 6 Add Document Properties and Print a File

Activity 1.06 Adding Document Properties and Printing a File

PROJECT 1B Word File

Objective 7 Open an Existing File and Save it with a New Name

Activity 1.07 Opening an Existing File and Saving it with a New Name

Objective 8 Explore Options for an Application

Activity 1.08 Viewing Application Options

Objective 9 Perform Commands from the Ribbon

Activity 1.09 Performing Commands from the Ribbon

Activity 1.10 Minimizing and Using the Keyboard to Control the Ribbon

Objective 10 Apply Formatting in Office Programs

Activity 1.11 Formatting and Viewing Pages

Activity 1.12 Formatting Text

Activity 1.13 Using the Office Clipboard to Cut, Copy, and Paste

Activity 1.14 Viewing Print Preview and Printing a Word Document

Objective 11 Use the Microsoft Office 2010 Help System

Activity 1.15 Using the Microsoft Office 2010 Help System in Excel

Objective 12 Compress Files

Activity 1.16 Compressing Files

 

Excel

 

Chapter 1 Creating a Worksheet and Charting Data

PROJECT 1A Sales Report with Embedded Column Chart and Sparklines

Objective 1 Create, Save, and Navigate an Excel Workbook

Activity 1.01 Starting Excel and Naming and Saving a Workbook

Activity 1.02 Navigating a Worksheet and a Workbook

Objective 2 Enter Data in a Worksheet

Activity 1.03 Entering Text and Using AutoComplete

Activity 1.04 Using Auto Fill and Keyboard Shortcuts

Activity 1.05 Aligning Text and Adjusting the Size of Columns

Activity 1.06 Entering Numbers

Objective 3 Construct and Copy Formulas and Use the SUM Function

Activity 1.07 Constructing a Formula and Using the SUM Function

Activity 1.08 Copying a Formula by Using the Fill Handle

Objective 4 Format Cells with Merge & Center and Cell Styles

Activity 1.09 Using Merge & Center and Applying Cell Styles

Activity 1.10 Formatting Financial Numbers

Objective 5 Chart Data to Create a Column Chart and Insert Sparklines

Activity 1.11 Charting Data in a Column Chart

Activity 1.12 Creating and Formatting Sparklines

Objective 6 Print, Display Formulas, and Close Excel

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

Activity 1.14 Deleting Unused Sheets in a Workbook

Activity 1.15 Printing a Worksheet

Activity 1.16 Displaying, Printing, and Hiding Formulas

PROJECT 1B Inventory Valuation

Objective 7 Check Spelling in a Worksheet

Activity 1.17 Checking Spelling in a Worksheet

Objective 8 Enter Data by Range

Activity 1.18 Entering Data by Range

Objective 9 Construct Formulas for Mathematical Operations

Activity 1.19 Using Arithmetic Operators

Activity 1.20 Copying Formulas Containing Absolute Cell References

Objective 10 Edit Values in a Worksheet

Activity 1.21 Editing Values in a Worksheet

Activity 1.22 Formatting Cells with the Percent Style

Objective 11 Format a Worksheet

Activity 1.23 Inserting and Deleting Rows and Columns

Activity 1.24 Adjusting Column Widths and Wrapping Text

 

Chapter 2 Using Functions, Creating Tables, and Managing Large Workbooks

PROJECT 2A Inventory Status Report

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

Activity 2.01 Using the SUM and AVERAGE Functions

Activity 2.02 Using the MEDIAN Function

Activity 2.03 Using the MIN and MAX Functions

Objective 2 Move Data, Resolve Error Messages, and Rotate Text

Activity 2.04 Moving Data and Resolving a # # # # # Error Message

Activity 2.05 Rotating Text

Objective 3 Use COUNTIF and IF Functions and Apply Conditional Formatting

Activity 2.06 Using the COUNTIF Function

Activity 2.07 Using the IF Function

Activity 2.08 Applying Conditional Formatting by Using Highlight

Cells Rules and Data Bars

Activity 2.09 Using Find and Replace

Objective 4 Use Date & Time Functions and Freeze Panes

Activity 2.10 Using the NOW Function to Display a System Date

Activity 2.11 Freezing and Unfreezing Panes

Objective 5 Create, Sort, and Filter an Excel Table

Activity 2.12 Creating an Excel Table

Activity 2.13 Sorting and Filtering an Excel Table

Activity 2.14 Converting a Table to a Range of Data

Objective 6 Format and Print a Large Worksheet

Activity 2.15 Printing Titles and Scaling to Fit

PROJECT 2B Weekly Sales Summary

Objective 7 Navigate a Workbook and Rename Worksheets

Activity 2.16 Navigating Among Worksheets, Renaming Worksheets, and Changing the Tab Color of Worksheets

Objective 8 Enter Dates, Clear Contents, and Clear Formats

Activity 2.17 Entering and Formatting Dates

Activity 2.18 Clearing Cell Contents and Formats

Objective 9 Copy and Paste by Using the Paste Options Gallery

Activity 2.19 Copying and Pasting by Using the Paste Options Gallery

Objective 10 Edit and Format Multiple Worksheets at the Same Time

Activity 2.20 Grouping Worksheets for Editing

Activity 2.21 Formatting and Constructing Formulas on Grouped Worksheets

Objective 11 Create a Summary Sheet with Column Sparklines

Activity 2.22 Constructing Formulas that Refer to Cells in Another Worksheet

Activity 2.23 Changing Values in a Detail Worksheet to Update a Summary Worksheet

Activity 2.24 Inserting Sparklines

Objective 12 Format and Print Multiple Worksheets in a Workbook

Activity 2.25 Moving and Formatting Worksheets in a Workbook

Activity 2.26 Printing All the Worksheets in a Workbook

 

Chapter 3 Analyzing Data with Pie Charts, Line Charts, and What-If

Analysis Tools

PROJECT 3A Budget Pie Chart Objective 1 Chart Data with a Pie Chart

Activity 3.01 Creating a Pie Chart and a Chart Sheet

Objective 2 Format a Pie Chart

Activity 3.02 Applying Percentages to Labels in a Pie Chart

Activity 3.03 Formatting a Pie Chart with 3-D

Activity 3.04 Rotating a Pie Chart

Activity 3.05 Exploding and Coloring a Pie Slice

Activity 3.06 Formatting the Chart Area

Activity 3.07 Inserting a Text Box in a Chart

Objective 3 Edit a Workbook and Update a Chart

Activity 3.08 Editing a Workbook and Updating a Chart

Activity 3.09 Inserting WordArt in a Worksheet

Objective 4 Use Goal Seek to Perform What-If Analysis

Activity 3.10 Using Goal Seek to Perform What-If Analysis

Activity 3.11 Preparing and Printing a Workbook with a Chart Sheet

PROJECT 3B Growth Projection with Line Chart

Objective 5 Design a Worksheet for What-If Analysis

Activity 3.12 Using Parentheses in a Formula to Calculate a Percentage Rate of Increase

Activity 3.13 Using Format Painter and Formatting as You Type

Activity 3.14 Calculating a Value After an Increase

Objective 6 Answer What-If Questions by Changing Values in a Worksheet

Activity 3.15 Answering What-If Questions and Using Paste Special

Objective 7 Chart Data with a Line Chart

Activity 3.16 Inserting Multiple Rows and Creating a Line Chart

Activity 3.17 Formatting Axes in a Line Chart

Activity 3.18 Formatting the Chart and Plot Areas

Activity 3.19 Preparing and Printing Your Worksheet

 

Business Running Case 1: Excel Chapters 1—3

 

Chapter 4 Use Financial and Lookup Functions, Define Names, and Validate Data

Project 4A Amortization Schedule

Objective 1 Use Financial Functions

Activity 4.01 Inserting the PMT Financial Function

Objective 2 Use Goal Seek

Activity 4.02 Using Goal Seek to Produce a Desired Result

Activity 4.03 Using Goal Seek to Find an Increased Period

Objective 3 Create a Data Table

Activity 4.04 Designing a Two-Variable Data Table

Activity 4.05 Using a Data Table to Calculate Options

Project 4B Quarterly Cost Report and Lookup Form

Objective 4 Define Names

Activity 4.06 Defining a Name

Activity 4.07 Inserting New Data into a Named Range

Activity 4.08 Changing A Defined Name

Activity 4.09 Creating a Defined Name by Using Row and Column Titles

Objective 5 Use Defined Names in a Formula

Activity 4.10 Using Defined Names in a Formula

Objective 6 Use Lookup Functions

Activity 4.11 Defining a Range of Cells for a Lookup Function

Activity 4.12 Inserting the VLOOKUP Function

Objective 7 Validate Data

Activity 4.13 Creating a Validation List

 

Chapter 5 Managing Large Workbooks and Using Advanced Sorting

and Filtering

Project 5A Large Worksheet for a Class Schedule

Objective 1 Navigate and Manage Large Worksheets

Activity 5.01 Using the Go To Special Command

Activity 5.02 Hiding Columns

Activity 5.03 Using the Go To Command

Activity 5.04 Arranging Multiple Workbooks and Splitting Worksheets

Objective 2 Enhance Worksheets with Themes and Styles

Activity 5.05 Changing and Customizing a Workbook Theme

Activity 5.06 Creating and Applying a Custom Table Style

Objective 3 Format a Worksheet to Share with Others

Activity 5.07 Previewing and Modifying Page Breaks

Activity 5.08 Repeating Column or Row Titles

Activity 5.09 Inserting a Hyperlink in a Worksheet

Activity 5.10 Modifying a Hyperlink

Objective 4 Save Excel Data in Other File Formats

Activity 5.11 Viewing and Saving a Workbook as a Web Page

Activity 5.12 Saving Excel Data in CSV File Format

Activity 5.13 Saving Excel Data as a PDF or XPS File

Project 5B Sorted, Filtered, and Outlined Database

Objective 5 Use Advanced Sort Techniques

Activity 5.14 Sorting on Multiple Columns

Activity 5.15 Sorting by Using a Custom List

Objective 6 Use Custom and Advanced Filters

Activity 5.16 Filtering by Format and Value Using AutoFilter

Activity 5.17 Filtering by Custom Criteria Using AutoFilter

Activity 5.18 Inserting the Sheet Name and Page Numbers in a Footer

Activity 5.19 Filtering by Using Advanced Criteria

Activity 5.20 Extracting Filtered Rows

Objective 7 Subtotal, Outline, and Group a List of Data

Activity 5.21 Subtotaling, Outlining, and Grouping a List of Data

 

Chapter 6 Creating Charts, Diagrams, and Templates

Project 6A Attendance Charts and Diagrams

Objective 1 Create and Format Sparklines and a 3-D Column Chart

Activity 6.01 Creating and Formatting Sparklines

Activity 6.02 Creating a 3-D Column Chart

Activity 6.03 Changing the Display of Chart Data

Activity 6.04 Editing and Formatting the Chart Title

Activity 6.05 Adding, Formatting, and Aligning Axis Titles

Activity 6.06 Editing Source Data

Activity 6.07 Formatting the Chart Floor and Chart Walls

Objective 2 Create and Format a Line Chart

Activity 6.08 Creating a Line Chart

Activity 6.09 Deleting a Legend and Changing a Chart Title

Activity 6.10 Changing the Values on the Value Axis

Activity 6.11 Formatting the Plot Area and the Data Series

Activity 6.12 Inserting a Trendline

Objective 3 Create and Modify a SmartArt Graphic

Activity 6.13 Creating a Process SmartArt Graphic

Activity 6.14 Modifying the Diagram Style

Objective 4 Create and Modify an Organization Chart

Activity 6.15 Creating and Modifying a SmartArt Organization Chart 3

Activity 6.16 Adding Effects to a SmartArt Graphic

Activity 6.17 Preparing Worksheets Containing Charts and Diagrams for Printing

Project 6B Order Form Template

Objective 5 Create an Excel Template

Activity 6.18 Entering Template Text

Activity 6.19 Formatting a Template

Activity 6.20 Entering Template Formulas

Activity 6.21 Inserting and Modifying an Image

Activity 6.22 Inserting and Modifying a WordArt Image

Activity 6.23 Saving a File as a Template

Objective 6 Protect a Worksheet

Activity 6.24 Protecting a Worksheet

Objective 7 Create a Worksheet Based on a Template

Activity 6.25 Creating a Worksheet Based on a Template

 

Business Running Case 2: Excel Chapters 4—6

 

Chapter 7 Creating PivotTable and PivotChart Reports and Auditing Worksheets

Project 7A PivotTable and PivotChart

Objective 1 Create a PivotTable Report

Activity 7.01 Creating a PivotTable Report

Activity 7.02 Adding Fields to a PivotTable Report

Objective 2 Use Slicers and Search Filters

Activity 7.03 Using a Slicer to Filter a PivotTable

Activity 7.04 Clearing Filters and Filtering by Using the Search Box

Objective 3 Modify a PivotTable Report

Activity 7.05 Rearranging a PivotTable Report

Activity 7.06 Displaying PivotTable Report Details in a New Worksheet

Activity 7.07 Displaying PivotTable Data on Separate Pages

Activity 7.08 Changing Calculations in a PivotTable Report

Activity 7.09 Formatting a PivotTable Report

Activity 7.10 Updating PivotTable Report Data

Objective 4 Create a PivotChart Report

Activity 7.11 Creating a PivotChart Report from a PivotTable Report

Activity 7.12 Modifying a PivotChart Report

Project 7B Revenue Report for Formula Auditing

Objective 5 Trace Precedents and Dependents to Audit Worksheet Formulas

Activity 7.13 Using the Trace Precedents Command

Activity 7.14 Using the Trace Dependents Command

Activity 7.14 Using the Trace Dependents Command

Activity 7.15 Using the Trace Error Command

Objective 6 Use Error Checking to Audit Worksheet Formulas

Activity 7.16 Using Error Checking

Activity 7.17 Circling Invalid Data

Objective 7 Use the Watch Window to Monitor Cell Values

Activity 7.18 Using the Watch Window to Monitor Changes

 

Chapter 8 Using the Data Analysis, Solver, and Scenario Features

Project 8A Sales Analysis

Objective 1 Calculate a Moving Average

Activity 8.01 Creating a Custom Number Format

Activity 8.02 Calculating a Moving Average

Activity 8.03 Modifying the Moving Average Chart

Activity 8.04 Calculating Growth Based on a Moving Average

Objective 2 Project Income and Expenses

Activity 8.05 Projecting Income and Expenses

Objective 3 Determine a Break-Even Point

Activity 8.06 Charting the Break-Even Point With a Line Chart

Project 8B Staffing Analysis

Objective 4 Use Solver

Activity 8.07 Installing Solver

Activity 8.08 Understanding a Solver Worksheet

Activity 8.09 Using Solver

Objective 5 Evaluate Complex Formulas

Activity 8.10 Evaluating Complex Formulas

Objective 6 Create Scenarios

Activity 8.11 Creating a Scenario Using the Scenario Manager

Activity 8.12 Creating a Scenario Using Solver

Activity 8.13 Creating a Scenario Summary

 

Chapter 9 Using Macros and Visual Basic for Applications

Project 9A Travel Expenses

Objective 1 Record a Macro

Activity 9.01 Adding the Developer Tab to the Ribbon

Activity 9.02 Changing the Macro Security Settings in Excel

Activity 9.03 Unprotecting a Workbook

Activity 9.04 Recording a Macro

Objective 2 Assign a Macro to a Button on the Quick Access Toolbar

Activity 9.05 Adding a Button to the Quick Access Toolbar

Activity 9.06 Testing the Macro Button

Objective 3 Modify a Macro

Activity 9.07 Changing the Visual Basic Code

Project 9B VBA Procedure

Objective 4 Write a VBA Procedure to Use an ActiveX Control

Activity 9.08 Inserting ActiveX Controls

Activity 9.09 Changing the Properties of an ActiveX Control

Activity 9.10 Writing a VBA Procedure for a Command Button

Activity 9.11 Modifying a VBA Procedure

Activity 9.12 Testing the VBA Procedure and the ActiveX Control

Objective 5 Restore Initial Settings

Activity 9.13 Removing the Quick Access Toolbar Button and the Macro

 

Business Running Case 3: Excel Chapters 7—9

 

Chapter 10 External Data, Database Functions, and Side-by-Side

Tables

Project 10A Medical Center Information

Objective 1 Get External Data into Excel

Activity 10.01 Importing Information into Excel from an Access Database

Activity 10.02 Importing Information into Excel from a Web Page

Activity 10.03 Importing Information into Excel from a Text File

Activity 10.04 Importing Information into Excel from an XML File

Objective 2 Create a Query and Use the Query Wizard to Sort and Filter

Activity 10.05 Creating a Query and Filtering and Sorting Data by Using the Query Wizard

Activity 10.06 Converting a Range and Formatting Imported Data

Objective 3 Use DAVERAGE and DSUM Database Functions

Activity 10.07 Using the DAVERAGE Database Function

Activity 10.08 Using the DSUM Database Function

Objective 4 Use DCOUNT and DGET Database Functions

Activity 10.09 Using DCOUNT

Activity 10.10 Using DGET

Project 10B Office Equipment Inventory...................................................

Objective 5 Insert a Second Table into a Worksheet

Activity 10.11 Inserting a Second Table into a Worksheet

Activity 10.12 Sorting Side-by-Side Tables

Objective 6 Apply Conditional Formatting to Side-by-Side Tables

Activity 10.13 Applying Icon Sets to Side-by-Side Tables

Objective 7 Insert a Screenshot

Activity 10.14 Inserting a Screen Shot

Objective 8 Create Custom Headers and Footers

Activity 10.15 Creating Custom Headers and Footers

 

Chapter 11 Collaborating with Others and Preparing a Workbook for

Distribution

Project 11A Summer Schedule

Objective 1 Create a Shared Workbook

Activity 11.01 Locating and Modifying Workbook Properties

Activity 11.02 Activating Track Changes to Create a Shared Workbook

Objective 2 Track Changes Made to a Workbook

Activity 11.03 Making a Copy of a Shared Workbook

Activity 11.04 Making Changes to a Shared Workbook

Activity 11.05 Making Changes to a Copy of the Shared Workbook

Objective 3 Merge Workbooks and Accept Changes

Activity 11.06 Merging Revisions

Activity 11.07 Accepting or Rejecting Tracked Changes

Activity 11.08 Removing the Shared Designation, Resetting the User Name, and Removing a Command from the Quick Access Toolbar

Activity 11.09 Adding a Signature Line

Project 11B Distributed Workbook

Objective 4 Prepare a Final Workbook for Distribution

Activity 11.10 Ensuring Backward-Compatibility in a Workbook

Activity 11.11 Inspecting a Document

Activity 11.12 Encrypting a Workbook

Activity 11.13 Marking a Workbook as Final

Objective 5 Upload a Workbook to SkyDrive

Activity 11.14 Uploading a Workbook to SkyDrive

 

Business Running Case 4: Excel Chapters 10—11

 

Glossary

 

Index