Exploring Microsoft Office 2013, Volume 2

by Poatsy, Mary Anne; Mulbery, Keith; Cameron, Eric; Davidson, Jason; Williams, Jerri; Lau, Linda; Lawson, Rebecca; Grauer, Robert T.; Dodson, Sallie

• ISBN13: 9780133412123
• ISBN10: 0133412121
• Edition: 1st
• Format: Spiralbound w/ Access Card
• Copyright: 2013-07-03
• Publisher: Pearson

Summary

This book covers advanced Word, Excel, Access, and PowerPoint.

Move students beyond the point-and-click.

 

The goal of the Exploring series is to move students beyond the point and click, to understanding the why and how behind each skill. And, because so much learning takes place outside of the classroom, this series provides learning tools that students can access anywhere, any time.

 

Students go to college now with a different set of skills than they did years ago. With this in mind, the Exploring series seeks to move students beyond the basics of the software at a faster pace, without sacrificing coverage of the fundamental skills that everyone needs to know.

NOTE: Before purchasing, check with your instructor to ensure you select the correct ISBN. Several versions of Pearson's MyLab & Mastering products exist for each title, and registrations are not transferable. To register for and use Pearson's MyLab & Mastering products, you may also need a Course ID, which your instructor will provide.

 

Used books, rentals, and purchases made outside of Pearson

If purchasing or renting from companies other than Pearson, the access codes for Pearson's MyLab & Mastering products may not be included, may be incorrect, or may be previously redeemed. Check with the seller before completing your purchase.

 

Table of Contents

 

Microsoft Office Word 2013

Chapter Five Desktop Publishing and Graphic Design: Creating a

Newsletter, Working with Graphics, and Linking Objects 1

Objectives         1

Case Study: Along the Greenways         1

Desktop Publishing         2

Constructing a Newsletter 2

Developing a Document Design 6

Hands-On Exercise 1:

Desktop Publishing 10

Decorative Text and Drawing Tools         16

Inserting Graphic Objects 16

Manipulating Graphic Objects 22

Hands-On Exercise 2:

Decorative Text and Drawing Tools 27

Object Linking and Embedding         34

Using OLE to Insert an Object 34

Updating a Linked Object 37

Hands-On Exercise 3:

Object Linking and Embedding 39

Chapter Objectives Review         42

Key Terms Matching         43

Multiple Choice         44

Practice Exercises         45

Mid-Level Exercises         50

Beyond the Classroom         54

Capstone Exercise         55

 

Chapter Six Time Saving Tools: Using Templates, Multiple Documents, and Themes 57

Objectives         57

Case Study: Computer Training Concepts, Inc. 57

Document Templates         58

Selecting a Template from the Backstage View 58

Creating a Word Template 61

Using Building Blocks 61

Hands-On Exercise 1:

Document Templates 64

Multiple Documents         69

Viewing Documents Side by Side 69

Merging Documents 70

Using Navigational Tools 75

Hands-On Exercise 2:

Multiple Documents 80

Document Themes         87

Customizing Theme Colors, Fonts, and Effects 87

Hands-On Exercise 3:

Document Themes 91

Chapter Objectives Review         95

Key Terms Matching         96

Multiple Choice         97

Practice Exercises         98

Mid-Level Exercises         102

Beyond the Classroom         105

Capstone Exercise         106

 

Chapter Seven Document Automation: Forms, Macros, and Security 109

Objectives         109

Case Study: Oak Grove Products, Inc. 109

Forms         110

Creating an Electronic Form 110

Inserting Form Controls 112

Protecting a Form 114

Hands-On Exercise 1:

Forms 116

Introduction to Macros         124

Creating a Macro 124

Running a Macro 127

Hands-On Exercise 2:

Introduction to Macros 129

Document Protection and Authentication         134

Applying Document Restrictions 134

Setting Passwords to Open a Document 137

Using Digital Signatures to Authenticate Documents 140

Hands-On Exercise 3:

Document Protection and Authentication 143

Chapter Objectives Review         151

Key Terms Matching         152

Multiple Choice         153

Practice Exercises         154

Mid-Level Exercises         157

Beyond the Classroom         162

Capstone Exercise         163

A01_GRAU2123_01_SE_FM.indd 9 6/4/13 7:22 PM

x Contents

¿ Chapter SIX What-If Analysis: Using Decision-Making Tools 265

Objectives         265

Case Study: Personal Finance: Buying Your

First Home         265

One- and Two-Variable Data Tables         266

Creating a One-Variable Data Table 266

