OFFICE FUNDAMENTALS AND FILE MANAGEMENT
CHAPTER ONE ? Office Fundamentals and File Management
CASE STUDY: RAILS AND TRAILS
OBJECTIVES
FILES AND FOLDERS
Using Windows Explorer
Working with Folders and Files
Selecting, Copying, and Moving Multiple Files and Folders
HANDS-ON EXERCISE 1: Files and Folders
MICROSOFT OFFICE SOFTWARE
Identifying Common Interface Components
Getting Office Help
HANDS-ON EXERCISE 2: Microsoft Office Software
BACKSTAGE VIEW TASKS
Opening a File
Printing a File
Closing a File and Application
HANDS-ON EXERCISE 3: Backstage View Tasks
HOME TAB TASKS
Selecting and Editing Text
Using the Clipboard Group Tasks
Using the Editing Group Tasks
HANDS-ON EXERCISE 4: Home Tab Tasks
INSERT TAB TASKS
Inserting Objects
REVIEW TAB TASKS
Reviewing a File
PAGE LAYOUT TAB TASKS
Changing Page Settings
HANDS-ON EXERCISE 5: Tasks on the Insert Tab, Page Layout Tab, and Review Tab
CHAPTER OBJECTIVES REVIEW
KEY TERMS
MULTIPLE CHOICE
PRACTICE EXERCISES
MID-LEVEL EXERCISES
CAPSTONE EXERCISE
BEYOND THE CLASSROOM
MICROSOFT OFFICE EXCEL 2010
CHAPTER ONE ? Introduction to Excel
CASE STUDY: OK OFFICE SYSTEMS
OBJECTIVES
INTRODUCTION TO SPREADSHEETS
Planning for Effective Workbook and Worksheet Design
Exploring the Excel Window
Entering and Editing Cell Data
HANDS-ON EXERCISE 1: Introduction to Spreadsheets
MATHEMATICS AND FORMULAS
Using Symbols and the Order of Precedence
Using Auto Fill
Displaying Cell Formulas
HANDS-ON EXERCISE 2: Mathematics and Formulas
WORKBOOK AND WORKSHEET MANAGEMENT
Managing Worksheets
Managing Columns and Rows
Selecting, Moving, Copying, and Pasting
HANDS-ON EXERCISE 3: Workbook and Worksheet Management
FORMATTING
Applying Alignment and Font Options
Applying Number Formats
HANDS-ON EXERCISE 4: Formatting
PAGE SETUP AND PRINTING
Selecting Page Setup Options
Printing a Worksheet
HANDS-ON EXERCISE 5: Page Setup and Printing
CHAPTER OBJECTIVES REVIEW
KEY TERMS
MULTIPLE CHOICE
PRACTICE EXERCISES
MID-LEVEL EXERCISES
CAPSTONE EXERCISE
BEYOND THE CLASSROOM
CHAPTER TWO ? Formulas and Functions
CASE STUDY: DENVER MORTGAGE COMPANY
OBJECTIVES
FORMULA BASICS
Using Semi-Selection to Create a Formula
Using Relative, Absolute, and Mixed Cell References in Formulas
Avoiding Circular References
HANDS-ON EXERCISE 1: Formula Basics
FUNCTION BASICS
Inserting a Function
Totaling Values with the SUM Function
Inserting Basic Statistical Functions
Using Date Functions
HANDS-ON EXERCISE 2: Function Basics
LOGICAL, LOOKUP, AND FINANCIAL FUNCTIONS
Determining Results with the IF Function
Using Lookup Functions
Calculating Payments with the PMT Function
HANDS-ON EXERCISE 3: Logical, Lookup, and Financial Functions
RANGE NAMES
Creating and Maintaining Range Names
Using Range Names in Formulas
HANDS-ON EXERCISE 4: Range Names
CHAPTER OBJECTIVES REVIEW
KEY TERMS
MULTIPLE CHOICE
PRACTICE EXERCISES
MID-LEVEL EXERCISES
CAPSTONE EXERCISE
BEYOND THE CLASSROOM
CHAPTER THREE ? Charts
CASE STUDY: HORT UNIVERSITY MAJORS
OBJECTIVES
CHART BASICS
Deciding Which Chart Type to Create
Creating a Chart
HANDS-ON EXERCISE 1: Chart Basics
CHART DESIGN
Changing the Chart Type
Changing the Data Source and Structure
Applying a Chart Layout and a Chart Style
Moving a Chart
Printing Charts
Inserting and Customizing a Sparkline
HANDS-ON EXERCISE 2: Chart Design
CHART LAYOUT
Selecting and Formatting Chart Elements
Customizing Chart Labels
Formatting the Axes and Gridlines
Adding a Trendline
HANDS-ON EXERCISE 3: Chart Layout
CHAPTER OBJECTIVES REVIEW
KEY TERMS
MULTIPLE CHOICE
PRACTICE EXERCISES
MID-LEVEL EXERCISES
CAPSTONE EXERCISE
BEYOND THE CLASSROOM
CHAPTER FOUR ? Datasets and Tables
CASE STUDY: THE SPA EXPERTS
OBJECTIVES
LARGE DATASETS
Freezing Rows and Columns
Printing Large Datasets
HANDS-ON EXERCISE 1: Large Datasets
EXCEL TABLES
Understanding Table Design
Creating a Table
Applying a Table Style
HANDS-ON EXERCISE 2: Excel Tables
TABLE MANIPULATION AND AGGREGATION
Sorting Data
Filtering Data
Using Structured References and a Total Row
HANDS-ON EXERCISE 3: Table Manipulation and Aggregation
CONDITIONAL FORMATTING
Applying Conditional Formatting
Creating a New Rule
Sorting and Filtering Using Conditional Formatting
HANDS-ON EXERCISE 3: Conditional Formatting
CHAPTER OBJECTIVES REVIEW
KEY TERMS
MULTIPLE CHOICE
PRACTICE EXERCISES
MID-LEVEL EXERCISES
CAPSTONE EXERCISE
BEYOND THE CLASSROOM
CHAPTER FIVE ? Subtotals, PivotTables, and PivotCharts
CASE STUDY: IVORY HALLS PUBLISHING COMPANY
OBJECTIVES
OUTLINES AND SUBTOTALS
Grouping and Ungrouping Data
Subtotaling Data
HANDS-ON EXERCISE 1: Outlines and Subtotals
PIVOTTABLE BASICS
Creating a PivotTable
Changing the Values Field
Modifying a PivotTable
HANDS-ON EXERCISE 2: PivotTable Basics
PIVOTTABLE OPTIONS
Sorting, Filtering, and Slicing a PivotTable
Creating a Calculated Field
HANDS-ON EXERCISE 3: PivotTable Options
PIVOTTABLE DESIGN AND PIVOTCHARTS
Formatting a PivotTable
Creating a PivotChart
HANDS-ON EXERCISE 4: PivotTable Design and PivotCharts
CHAPTER OBJECTIVES REVIEW
KEY TERMS
MULTIPLE CHOICE
PRACTICE EXERCISES
MID-LEVEL EXERCISES
CAPSTONE EXERCISE
BEYOND THE CLASSROOM
CHAPTER SIX ? What-If Analysis
CASE STUDY: PERSONAL FINANCE: BUYING A CAR
OBJECTIVES
ONE- AND TWO-VARIABLE DATA TABLES
Creating a One-Variable Data Table
Creating a Two-Variable Data Table
HANDS-ON EXERCISE 1: One- and Two-Variable Data Tables
GOAL SEEK AND SCENARIO MANAGER
Identifying an Input Value with Goal Seek
Using Scenario Manager
Generating Scenario Summary Reports
HANDS-ON EXERCISE 2: Goal Seek and Scenario Manager
SOLVER
Loading the Solver Add-In
Optimizing Results with Solver
HANDS-ON EXERCISE 3: Solver
CHAPTER OBJECTIVES REVIEW
KEY TERMS
MULTIPLE CHOICE
PRACTICE EXERCISES
MID-LEVEL EXERCISES
CAPSTONE EXERCISE
BEYOND THE CLASSROOM
CHAPTER SEVEN ? Specialized Functions
CASE STUDY: STANWORTH, INC.
OBJECTIVES
MATH AND STATISTICAL FUNCTIONS
Using Conditional Math and Statistical Functions
Calculating Relative Standing with Statistical Functions
HANDS-ON EXERCISE 1: Math and Statistical Functions
LOGICAL AND LOOKUP FUNCTIONS
Creating a Nested Logical Function
Using MATCH and INDEX Lookup Functions
HANDS-ON EXERCISE 2: Logical and Lookup Functions
DATABASE FILTERING AND FUNCTIONS
Using Advanced Filtering
Manipulating Data with Database Functions
HANDS-ON EXERCISE 3: Database Filtering and Functions
FINANCIAL FUNCTIONS
Creating a Loan Amortization Table
Performing Other Financial Calculations
HANDS-ON EXERCISE 4: Financial Functions
CHAPTER OBJECTIVES REVIEW
KEY TERMS
MULTIPLE CHOICE
PRACTICE EXERCISES
MID-LEVEL EXERCISES
CAPSTONE EXERCISE
BEYOND THE CLASSROOM
CHAPTER EIGHT ? Multiple-Sheet Workbook Management
CASE STUDY: HEARTLAND DEPARTMENT STORE
OBJECTIVES
MULTIPLE WORKSHEETS
Working with Grouped Worksheets
Managing Windows and Workspaces
Inserting Hyperlinks
HANDS-ON EXERCISE 1: Multiple Worksheets
3-D FORMULAS AND LINKED WORKBOOKS
Inserting a 3-D Formula
Linking Workbooks
HANDS-ON EXERCISE 2: 3-D Formulas and Linked Workbooks
FORMULA AUDITS AND DATA VALIDATION
Auditing Formulas
Setting Up a Watch Window
Validating Data
HANDS-ON EXERCISE 3: Formula Audits and Data Validation
CHAPTER OBJECTIVES REVIEW
KEY TERMS
MULTIPLE CHOICE
PRACTICE EXERCISES
MID-LEVEL EXERCISES
CAPSTONE EXERCISE
BEYOND THE CLASSROOM
CHAPTER NINE ? Imports, Web Queries, and XML
CASE STUDY: STOCK ANALYSIS
OBJECTIVES
EXTERNAL DATA
Importing Data from External Sources
Creating a Web Query
Managing Connections
HANDS-ON EXERCISE 1: External Data
TEXT MANIPULATION
Converting Text to Columns
Manipulating Text with Functions
HANDS-ON EXERCISE 2: Text Manipulation
XML
Understanding XML Syntax
Importing XML Data into Excel
HANDS-ON EXERCISE 3: XML
CHAPTER OBJECTIVES REVIEW
KEY TERMS
MULTIPLE CHOICE
PRACTICE EXERCISES
MID-LEVEL EXERCISES
CAPSTONE EXERCISE
BEYOND THE CLASSROOM
CHAPTER TEN ? Collaboration and Workbook Distribution
CASE STUDY: CULINARY ARTS SCHOLARSHIP DINNER
OBJECTIVES
CUSTOMIZATION AND PERSONALIZATION
Customizing Excel
Changing Properties
HANDS-ON EXERCISE 1: Customization and Personalization
COLLABORATION
Sharing and Merging Workbooks
Inserting Comments
Tracking Changes
HANDS-ON EXERCISE 2: Collaboration
WORKBOOK INFORMATION
Checking for Issues
Protecting a Workbook
HANDS-ON EXERCISE 3: Workbook Information
WORKBOOK DISTRIBUTION
Saving a Workbook in Different Formats
Sending a Workbook to Others
HANDS-ON EXERCISE 3: Workbook Distribution
CHAPTER OBJECTIVES REVIEW
KEY TERMS
MULTIPLE CHOICE
PRACTICE EXERCISES
MID-LEVEL EXERCISES
CAPSTONE EXERCISE
BEYOND THE CLASSROOM
CHAPTER ELEVEN ? Templates, Styles, and Macros
CASE STUDY: PERSONAL FINANCIAL CONSULTANT
OBJECTIVES
TEMPLATES, THEMES, AND STYLES
Selecting a Template
Applying Themes and Backgrounds
Applying Cell Styles
HANDS-ON EXERCISE 1: Templates, Themes, and Styles
CUSTOM TEMPLATES AND WORKBOOK PROTECTION
Creating and Using a Template
Protecting a Cell, a Worksheet, and a Workbook
HANDS-ON EXERCISE 2: Custom Templates and Workbook Protection
MACROS
Creating a Macro
Creating Macro Buttons
Setting Macro Security
HANDS-ON EXERCISE 3: Macros
CHAPTER OBJECTIVES REVIEW
KEY TERMS
MULTIPLE CHOICE
PRACTICE EXERCISES
MID-LEVEL EXERCISES
CAPSTONE EXERCISE
BEYOND THE CLASSROOM
GLOSSARY
INDEX
