Your Office Microsoft Excel 2013, Comprehensive

by Kinser, Amy S.; Hammerle, Patti; Moriarity, Brant; Nightingale, Jennifer Paige; O'Keefe, Timothy

• ISBN13: 9780133143225
• ISBN10: 0133143228
• Edition: 1st
• Format: Package
• Copyright: 2013-06-17
• Publisher: Pearson

Summary

This book offers full, comprehensive coverage of Excel. Ideal for business courses.

Make Microsoft Office, Your Office

The Your Office series prepares you to use both technical and soft skills in the real world. Our modern approach weaves the technical content into realistic business scenarios and focuses on using Office as a decision-making tool. Students learn best by doing, so you will be active from page 1. There is plenty of guidance at first, but the case exercises will ask more from you as the book progresses. Innovative features help you progress from a basic understanding to mastery of each application, empowering you to perform with confidence in Windows 8, Word, Excel, Access, and PowerPoint, even on mobile devices. No matter what career path you’re on, the Your Office series will give you the foundation to succeed.

Table of Contents

<>Contents

Common Features

Common Features WORKSHOP 1: The Common Features of Microsoft Office

PREPARE CASE: Painted Paradise Resort and Spa Employee Training Preparation

Working with the Office Interface
Understand Office Applications and Accounts
Start Office Programs and Manipulate Windows
Use the Office Ribbon, Contextual Tools, and Other Menus
Manipulating Files and Finding Help in Office
Manage Files in Office
Get Help
Understanding the Cloud and Touch Integration
Use Windows SkyDrive
Use Touch Mode, Gestures, and Reading Mode

Concept Check

Key Terms

Visual Summary

Practice

Practice Case 1

Problem Solve

Problem Solve Case 1

Perform

Perform in Your Career

Perform in Your Life

 

Excel Module 1-Understanding the Fundamentals

Excel WORKSHOP 1: Navigate, Manipulate, and Print Worksheets

PREPARE CASE: Red Bluff Golf Course and Pro Shop Golf Cart Purchase Analysis

Excel Worksheets—What If Data and Information Could Speak?
Understand Spreadsheet Terminology and Components
Navigate Worksheets and Workbooks
Document Your Work!
Enter and Edit Data
Manipulate Cells and Cell Ranges
Manipulate Columns and Rows
Printing Worksheets and Manipulating Workbooks
Preview and Print Worksheets
Manipulate Worksheets and Workbooks

Concept Check

Key Terms

Visual Summary

Practice

Practice Case 1

Problem Solve

Problem Solve Case 1

Perform

Perform in Your Career

 

Excel WORKSHOP 2: Formatting, Functions, and Formulas

PREPARE CASE: Red Bluff Golf Course & Pro Shop Sales Analysis

Worksheet Formatting
Format Cells, Cell Ranges, and Worksheets
Creating Information for Decision Making
What Is a Function?
What Is a Formula?
Decision Making
Documenting Your Work

Concept Check

Key Terms

Visual Summary

Practice

Practice Case 1

Problem Solve

Problem Solve Case 1

Perform

Perform in Your Life

EXCEL Module 1 Capstone

More Practice

More Practice Case 1

Problem Solve

Problem Solve Case 1

Problem Solve Case 2

Perform

Perform in Your Life

Perform in Your Career

Perform in Your Team

How Others Perform

 

Excel Module 2-Conducting Business Analysis

Excel WORKSHOP 3: Cell References, Named Ranges, and Functions

PREPARE CASE: Painted Paradise Resort and Spa Wedding Planning

Cell References and Named Ranges
Types of Cell References
Naming a Cell Range
Functions
Function Structure and Construction
Common Math and Statistical Functions
Common Date and Time Functions
Text Functions and Flash Fill
Financial and Lookup & Reference Functions

Concept Check

Key Terms

Visual Summary

Practice

Practice Case 1

Problem Solve

Problem Solve Case 1

Perform

Perform in Your Life

 

Excel WORKSHOP 4: Effective Charts

PREPARE CASE: Turquoise Oasis Spa Sales Reports

Designing a Chart
Exploring Chart Characteristics: Types, Layouts, and Styles
Chart Locations
Chart Types
Laying out a Chart
Chart Layouts, Styles, and Data
Editing and Formatting Charts
Strategic Statements with Charts
Recognizing and Correcting Confusing Charts

Concept Check

Key Terms

Visual Summary

Practice

Practice Case 1

Problem Solve

Problem Solve Case 1

Perform

Perform in Your Career

EXCEL Module 2 Capstone

More Practice

More Practice Case 1

Problem Solve

Problem Solve Case 1

