Chapter 1 Creating a Worksheet and Charting Data
Project 1A Seattle Payroll
Objective 1 Start Excel and Navigate a Worksheet
Activity 1.1 Starting Excel and Naming and Saving a Workbook
Activity 1.2 Navigating a Worksheet
Objective 2 Select Parts of a Worksheet
Activity 1.3 Selecting Cells and Ranges and Entering Data 1
Objective 3 Enter Data, Construct a Formula, and Use the SUM Function
Activity 1.4 Opening an Existing Workbook, Entering Text, and Using
AutoComplete
Activity 1.5 Entering Numbers and Adjusting Column Width
Activity 1.6 Entering a Formula and Using the Sum Function
Objective 4 Format Data, Cells, and Worksheets
Activity 1.7 Using Font Styles and Centering Text
Activity 1.8 Using Merge and Center
Objective 5 Insert a Footer into a Worksheet
Activity 1.9 Adding a Footer to a Worksheet
Objective 6 Delete Unused Worksheets and Preview and Print
a Worksheet
Activity 1.10 Deleting Unused Worksheets
Activity 1.11 Previewing and Printing a Worksheet
Objective 7 Print Formulas, Close a Workbook, and Exit Excel
Activity 1.12 Displaying and Printing Formulas, Closing a Workbook, and Exiting Excel
Project 1B Annual Income
Objective 8 Check Spelling and Edit a Worksheet
Activity 1.13 Opening and Saving an Existing Workbook
Activity 1.14 Using Wrap Text and the Undo Command
Activity 1.15 Checking for Spelling Errors in a Worksheet
Activity 1.16 Editing Text in a Worksheet
Objective 9 Enter Data by Range
Activity 1.17 Selecting Cells and Entering Data by Range
Objective 10 Create and Copy Formulas
Activity 1.18 Using the Point-and-Click Method and Calculation Operators
to Create a Formula
Activity 1.19 Using the Fill Handle to Copy a Formula
Activity 1.20 Determining Percentages
Objective 11 Use Format Painter and Chart Data
Activity 1.21 Using Format Painter
Activity 1.22 Charting Data
Activity 1.23 Printing the Worksheet and its Formulas
Activity 1.24 Using Help
Chapter 2 Using Multiple-Sheet Workbooks
Project 2A Income From Lodging
Objective 1 Use a Multiple-Sheet Workbook
Activity 2.1 Inserting a Worksheet and Entering and Formatting Data in a Multiple Sheet Workbook
Activity 2.2 Changing the Format of Worksheet Tabs and Using the Tab
Scrolling Buttons
Objective 2 Enter a Series
Activity 2.3 Entering a Series
Objective 3 Copy and Paste Cell Contents
Activity 2.4 Using Copy and Paste
Activity 2.5 Copying Data Between Worksheets
Objective 4 Use the Office Clipboard
Activity 2.6 Using the Office Clipboard to Collect and Paste Data Between
Workbooks
Objective 5 Total the Worksheet Range and Enter a Grand Total
Activity 2.7 Totaling the Worksheet in One Step and Correcting Formula Errors
Activity 2.8 Creating a Grand Total in One Step
Objective 6 Format a Multiple-Sheet Workbook Group
Activity 2.9 Formatting a Worksheet Group and Using Print Preview
Objective 7 Insert Columns and Rows in Multiple Worksheets
Activity 2.10 Inserting Columns in a Worksheet
Activity 2.11 Inserting Rows That Adjust Relative References and Reviewing
Error Messages
Project 2B Hotel Taxes
Objective 8 Copy a Worksheet
Activity 2.12 Copying a Worksheet within a Workbook
Objective 9 Create Formulas with Absolute Cell References and Copy Formats
Activity 2.13 Creating and Copying a Formula That Uses an Absolute Cell
Reference
Objective 10 Find and Replace Text and Hide and Unhide Columns
Activity 2.14 Using Find and Replace
Activity 2.15 Hiding and Unhiding Columns
Objective 11 Conduct a What-If Analysis and Use Statistical Functions
Activity 2.16 Performing a What-If Analysis and Using Statistical Functions
Activity 2.17 Planning and Preparing a Worksheet
Objective 12 Create Accurate Worksheets with Accuracy Tools
Activity 2.18 Inserting a Worksheet into a Workbook
Activity 2.19 Using Undo and Redo
Activity 2.20 Identifying and Correcting Errors in Formulas
Activity 2.21 Formatting Page Placement and Adding Footers
for Multiple Worksheets
Chapter 3 Working with IF Functions and Large Worksheets
Project 3A Payroll
Objective 1 Construct an IF Function
Activity 3.1 Preparing Regular Hours Worked in a Payroll Report Using an
IF Statement
Activity 3.2 Reporting Overtime Hours Worked with an IF Formula
Activity 3.3 Determining Payroll Deductions Using Absolute References
in Formulas
Activity 3.4 Formatting the Worksheet and Merging and Centering a Vertical
Range of Cells
Objective 2 Link Data in Workbooks
Activity 3.5 Constructing Formulas That Refer to Cells in Another Worksheet
Objective 3 Create IF Functions That Return Text
Activity 3.6 Copying Cells and Creating an IF Formula to Enter Text Using
Drag-and-Drop
Objective 4Emphasize Data Using Conditional Formatting
Activity 3.7 Highlighting Results with a Conditional Format
Activity 3.8 Correcting an Error and Reviewing Results
Objective 5 Format with Themes
Activity 3.9 Formatting a Worksheet Using a Theme
Activity 3.10 Changing a Theme and Font
Objective 6Add Information in the Header and Footer
Activity 3.11 Editing the Footer and Header
Project 3B Enrollments
Objective 7 Enter Dates
Activity 3.12 Entering a Date as Text
Objective 8 Format Large Worksheets
Activity 3.13 Indenting Cell Contents and Using Zoom Control
Activity 3.14 Adding Vertical Borders
Activity 3.15 Freezing Panes
Objective 9 Apply Number Formats
Activity 3.16 Formatting Numbers
Activity 3.17 Using a Theme on Large Worksheets
Objective 10 Control Print Options
Activity 3.18 Printing Gridlines and Headings
Activity 3.19 Previewing and Modifying Page Breaks
Activity 3.20 Setting Margins
Activity 3.21 Inserting Page Numbers in a Header
Activity 3.22 Setting the Print Area
Chapter 4 Creating charts and Tables, Sorting and Filtering Data
Project 4A Census
Objective 1 Use Text Orientation
Activity 4.1 Rotating Text in Cells
Objective 2 Create a Column Chart
Activity 4.2 Creating a 3-D Column Chart
Objective 3 Create a Chart Sheet and Edit the Chart
Activity 4.3 Creating a Chart Sheet
Activity 4.4 Changing Chart Type and Style
Activity 4.5 Editing and Formatting a Chart Title
Activity 4.6 Editing and Formatting a Chart Legend
Activity 4.7 Adding Axis Titles and Editing Worksheet Data
Objective 4 Create and Modify a Pie Chart
Activity 4.8 Creating a Pie Chart
Activity 4.10 Rotating a Pie Chart
Activity 4.11 Inserting a Text Box in a Chart
Objective 5 Apply a Theme to a Chart
Activity 4.12 Changing the Theme in a Chart
Objective 6 Print Charts
Activity 4.13 Inserting Headers and Footers for Chart Sheets
Activity 4.14 Printing a Chart
Project 4B Employees
Objective 7 Sort Data
Activity 4.15 Sorting Alphabetical Data
Activity 4.16 Sorting by Numbers
Activity 4.17 Sorting by Date
Objective 8 Convert Text into Columns
Activity 4.18 Splitting Cell Contents into Multiple Cells and Copy and Paste
Activity 4.19 Using the TRIM Function
Objective 9 Apply Conditional Formatting Using Data Bars
and Color Scales
Activity 4.20 Highlighting Cell Rules
Activity 4.21 Using Top/Bottom Rules
Activity 4.22 Formatting and Editing Data Bars
Activity 4.23 Formatting and Editing Color Scales
Objective 10 Insert a Table and Filter Data
Activity 4.24 Filtering a Table
Activity 4.25 Filtering and Sorting on Two Tables
Activity 4.26 Filtering by Using Text and Number Filters
Activity 4.27 Chart Filtered Data
Chapter 5 Making Decisions with Functions
Project 5A Building Permits
Objective 1 Create Text Functions
Activity 5.1 Using the PROPER Function
Objective 2 Create Statistical Functions
Activity 5.2 Using the MEDIAN and MODE Functions and Formula AutoComplete 462
Activity 5.3 Counting Cells
Objective 3 Insert Date and Time Functions
Activity 5.4 Using Date and Time Functions
Activity 5.5 Using Dates in a Formula
Objective 4 Create Logical Functions and Insert a Comment
Activity 5.6 Using the AND Function
Activity 5.7 Using the OR Function
Activity 5.8 Inserting a Comment
Activity 5.9 Printing Comments
Project 5B City Financial
Objective 5 Insert Financial Functions
Activity 5.10 Creating an Amortization Table
Activity 5.11 Using the PMT (Payment) Function
Activity 5.12 Using the IPMT (Interest Payment) Function
Activity 5.13 Using the PPMT (Principal Payment) Function and Determining
the Ending Balance
Activity 5.14 Complete the Amortization Schedule
Objective 6 Create What-If Analysis with Goal Seek
Activity 5.15 Using Goal Seek to Determine Length of Loan
Objective 7 Determine Future Value and Present Value
Activity 5.16 Determining Present Value
Activity 5.17 Determining Future Value
Chapter 6 Using Named Ranges, Templates, Lookup Values and 3-D References
Project 6A Third Quarter
Objective 1 Create Formulas Using Named Ranges
Activity 6.1 Defining and Using a Named Cell
Activity 6.2 Creating Names from Row and Column Titles
Activity 6.3 Creating Formulas Using Named Ranges
Activity 6.4 Inserting Rows Within a Range and Editing the Worksheet
Activity 6.5 Using Named Cells to Create Quarterly Totals
Objective 2 Utilize Lookup Lists
Activity 6.7 Creating a Lookup List
Activity 6.8 Looking Up Text in a Worksheet Using VLOOKUP
Activity 6.9 Editing the Lookup List
Activity 6.10 Determining Sales Amounts Using the COUNTIF Function
Objective 3 Customize and Use Microsoft-Created Templates
Activity 6.11 Downloading and Personalizing a Template
Activity 6.12 Completing a Worksheet Using a Template
Project 6B Quarter 2 Sales
Objective 4 Transpose Data in a Worksheet and Apply Cell Styles
Activity 6.13 Transposing Data in a Worksheet
Activity 6.14 Applying Cell Styles to a Worksheet
Activity 6.15 Linking Worksheet Data Using a 3-D Reference
Activity 6.16 Linking Workbook Data with a 3-D Reference
Activity 6.17 Saving a Workspace and Editing Linked Data
Objective 6 Create Hyperlinks
Activity 6.18 Inserting a Hyperlink in a Worksheet
Activity 6.19 Inserting a Hyperlink in Workbooks
Chapter 7 Importing Data, Expanding a Table, and utilizing Database Features
Project 7A Closed Cases
Objective 1 Create and Expand a Table and Insert a Calculated Column
Activity 7.1 Creating and Expanding a Table
Activity 7.2 Naming a Table
Activity 7.3 Inserting a Calculated Column into the Table
Objective 2 Create and Sort a Custom List
Activity 7.4 Creating a Custom List
Objective 3 Filter by Using Advanced Criteria
Activity 7.5 Filtering on Two Number Criteria
Activity 7.6 Filtering on a Calculated Field
Objective 4 Evaluate Data with Database Functions
Activity 7.7 Determining the Average using the DAVERAGE Database Function
Activity 7.8 Determining the Sum of Certain Contingencies Using the DSUM Function
Activity 7.9 Counting Specific Records in a Database
Project 7B Clients
Objective 5 Import Data to Excel
Activity 7.10 Importing an Access Database into Excel
Activity 7.11 Importing a Word Document to Excel
Activity 7.12 Copying an Excel Worksheet and Editing the Worksheet
Objective 6 Create Lookup Tables in Another Workbook
Activity 7.14 Using an External Lookup
Objective 7 Enter Subtotals and Outline the Worksheet
Activity 7.15 Subtotaling the Worksheet
Objective 8 Link and Embed a Worksheet and Chart into Word Documents
Activity 7.16 Embedding a Worksheet into a Word Memo
Activity 7.17 Linking a Worksheet and a Chart into a Word Memo
Activity 7.18 Editing Linked and Embedded Data
Chapter 8 Creating Macros, Using Depreciation and Conditional Functions, and Creating PivotTables ad PivotChart Reports
Project 8A Depreciation
Objective 1 Create and Run a Macro
Activity 8.1 Accessing the Developer Tab
Activity 8.2 Creating a Macro
Activity 8.3 Running the Macro
Activity 8.5 Opening a Document that Contains a Macro
Activity 8.6 Editing a Macro with VBA Code
Objective 2 Apply Depreciation Functions
Activity 8.8 Depreciating an Asset Using the Sum-of-Years' Digits Method
Activity 8.9 Depreciating an Asset Using the Declining Balance Method
Activity 8.10 Preparing a Schedule of Depreciation
Activity 8.11 Create a Macro Using the Relative Reference Feature
Project 8B New Car Sales
Objective 3 Evaluate Worksheet Data with Conditional IF Functions
Activity 8.12 Determining Individual Cars Sold Using the COUNTIF Function
Activity 8.13 Determining Individual Sales Using the SUMIF Function
Activity 8.14 Determining Individual Sales Using the AVERAGEIF Function
Activity 8.15 Counting the Number of Hybrids Sold Using the COUNTIFS Function
Activity 8.16 Determining Sales of a Car Make Using the SUMIFS Function
Objective 4 Create a PivotTable and PivotChart
Activity 8.17 Creating a PivotTable
Activity 8.18 Filtering Data
Activity 8.19 Rearranging the Filtered Data to Display Sales Information
Activity 8.20 Removing Filters to Display More Data
Activity 8.21 Refreshing the Pivot Table and Displaying the Table
on Separate Pages
Activity 8.22 Establishing a Custom Calculation and Formatting
the PivotTable
Activity 8.23 Creating a Pivot Chart
Chapter 9 Inserting Graphic Elements into Worksheets and Charts
Project 9A Quarter1 Sales
Objective 1 Format with Graphic Images Including WordArt to Enhance Worksheets
Activity 9.1 Formatting with Patterns
Activity 9.2 Inserting and Formatting a Shape
Activity 9.3 Formatting Text Within a Shape
Activity 9.4 Inserting and Formatting WordArt
Objective 2 Insert SmartArt Graphics
Activity 9.5 Inserting a SmartArt Organization Chart
Activity 9.6 Inserting a Picture as Background
Activity 9.7 Formatting SmartArt
Objective 3 Create, Edit, and Publish a Web Page
Activity 9.8 Preparing a Web Page
Activity 9.9 Inserting and Formatting Graphic Elements into a Web Page
Activity 9.10 Insert Hyperlinks into a Web Page
Activity 9.11 Saving and Publishing a Web Page
Project 9B SoAmerica
Objective 4 Create Specialized Charts
Activity 9.12 Inserting a Line Chart
Activity 9.13 Copy a Chart and Change Chart Type to Area Chart
Activity 9.14 Format Data Series and Chart Area
Activity 9.15 Rearrange Data Markers 7
Activity 9.16 Inserting a Scatter Chart
Activity 9.17 Inserting a Trend line
Activity 9.18 Inserting Axis Titles 7
Activity 9.19 Editing the Axis of a Chart
Activity 9.20 Creating a Stock Chart 7
Objective 5 Format Column Charts with Pictures
and Other Graphic Elements
Activity 9.21 Using Pictures to Format a 2-D Column Chart
Activity 9.22 Using Pictures to Format a 3-D Column Chart 7
Chapter 10 Creating Templates and Creating and Validating Form
10A Project 10A Payroll Report
Objective 1 Protect Worksheet Elements
Activity 10.1 Inserting a Picture into a Header
Activity 10.2 Inserting Formulas and Formats into a Report
Activity 10.3 Completing a Chart for the Worksheet
Activity 10.4 Protecting Elements of the Worksheet
Objective 2 Unprotect Elements and Hide Formulas
Activity 10.5 Unprotecting Cells in a Protected Worksheet
Activity 10.6 Hiding Formulas and Other Sensitive Information
Objective 3 Protect a Workbook
Activity 10.7 Protecting a Workbook's Structure and Windows
Activity 10.8 Open a Protected Workbook
Objective 4 Save Worksheet and Chart Templates
Activity 10.9 Saving a Workbook as a Template
Activity 10.10 Creating a Chart to Save as a Template
Objective 5 Complete a Report with Worksheet and Chart Templates
Activity 10.11 Creating a Worksheet and Charts Using Templates
10B Project 10B Time Card
Objective 6 Create a Form
Activity 10.12 Inserting a VLOOKUP to Enter Data
Activity 10.13 Inserting Formulas to Display Dates in a Form and Formatting the Input Area
Activity 10.14 Creating the Output Area
Activity 10.15 Completing the Formulas in the Output Area
Objective 7 Validate a Form
Activity 10.16 Validating the Form
Activity 10.17 Testing the Validation Rule
Activity 10.18 Creating a Drop-Down List
Activity 10.19 Hiding Formulas
Objective 8 Insert Macro Command Buttons
Activity 10.20 Creating a Print Macro
Activity 10.21 Creating a Macro to Clear the Input Area
Activity 10.22 Inserting a Macro Command Button
Activity 10.23 Protecting the Worksheet and Saving It as a Template
Activity 10.24 Preparing Payroll Reports for a Week
Chapter 11 Nesting Functions and Consolidating Worksheets
11A Project 11A Server Bonus
Objective 1 Nest One Function within Another
Activity 11.1 Creating a Nested Function
Activity 11.2 Nesting IF Functions
Activity 11.3 Nesting IF Functions to Determine the Incentive for Sales for one Month
Activity 11.4 Nesting IF and AND Functions to Determine The Weekend Recipients
Objective 2 Use 3-D References and Nested Lookup
Activity 11.5 Using Nested 3-D Reference
Activity 11.6 Nesting a Lookup Function
Objective 3 Check Accuracy with Excel's Auditing Tools
Activity 11.7 Evaluating Formula
Activity 11.8 Using Auditing Tools to Trace Precedents and Dependents
Activity 11.9 Using Error Checking
Activity 11.10 Using Watch Window
Project 11B Sales Report
Objective 4 Consolidate Workbooks
Activity 11.11 Consolidating by Position
Activity 11.12 Consolidating by Category
Objective 5 Share and Merge Workbooks
Activity 11.13 Using a Shared Workbook
Activity 11.14 Creating a Shared Workbook
Activity 11.15 Entering Data into a Shared Workbook
Activity 11.16 Display the Compare and Merge Button
Activity 11.17 Merge Worksheets
Chapter 12 Working with Data Tables, Scenarios, Solver, XML, and the Document Inspector
12A Project 12A Analysis Tools
Objective 1 Create a Data Table
Activity 12.1 Creating a One-variable Data Table
Activity 12.2 Adding a Formula to One-Variable Data Table
Activity 12.3 Creating a Two-Variable Data Table
Activity 12.3 Creating a Two-Variable Data Table
Objective 2 Create a Scenario
Activity 12.4 Creating and Displaying Scenario
Activity 12.5 Creating a Scenario Summary Report
Objective 3 Use Solver
Activity 12.6 Loading Solver
Activity 12.7 Using Solver
12B Project 12B Price List
Objective 4 Prepare a Document for Distribution
Activity 12.8 Changing the User Information
Activity 12.9 Examine a File for Sensitive Information
Activity 12.10 Using Document Properties
Activity 12.11 Restricting Permissions Using Encryption and Information Rights Management
Activity 12.12 Saving as PDF or XPS Formats
Activity 12.13 Checking for Features Not Supported in Earlier Versions of Excel
Activity 12.14 Using the Document Inspector
Activity 12.15 Marking the Workbook as Final
Objective 5 Use XML to Enhance Security
Activity 12.16 Renaming an Excel 2007 file and Displaying its Structure
Activity 12.17 Identifying the Functions of Folders and Files
Activity 12.18 Replacing a Picture in the Media Folder
Activity 12.19 Identifying the Characteristics of an XML File
Activity 12.20 Editing an XML File
Activity 12.21 Checking XML Files for Valid Structure
Activity 12.22 Replacing Parts and Zipping the Container File
Activity 12.23 Renaming the File and Restoring Default Values
