GO! with Microsoft Excel 2010, Comprehensive
Published on 8. October 2010
Book
Mixed media product
816 pages
978-0-13-509821-9 (ISBN)
Description
For introductory computer courses on Microsoft Excel 2010 or courses in computer concepts with a lab component on Excel.
Teach the course YOU want in LESS TIME!
The primary goal of the GO! Series, aside from teaching computer applications, is ease of implementation. This approach is based on clearly defined projects for students and a one-of-a-kind supplements package for instructors.
Teach the course YOU want in LESS TIME!
The primary goal of the GO! Series, aside from teaching computer applications, is ease of implementation. This approach is based on clearly defined projects for students and a one-of-a-kind supplements package for instructors.
More details
Language
English
Place of publication
United States
Publishing group
Pearson Education (US)
Target group
College/higher education
Dimensions
Height: 275 mm
Width: 235 mm
Thickness: 25 mm
Weight
1560 gr
ISBN-13
978-0-13-509821-9 (9780135098219)
Copyright in bibliographic data and cover images is held by Nielsen Book Services Limited or by the publishers or by their respective licensors: all rights reserved.
Schweitzer Classification
Persons
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.
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.
Content
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
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