Problem Solve Case 2

Perform

Perform in Your Life

Perform in Your Career

Perform in Your Team

How Others Perform 

 

Excel Module 3- Integrating Complex Functions into Business Analysis

Excel WORKSHOP 5: Complex Conditional and Retrieval Functions

PREPARE CASE: Red Bluff Golf Course & Pro Shop Sales Analysis

Integrating Logical Functions
Preview the Data
Use IF Functions
Build Nested IF Functions
Integrate Conjunction Functions into IF Functions
Using Conditional Aggregate Functions
Use Conditional Statistical Functions
Use Conditional Math Functions
Construct Database Functions
Retrieving Data Using LOOKUP and Reference Functions
Explore LOOKUP Functions
Retrieve Data Using MATCH, INDEX, and INDIRECT
Handle Errors with the IFERROR Function

Concept Check

Key Terms

Visual Summary

Practice

Practice Case 1

Problem Solve

Problem Solve Case 1

Perform

Perform in Your Life

 

Excel WORKSHOP 6: Analyze Data using Tables

PREPARE CASE: Golf Course Marketing Strategies

Organizing Data with Tables
Work with Data and Information
Work with Data and Information in Data Tables
Use the SUBTOTAL Function and Filters in Data Tables
Organizing and Analyzing with PivotTables and PivotCharts
Develop and Customize PivotTables
Develop and Customize PivotCharts

Concept Check

Key Terms

Visual Summary

Practice

Practice Case 1

Problem Solve

Problem Solve Case 1

Perform

Perform in Your Career

EXCEL Module 3 Capstone

More Practice

More Practice Case 1

Problem Solve

Problem Solve Case 1

Problem Solve Case 2

Perform

Perform in Your Life

Perform in Your Career

Perform in Your Team

How Others Perform

 

Excel Module 4-Building an Application with Multiple Worksheets and Workbooks

WORKSHOP 7: Multiple Worksheets, Workbooks, and Templates

PREPARE CASE: Turquoise Oasis Spa Therapist Sales and Service Analysis

Working with Multiple Worksheets
Group Worksheets
Create Summary Worksheets
Using Multiple Workbooks
Work with Multiple Workbooks
Collaborate Using Multiple Workbooks
Using and Creating Templates
Use Existing Templates
Create Templates from an Existing Workbook

Concept Check

Key Terms

Visual Summary

Practice

Practice Case 1

Problem Solve

Problem Solve Case 1

Perform

Perform in Your Life

 

Excel WORKSHOP 8: Refine an Excel Application

PREPARE CASE: Turquoise Oasis Spa Application

Auditing Formulas
View Formula Precedents and Dependents
Evaluate Formulas
Correct Circular References
Use the Watch Window
Creating Data Validation Rules
Control Data Entry with Data Validation
Developing User-Friendly Excel Applications
Create and Use Macros
Change How to Navigate a Workbook
Protect Workbooks and Worksheets

Concept Check

Key Terms

Visual Summary

Practice

Practice Case 1

Problem Solve

Problem Solve Case 1

Perform

Perform in Your Career

EXCEL Module 4 Capstone

More Practice

More Practice Case 1

Problem Solve

Problem Solve Case 1

Problem Solve Case 2

Perform

Perform in Your Life

Perform in Your Career

Perform in Your Team

How Others Perform

 

Excel Module 5- Manipulating Data Sets for Decision Making

Excel WORKSHOP 9: Organize, Import, Export, and Cleanse Data Sets

PREPARE CASE: Red Bluff Golf Course & Pro Shop Data Integration

Working with Data Sets
Understand the Importance of External Data Sets
Working with XML Data and Text Files
Connect to an Access Database
Making Data Useful
Clean Imported Data
Use Flash Fill to Cleanse Data
Separate Data Using Text Functions and Wizards
Clean Date-Related Data

Concept Check

Key Terms

Visual Summary

Practice

Practice Case 1

Problem Solve

Problem Solve Case 1

Perform

Perform in Your Life

 

Excel WORKSHOP 10: Data Tables, Scenario Manager, and Solver

PREPARE CASE: The Red Bluff Golf Course & Pro Shop Business Planning Analysis

Examining Cost-Volume-Profit Relationships
Perform Break-Even Analysis
Analyze Variables in Formulas Through the Use of Data Tables
Use Goal Seek
Using the Scenario Manager
Use the Scenario Manager to Create Scenarios
Create Scenario Reports
Using Solver
Understand the Use of the Solver Add-In
Solve Complex Problems Using Solver
Generate and Interpret Solver Answer Reports

Concept Check

Key Terms

Visual Summary

Practice

