Chapters include: introduction to Excel; formulas and functions; charts; working with large worksheets and tables; convert data to information; and consolidate data and link files.
Table of Contents:
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
