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