Creating a Two-Variable Data Table 270

Hands-On Exercise 1:

One- and Two-Variable Data Tables 272

Goal Seek and Scenario Manager         277

Identifying an Input Value with Goal Seek 277

Using Scenario Manager 278

Generating Scenario Summary Reports 280

Hands-On Exercise 2:

Goal Seek and Scenario Manager 282

Solver_286

Loading the Solver Add-In 286

Optimizing Results with Solver 287

Hands-On Exercise 3:

Solver 293

Chapter Objectives Review_297

Key Terms Matching_298

Multiple Choice_299

Practice Exercises_300

Mid-Level Exercises_303

Beyond the Classroom_305

Capstone Exercise_307

Microsoft Office Excel 2013

¿ Chapter FIVE Subtotals, PivotTables, and PivotCharts:

Summarizing and Analyzing Data 215

Objectives         215

Case Study: Ivory Halls Publishing Company 215

Subtotals and Outlines         216

Subtotaling Data 216

Grouping and Ungrouping Data 219

Hands-On Exercise 1:

Subtotals and Outlines 220

Pivottable Basics         224

Creating a PivotTable 224

Modifying a PivotTable 227

Hands-On Exercise 2:

PivotTable Basics 232

Pivottable Options         237

Filtering and Slicing a PivotTable 237

Creating a Calculated Field 240

Hands-On Exercise 3:

PivotTable Options 242

Pivottable Design and Pivotcharts_247

Formatting a PivotTable 247

Using PowerPivot Functionality 248

Creating a PivotChart 250

Hands-On Exercise 4:

PivotTable Design and PivotCharts 252

Chapter Objectives Review_254

Key Terms Matching_255

Multiple Choice_256

Practice Exercises_257

Mid-Level Exercises_261

Beyond the Classroom_263

Capstone Exercise_264

¿ Chapter eight Word and the Internet: Web Page Creation

and Enhancement, and Blogs 165

Objectives         165

Case Study: A Math Tutoring Club 165

Web Page Creation         166

Customizing the Ribbon 166

Building and Publishing a Web Page 169

Hands-On Exercise 1:

Web Page Creation 177

Web Page Enhancement         186

Creating a Blog Post 186

Presenting a Word Document Online 188

Enhancing a Web Page 190

Hands-On Exercise 2:

Web Page Enhancement 194

Chapter Objectives Review_202

Key Terms Matching_203

Multiple Choice_204

Practice Exercises_205

Mid-Level Exercises_208

Beyond the Classroom_211

Capstone Exercise_212

A01_GRAU2123_01_SE_FM.indd 10 6/4/13 7:22 PM

Contents xi

¿ Chapter SEVEN Specialized Functions: Logical, Lookup,

Databases, and Finances 309

Objectives         309

Case Study: Transpayne Filtration         309

Logical and Lookup Functions         310

Creating a Nested Logical Function 310

Using Match and Index Lookup Functions 314

Hands-On Exercise 1:

Logical and Lookup Functions 317

Database Filtering and Functions         321

Using Advanced Filtering 321

Manipulating Data with Database Functions 323

Hands-On Exercise 2:

Database Filtering and Functions 326

Financial Functions         330

Creating a Loan Amortization Table 330

Performing Other Financial Calculations 332

Hands-On Exercise 3:

Financial Functions 335

Chapter Objectives Review         339

Key Terms Matching         340

Multiple Choice         341

Practice Exercises         342

Mid-Level Exercises         347

Beyond the Classroom         350

Capstone Exercise         351

 

Chapter Eight Statistical Functions: Analyzing Statistics 353

Objectives         353

Case Study: Education Evaluation         353

Math and Statistical Functions         354

Using Conditional Math and Statistical Functions 354

Calculating Relative Standing with Statistical Functions 357

Hands-On Exercise 1:

Math and Statistical Functions 362

Descriptive Statistical Functions         367

Measuring Central Tendency 367

Hands-On Exercise 2:

Descriptive Statistical Functions 371

Inferential Statistics         374

Loading the Analysis ToolPak 374

Performing Analysis Using the Analysis ToolPak 374

Performing Analysis of Variance (Anova) 374

Calculating Covariance 376

Creating a Histogram 376

Hands-On Exercise 3:

Inferential Statistics 378

Chapter Objectives Review         382

Key Terms Matching         383

Multiple Choice         384

Practice Exercises         385

Mid-Level Exercises         388

Beyond the Classroom         390

Capstone Exercise         391

 

