Exploring Microsoft Excel 2013, Comprehensive

by Poatsy, Mary Anne; Mulbery, Keith; Davidson, Jason; Grauer, Robert T.

• ISBN13: 9780133412185
• ISBN10: 0133412180
• Edition: 1st
• Format: Spiral Bound
• Copyright: 2013-06-04
• Publisher: Pearson

Summary

This book offers full, comprehensive coverage of Excel.

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 2013

Chapter One Office Fundamentals and File Management: Taking the First Step 1

Objectives         1

Case Study: Spotted Begonia Art Gallery 1

Windows 8 Startup 2

Logging In with Your Microsoft Account 2

Identifying the Start Screen Components 3

Interacting with the Start Screen 4

Accessing the Desktop 4

Hands-On Exercise 1:

Windows 8 Startup 6

Files and Folders         10

Using File Explorer 10

Working with Folders and Files 13

Selecting, Copying, and Moving Multiple Files and Folders 15

Hands-On Exercise 2:

Files and Folders 17

Microsoft Office Software 22

Identifying Common Interface Components 22

Getting Office Help 28

Hands-On Exercise 3:

Microsoft Office Software 31

The Backstage View Tasks         36

Opening a File 36

Printing a File 38

Closing a File and Application 39

Hands-On Exercise 4:

The Backstage View Tasks 41

Home Tab Tasks         45

Selecting and Editing Text 45

Using the Clipboard Group Commands 49

Using the Editing Group Commands 52

Hands-On Exercise 5:

Home Tab Tasks 55

Insert Tab Tasks         60

Inserting Objects 60

Review Tab Tasks         62

Reviewing a File 62

Page Layout Tab Tasks         65

Using the Page Setup Dialog Box 66

Hands-On Exercise 6:

Insert Tab Tasks, Page Layout Tab Tasks, and

Review Tab Tasks 68

Chapter Objectives Review         72

Key Terms Matching         73

Multiple Choice 74

Practice Exercises         75

Mid-Level Exercises         79

Beyond the Classroom         81

Capstone Exercise 82

 

Microsoft Office Excel 2013

Chapter One Introduction to Excel: What Is a Spreadsheet? 83

Objectives         83

Case Study: ok Office Systems         83

Introduction to Spreadsheets         84

Exploring the Excel Window 84

Entering and Editing Cell Data 87

Hands-On Exercise 1:

Introduction to Spreadsheets 91

Mathematics and Formulas         94

Creating Formulas 94

Using Auto Fill 96

Displaying Cell Formulas 97

Hands-On Exercise 2:

Mathematics and Formulas 99

Workbook and Worksheet Management 104

Managing Worksheets 104

Managing Columns and Rows 107

Hands-On Exercise 3:

Workbook and Worksheet Management 111

Clipboard Tasks         116

Selecting, Moving, Copying, and Pasting Data 116

Hands-On Exercise 4:

Clipboard Tasks 121

Formatting         124

Applying Alignment and Font Options 124

Applying Number Formats 126

Hands-On Exercise 5:

Formatting 129

Page Setup and Printing         133

Selecting Page Setup Options 133

Previewing and Printing a Worksheet 137

Hands-On Exercise 6:

Page Setup and Printing 138

Chapter Objectives Review         142

Key Terms Matching         144

Multiple Choice 145

Practice Exercises         146

Mid-Level Exercises         151

Beyond the Classroom         154

Capstone Exercise 155

 

Chapter Two Formulas and Functions: Performing Quantitative Analysis 157

Objectives         157

Case Study: Townsend Mortgage Company 157

Formula Basics         158

Using Relative, Absolute, and Mixed Cell

References in Formulas 158

Correcting Circular References 160

Hands-On Exercise 1:

Formula Basics 162

Function Basics         166

Inserting a Function 166

Inserting Basic Math and Statistics Functions 168

Using Date Functions 173

Hands-On Exercise 2:

Function Basics 176

Logical, Lookup, and Financial Functions         181

Determining Results with the IF Function 181

Using Lookup Functions 184

Calculating Payments with the PMT Function 187

Hands-On Exercise 3:

Logical, Lookup, and Financial Functions 188

Range Names         192

Creating and Maintaining Range Names 192

Using Range Names in Formulas 194

Hands-On Exercise 4:

Range Names 195

Chapter Objectives Review         198

Key Terms Matching         199

Multiple Choice 200

Practice Exercises         201

Mid-Level Exercises         206

Beyond the Classroom         211

Capstone Exercise 213

 

Chapter Three Charts: Depicting Data Visually 215

Objectives         215

Case Study: Computer Job Outlook 215