Practice Case 1

Problem Solve

Problem Solve Case 1

Perform

Perform in Your Career

EXCEL Module 5 Capstone

More Practice

More Practice Case 1

Problem Solve

Problem Solve Case 1

Problem Solve Case 2

Perform

Perform in Your Life

Perform in Your Career

Perform in Your Team

How Others Perform

 

Excel Module 6- Building Financial and Statistical Models

Excel WORKSHOP 11: Loan Amortization, Investment Analysis, and Asset Depreciation

PREPARE CASE: The Turquoise Oasis Spa Financial Analysis

Constructing a Financial Analysis on Loans
Construct a Loan Analysis with PMT, RATE, and NPER
Calculate Cumulative Interest and Principal Using CUMIPMT and CUMPRINC
Create an Amortization Schedule Using PPMT and IPMT
Constructing a Financial Analysis of Investments
Analyze Bonds Using PV and FV
Analyze Investments Using NPV, XNPV, IRR, and XIRR
Calculating the Depreciating Value of Business Assets
Calculate the Depreciation of Assets Using the SLN, DB, and DDB Functions

Concept Check

Key Terms

Visual Summary

Practice

Practice Case 1

Problem Solve

Problem Solve Case 1

Perform

Perform in Your Life

 

Excel WORKSHOP 12: Business Statistics and Regression Analysis

PREPARE CASE: The Turquoise Oasis Spa: Using Statistics in Decision Making

Applying Basic Statistical Methods to Business
Understand the Language of Statistics
Understand the Basic Types of Data
Conduct Basic Statistical Analyses in Excel
Generate Descriptive Statistics and Other Analyses Using the Analysis ToolPak
Applying Probability Distributions to Business
Predict Business Outcomes Using Probability Distribution Functions
Finding Relationships in Data
Find Relationships in Data Using COVARIANCE.S and CORREL
Determine Relationships Between Multiple Variables Using a Correlation Matrix
Use Regression Analysis to Predict Future Values

Concept Check

Key Terms

Visual Summary

Practice

Practice Case 1

Problem Solve

Problem Solve Case 1

Perform

Perform in Your Career

EXCEL Module 6 Capstone

More Practice

More Practice Case 1

Problem Solve

Problem Solve Case 1

Problem Solve Case 2

Perform

Perform in Your Life

Perform in Your Career

Perform in Your Team

How Others Perform

 

Excel Module 7- Enabling Decisions with Data Visualization and VBA

Excel WORKSHOP 13: Excel’s Data Model, PowerPivot, and Power View

PREPARE CASE: The Red Bluff Golf Course & Pro Shop Dashboards, KPIs, and Data Visualizations

Exploring the Importance of Business Intelligence
Understand the Basics of Dashboard Design
Explore the New Data Model
Create Advanced Data Models Using PowerPivot
Create PivotTables and PivotCharts with PowerPivot
Use Apps for Office for Data Visualizations
Prepare a Dashboard for Production
Exploring the Benefits of Personalized Business Intelligence
Generate Visual Reports with Power View

Concept Check

Key Terms

Visual Summary

Practice

Practice Case 1

Problem Solve

Problem Solve Case 1

Perform

Perform in Your Life

 

Excel WORKSHOP 14: Visual Basic for Applications

PREPARE CASE: The Red Bluff Golf Course & Pro Shop Spreadsheet Enhancement with Form Controls and VBA

Enhancing the Readability and Interactivity of Dashboards
Enhance Spreadsheets with Form Controls
Leveraging the Power of Visual Basic for Applications (VBA)
Understand the Components of VBA
Create Custom Functions with VBA
Improve Readability of VBA with Formatting and Structure
Troubleshoot VBA
Create and Use Loops in VBA
Assign VBA Procedures to Events
Protect and Secure a Workbook

Concept Check

Key Terms

Visual Summary

Practice

Practice Case 1

Problem Solve

Problem Solve Case 1

Perform

Perform in Your Career

EXCEL Module 7 Capstone

More Practice

More Practice Case 1

Problem Solve

Problem Solve Case 1

Problem Solve Case 2

Perform

Perform in Your Life

Perform in Your Career

Perform in Your Team

How Others Perform

Appendix: Efficient Interaction with a Touchscreen

PREPARE CASE: Painted Paradise Resort and Spa Works with Touch Features

Interacting with a Windows 8 Tablet PC

Understand Variations in Tablets

Understand Methods for Interacting with a Tablet PC

Using the Touch-Screen Interface

Use Flick, Touch, and Bezel Gestures

Use Office 2013 Touch Gestures

Use Other Touch Features

Concept Check

Key Terms