Chapter Nine Multiple-Sheet Workbook Management: Ensuring Quality Control 393

Objectives         393

Case Study: Circle City Sporting Goods         393

Multiple Worksheets         394

Working with Grouped Worksheets 394

Managing Windows and Workspaces 397

Inserting Hyperlinks 399

Hands-On Exercise 1:

Multiple Worksheets 402

3-D Formulas and Linked Workbooks         407

Inserting a 3-D Formula 407

Linking Workbooks 409

Hands-On Exercise 2:

3-D Formulas and Linked Workbooks 412

Formula Audits and Data Validation_416

Auditing Formulas 417

Setting Up a Watch Window 419

Validating Data 420

Hands-On Exercise 3:

Formula Audits and Data Validation 423

Chapter Objectives Review_428

Key Terms Matching_429

Multiple Choice_430

Practice Exercises_431

Mid-Level Exercises_434

Beyond the Classroom_437

Capstone Exercise_439

 

Chapter Ten Imports, Web Queries, and XML: Managing Data 441

Objectives         441

Case Study: Stock Analysis         441

External Data         442

Importing Data from External Sources 442

Creating a Web Query 446

Managing Connections 448

Hands-On Exercise 1:

External Data 450

Text Manipulation         456

Converting Text to Columns 456

Manipulating Text with Functions 457

Using Flash Fill 459

Hands-On Exercise 2:

Text Manipulation 460

XML 463

Understanding Xml Syntax 463

Importing Xml Data into Excel 464

Hands-On Exercise 3:

Xml 467

Chapter Objectives Review         470

Key Terms Matching         471

Multiple Choice         472

Practice Exercises         473

Mid-Level Exercises         476

Beyond the Classroom         478

Capstone Exercise         479

 

Chapter Eleven Collaboration and Workbook Distribution: Sharing Data with Others 481

Objectives         481

Case Study: Marching Band Senior Dinner         481

Customization and Personalization         482

Customizing Excel 482

Changing Properties 485

Hands-On Exercise 1:

Customization and Personalization 488

Collaboration         491

Sharing and Merging Workbooks 491

Inserting Comments 494

Tracking Changes 496

Hands-On Exercise 2:

Collaboration 500

Workbook Information         505

Checking for Issues 505

Protecting a Workbook 507

Hands-On Exercise 3:

Workbook Information 511

Workbook Distribution         514

Saving a Workbook in Different Formats 514

Sending a Workbook to Others 516

Hands-On Exercise 4:

Workbook Distribution 519

Chapter Objectives Review         521

Key Terms Matching         522

Multiple Choice         523

Practice Exercises         524

Mid-Level Exercises         527

Beyond the Classroom         529

Capstone Exercise         530

 

Chapter Twelve Templates, Styles, and Macros: Standardizing Workbooks 531

Objectives         531

Case Study: Staff Accounting Services         531

Templates, Themes, and Styles         532

Selecting a Template 532

Applying Themes and Backgrounds 533

Applying Cell Styles 535

Hands-On Exercise 1:

Templates, Themes, and Styles 538

Custom Templates and Workbook Protection         541

Creating and Using a Template 541

Protecting a Cell, a Worksheet, and a Workbook 542

Hands-On Exercise 2:

Custom Templates and Workbook Protection 546

Macros         551

Creating a Macro 551

Creating Macro Buttons 554

Setting Macro Security 555

Hands-On Exercise 3:

Macros 557

Visual Basic for Applications         561

Creating a Sub Procedure 561

Creating a Custom Function 563

Hands-On Exercise 4:

Visual Basic for Applications 565

Chapter Objectives Review         568

Key Terms Matching         569

Multiple Choice         570

Practice Exercises         571

Mid-Level Exercises         576

Beyond the Classroom         578

Capstone Exercise         579

 

Microsoft Office Access 2013

Chapter Five Data Validation and Data Analysis: Reducing Errors and Extracting Better Information 581

Objectives         581

Case Study: Implementing a New Database At

Tommy's Shelter         581

Data Validation in Tables         582

Establishing Data Validation 582

Creating an Input Mask 585

Creating and Modifying a Lookup Field 587

Hands-On Exercise 1:

Data Validation in Tables 592

Data Analysis Using Advanced Select Queries         597

Creating a Parameter Query 597

Using Advanced Functions in the Expression Builder 601

Performing Date Arithmetic 607

Hands-On Exercise 2:

Data Analysis Using Advanced Select Queries 611

Chapter Objectives Review         616

Key Terms Matching         617

Multiple Choice         618