Chart Creation Basics         216

Selecting the Data Source 216

Choosing a Chart Type 217

Moving, Sizing, and Printing a Chart 227

Hands-On Exercise 1:

Chart Creation Basics 230

Chart Elements         235

Adding Chart Elements 235

Formatting Chart Elements 238

Hands-On Exercise 2:

Chart Elements 243

Chart Design and Sparklines         247

Applying a Chart Style and Colors 247

Modifying the Data Source 248

Creating and Customizing Sparklines 249

Hands-On Exercise 3:

Chart Design and Sparklines 251

Chapter Objectives Review         254

Key Terms Matching         255

Multiple Choice 256

Practice Exercises         257

Mid-Level Exercises         260

Beyond the Classroom         263

Capstone Exercise 264

 

Chapter Four Datasets and Tables: Managing Large Volumes of Data 265

Objectives         265

Case Study: Reid Furniture Store 265

Large Datasets         266

Freezing Rows and Columns 267

Printing Large Datasets 267

Hands-On Exercise 1:

Large Datasets 271

Excel Tables         275

Designing and Creating Tables 275

Applying a Table Style 279

Hands-On Exercise 2:

Excel Tables 281

Table Manipulation 286

Sorting Data 286

Filtering Data 288

Hands-On Exercise 3:

Table Manipulation 292

Table Aggregation 297

Using Structured References and a Total Row 297

Hands-On Exercise 4:

Table Aggregation 300

Conditional Formatting         304

Applying Conditional Formatting 304

Creating a New Rule 308

Hands-On Exercise 5:

Conditional Formatting 311

Chapter Objectives Review         315

Key Terms Matching         316

Multiple Choice 317

Practice Exercises         318

Mid-Level Exercises         322

Beyond the Classroom         324

Capstone Exercise 326

Summarizing and Analyzing Data 327

 

Chapter Five Subtotals, PivotTables, and PivotCharts:

Objectives         327

Case Study: Ivory Halls Publishing Company 327

Subtotals and Outlines         328

Subtotaling Data 328

Grouping and Ungrouping Data 331

Hands-On Exercise 1:

Subtotals and Outlines 332

Pivottable Basics         336

Creating a PivotTable 336

Modifying a PivotTable 339

Hands-On Exercise 2:

PivotTable Basics 344

Pivottable Options         349

Filtering and Slicing a PivotTable 349

Creating a Calculated Field 352

Hands-On Exercise 3:

PivotTable Options 354

Pivottable Design and Pivotcharts         359

Formatting a PivotTable 359

Using PowerPivot Functionality 360

Creating a PivotChart 362

Hands-On Exercise 4:

PivotTable Design and PivotCharts 364

Chapter Objectives Review         366

Key Terms Matching         367

Multiple Choice 368

Practice Exercises         369

Mid-Level Exercises         373

Beyond the Classroom         375

Capstone Exercise 376

 

 

Chapter Six What-If Analysis: Using Decision-Making Tools 377

Objectives         377

Case Study: Personal Finance: Buying Your First Home 377

One- and Two-Variable Data Tables         378

Creating a One-Variable Data Table 378

Creating a Two-Variable Data Table 382

Hands-On Exercise 1:

One- and Two-Variable Data Tables 384

Goal Seek and Scenario Manager 389

Identifying an Input Value with Goal Seek 389

Using Scenario Manager 390

Generating Scenario Summary Reports 392

Hands-On Exercise 2:

Goal Seek and Scenario Manager 394

Solver 398

Loading the Solver Add-In 398

Optimizing Results with Solver 399

Hands-On Exercise 3:

Solver 405

Chapter Objectives Review         409

Key Terms Matching         410

Multiple Choice 411

Practice Exercises         412

Mid-Level Exercises         415

Beyond the Classroom         417

Capstone Exercise 419

 

Chapter Seven Specialized Functions: Logical, Lookup, Databases, and Finances 421

Objectives         421

Case Study: Transpayne Filtration 421

Logical and Lookup Functions         422

Creating a Nested Logical Function 422

Using Match and Index Lookup Functions 426

Hands-On Exercise 1:

Logical and Lookup Functions 429

Database Filtering and Functions         433

Using Advanced Filtering 433

Manipulating Data with Database Functions 435

Hands-On Exercise 2:

Database Filtering and Functions 438

Financial Functions         442

Creating a Loan Amortization Table 442

Performing Other Financial Calculations 444

Hands-On Exercise 3:

Financial Functions 447

Chapter Objectives Review         451

Key Terms Matching         452

Multiple Choice 453

Practice Exercises         454

Mid-Level Exercises         459

Beyond the Classroom         462