Practice Exercises         619

Mid-Level Exercises         622

Beyond the Classroom         624

Capstone Exercise         626

 

Chapter Six Action and Specialized Queries: Moving Beyond the Select Query 629

Objectives         629

Case Study: Replacememt China, Inc. 629

Action Queries         630

Determining When to Use an Action Query 630

Updating Data with an Update Query 632

Adding Records to a Table with an Append Query 635

Creating a Table with a Make Table Query 638

Deleting Records with a Delete Query 639

Hands-On Exercise 1:

Action Queries 642

Queries for Special Conditions         649

Summarizing Data with a Crosstab Query 649

Finding Duplicate Records with a Query 653

Finding Unmatched Records with a Query 655

Hands-On Exercise 2:

Queries for Special Conditions 659

Chapter Objectives Review         663

Key Terms Matching         664

Multiple Choice         665

Practice Exercises         666

Mid-Level Exercises         670

Beyond the Classroom         673

Capstone Exercise         674

 

Chapter Seven Advanced Forms and Reports: Moving Beyond the Basics 675

Objectives         675

Case Study: Technical Services         675

Advanced Forms         676

Restricting Edits in a Form 676

Creating a Drop-Down Menu 678

Setting the Tab Order 681

Using Subforms 682

Hands-On Exercise 1:

Advanced Forms 686

Controls and Sections         691

Using Advanced Controls 691

Using Sections 695

Hands-On Exercise 2:

Controls and Sections 703

Chapter Objectives Review         708

Key Terms Matching         709

Multiple Choice         710

Practice Exercises         711

Mid-Level Exercises         714

Beyond the Classroom         716

Capstone Exercise         718

 

Chapter Eight Get Connected: Exchanging Data Between Access and Other Applications 719

Objectives         719

Case Study: Property Management Data Exchange         719

Connecting Access to External Files         720

Creating a Hyperlink Field 720

Adding an Attachment Field 722

Adding Attachment Controls to Forms and Reports 725

Hands-On Exercise 1:

Connecting Access to External Files 728

Exporting Data to Office AND OTHER Applications         735

Exporting Data to Excel 735

Exporting Data to Word 739

Exporting Data to a PDF or XPS Document 742

Exporting Objects to Another Access Database 745

Hands-On Exercise 2:

Exporting Data to Office and Other Applications 748

Importing Data Into Access         756

Linking to an Access Table 756

Linking to an Excel Spreadsheet 759

Importing an Excel Spreadsheet 762

Importing a Text File 765

Hands-On Exercise 2:

Importing Data into Access 768

Chapter Objectives Review         776

Key Terms Matching         777

Multiple Choice         778

Practice Exercises         779

Mid-Level Exercises         784

Beyond the Classroom         788

Capstone Exercise         789

 

Chapter Nine Fine-Tuning the Database: Analyzing and Improving

Database Performance 791

Objectives         791

Case Study: The Metropolitan Zoo 791

Database Normalization         792

Verifying First Normal Form 792

Verifying Second Normal Form 795

Verifying Third Normal Form 796

Creating Relationships 798

Hands-On Exercise 1:

Database Normalization 799

Built-In Analysis and Design Tools         803

Using the Database Documenter Tool 803

Using the Performance Analyzer Tool 805

Using the Table Analyzer Tool 807

Using the Database Splitter Tool 810

Hands-On Exercise 2:

Built-In Analysis and Design Tools 813

Database Security 817

Creating a Menu System 817

Encrypting and Password Protecting a Database 820

Digitally Signing and Publishing a Database 822

Saving a Database as an ACCDE File 823

Hands-On Exercise 3:

Database Security 825

Chapter Objectives Review         828

Key Terms Matching         829

Multiple Choice         830

Practice Exercises         831

Mid-Level Exercises         834

Beyond the Classroom         838

Capstone Exercise         839

 

Chapter Ten Using Macros and SQL in Access: Advanced Techniques 841

Objectives         841

Case Study: Retirement Plan Helps Retain Employees         841

Macro DESIGN 842

Understanding the Purpose of a Macro 842

Creating a Stand-Alone Macro 843

Using the Macro Designer 844

Attaching an Embedded Macro to an Event 845

Hands-On Exercise 1:

Macro Design 849

Data Macros         855

Identifying When to Use a Data Macro 855

Creating an Event-Driven Data Macro 855

Creating a Named Data Macro 856

Hands-On Exercise 2:

Data Macros 859

Structured Query Language         865

Understanding the Fundamentals of SQL 865

Interpreting an SQL SELECT Statement 866

Using an SQL SELECT Statement as a Record Source 868

Hands-On Exercise 3:

Structured Query Language 872

Chapter Objectives Review         875

Key Terms Matching         876

Multiple Choice         877

Practice Exercises         878

Mid-Level Exercises         882

BEYOND THE CLASSROOM 886

Capstone Exercise         887

 

Microsoft Office PowerPoint 2013

Chapter Five Infographics: Creating Text Charts, Tables, and Graphs 889

Objectives         889

Case Study: Healthy Living: Antioxidants         889

Text-Based Charts         890

Creating a Poster or a Banner 892

Drawing a Table 893

Creating a Table Structure 894

Hands-On Exercise 1:

Text-Based Charts 897

Table Design         902

Formatting Table Components 902

Changing Table Layout 905

Sharing Information Between Applications 906

Hands-On Exercise 2:

Table Design 909

Statistical Charts and Graphs         913

Identifying Chart Types and Elements 913

Creating and Inserting a Chart 916

Switching Row and Column Data 918

Hands-On Exercise 3:

Statistical Charts and Graphs 920

Chart Modification         925

Changing a Chart Type 925

Changing the Chart Layout 925

Formatting Chart Elements 926

Hands-On Exercise 4:

Chart Modification 928

Chapter Objectives Review         932

Key Terms Matching         933

Multiple Choice         934

Practice Exercises         935

Mid-Level Exercises         939

Beyond the Classroom         942

Capstone Exercise         943

 

Chapter Six Interactivity and Advanced Animation: Engaging the Audience 947

Objectives         947

Case Study: Teaching Aids         947

Hyperlinks and Action Buttons         948

Inserting and Using Hyperlinks 948

Adding Action Buttons 952

Using a Trigger 955

Hands-On Exercise 1:

Hyperlinks and Action Buttons 957

Advanced Animation         966

Applying Multiple Animations to an Object 966

Applying a Motion Path Animation 967

Specifying Animation Settings and Timing 970

Animating Text 972

Using the Animation Pane 973

Hands-On Exercise 2:

Advanced Animation 975

Chapter Objectives Review         982

Key Terms Matching         983

Multiple Choice         984

Practice Exercises         985

Mid-Level Exercises         988

Beyond the Classroom         991

Capstone Exercise         992

 

Chapter Seven Customization: Customizing PowerPoint and the Slide Show 995

Objectives         995

Case Study: Survival Solutions         995

Powerpoint Customization         996

Setting PowerPoint Options 996

Customizing the Ribbon 999

Using Combine Shapes Commands 1002

Hands-On Exercise 1:

PowerPoint Customization 1004

Master Basics         1010

Modifying Handout and Notes Masters 1010

Modifying a Slide Master 1012

Saving a Slide Master as a Template 1018

Hands-On Exercise 2:

Master Basics 1020

Custom Shows         1029

Creating a Custom Slide Show 1029

Running and Navigating a Custom Slide Show 1031

Designating and Displaying Hidden Slides 1031

Hands-On Exercise 3:

Custom Shows 1034

Chapter Objectives Review         1036

Key Terms Matching         1037

Multiple Choice         1038

Practice Exercises         1039

Mid-Level Exercises         1044

Beyond the Classroom         1046

Capstone Exercise         1047

 

Chapter Eight Collaboration and Distribution: Collaborating, Preparing, Securing, and Sharing a Presentation 1049

Objectives         1049

Case Study: The Harbor Business Center         1049

Presentation Collaboration         1050

Working with Comments and Annotations 1050

Showing, Hiding, and Printing Markup 1052

Comparing and Merging Presentations 1054

Viewing Presentation Properties 1055

Hands-On Exercise 1:

Presentation Collaboration 1057

Preparation for Sharing and Presentation Security 1063

Checking a Presentation for Issues 1063

Protecting a Presentation 1066

Hands-On Exercise 2:

Preparation for Sharing and Presentation Security 1071

Presentation Sharing         1076

Selecting a Presentation File Type 1076

Saving and Sharing a Presentation 1082

Hands-On Exercise 3:

Presentation Sharing 1088

Chapter Objectives Review         1091

Key Terms Matching         1092

Multiple Choice         1093

Practice Exercises         1094

Mid-Level Exercises         1098

Beyond the Classroom         1101

Capstone Exercise         1102

 

Application Capstone Exercises

¿ Word 1103

¿ Excel 1108

¿ Access 1111

¿ PowerPoint 1113

Glossary 1117

Index 1125