Capstone Exercise 463

 

Chapter Eight Statistical Functions: Analyzing Statistics 465

Objectives         465

Case Study: Education Evaluation 465

Math and Statistical Functions         466

Using Conditional Math and Statistical Functions 466

Calculating Relative Standing with Statistical Functions 469

Hands-On Exercise 1:

Math and Statistical Functions 474

Descriptive Statistical Functions         479

Measuring Central Tendency 479

Hands-On Exercise 2:

Descriptive Statistical Functions 483

Inferential Statistics         486

Loading the Analysis ToolPak 486

Performing Analysis Using the Analysis ToolPak 486

Performing Analysis of Variance (Anova) 486

Calculating Covariance 488

Creating a Histogram 488

Hands-On Exercise 3:

Inferential Statistics 490

Chapter Objectives Review         494

Key Terms Matching         495

Multiple Choice 496

Practice Exercises         497

Mid-Level Exercises         500

Beyond the Classroom         502

Capstone Exercise 503

 

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

Objectives         505

Case Study: Circle City Sporting Goods         505

Multiple Worksheets         506

Working with Grouped Worksheets 506

Managing Windows and Workspaces 509

Inserting Hyperlinks 511

Hands-On Exercise 1:

Multiple Worksheets 514

3-D Formulas and Linked Workbooks         519

Inserting a 3-D Formula 519

Linking Workbooks 521

Hands-On Exercise 2:

3-D Formulas and Linked Workbooks 524

Formula Audits and Data Validation 528

Auditing Formulas 529

Setting Up a Watch Window 531

Validating Data 532

Hands-On Exercise 3:

Formula Audits and Data Validation 535

Chapter Objectives Review         540

Key Terms Matching         541

Multiple Choice 542

Practice Exercises         543

Mid-Level Exercises         546

Beyond the Classroom         549

Capstone Exercise 551

 

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

Objectives         553

Case Study: Stock Analysis         553

External Data 554

Importing Data from External Sources 554

Creating a Web Query 558

Managing Connections 560

Hands-On Exercise 1:

External Data 562

Text Manipulation 568

Converting Text to Columns 568

Manipulating Text with Functions 569

Using Flash Fill 571

Hands-On Exercise 2:

Text Manipulation 572

XML 575

Understanding Xml Syntax 575

Importing Xml Data into Excel 576

Hands-On Exercise 3:

Xml 579

Chapter Objectives Review         582

Key Terms Matching         583

Multiple Choice 584

Practice Exercises         585

Mid-Level Exercises         588

Beyond the Classroom         590

Capstone Exercise 591

 

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

Objectives 593

Case Study: Marching Band Senior Dinner 593

Customization and Personalization 594

Customizing Excel 594

Changing Properties 597

Hands-On Exercise 1:

Customization and Personalization 600

Collaboration 603

Sharing and Merging Workbooks 603

Inserting Comments 606

Tracking Changes 608

Hands-On Exercise 2:

Collaboration 612

Workbook Information 617

Checking for Issues 617

Protecting a Workbook 619

Hands-On Exercise 3:

Workbook Information 623

Workbook Distribution 626

Saving a Workbook in Different Formats 626

Sending a Workbook to Others 628

Hands-On Exercise 4:

Workbook Distribution 631

Chapter Objectives Review         633

Key Terms Matching 634

Multiple Choice 635

Practice Exercises 636

Mid-Level Exercises 639

Beyond the Classroom         641

Capstone Exercise 642

 

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

Objectives 643

Case Study: Staff Accounting Services 643

Templates, Themes, and Styles 644

Selecting a Template 644

Applying Themes and Backgrounds 645

Applying Cell Styles 647

Hands-On Exercise 1:

Templates, Themes, and Styles 650

Custom Templates and Workbook Protection 653

Creating and Using a Template 653

Protecting a Cell, a Worksheet, and a Workbook 654

Hands-On Exercise 2:

Custom Templates and Workbook Protection 658

Macros 663

Creating a Macro 663

Creating Macro Buttons 666

Setting Macro Security 667

Hands-On Exercise 3:

Macros 669

Visual Basic for Applications 673

Creating a Sub Procedure 673

Creating a Custom Function 675

Hands-On Exercise 4:

Visual Basic for Applications 677

Chapter Objectives Review         680

Key Terms Matching 681

Multiple Choice 682

Practice Exercises 683

Mid-Level Exercises 688

Beyond the Classroom         690

Capstone Exercise 691

 

Application Capstone Exercises

¿ Introductory (Chapters 1—4) 693

¿ Comprehensive (Chapters 5—12) 695

Glossary 699

Index 707