Exploring Microsoft Excel 2016 : comprehensive

Cover......Page 1
Title Page......Page 2
Copyright Page......Page 3
Dedications......Page 4
About the Authors......Page 5
Brief Contents......Page 7
Contents......Page 8
Acknowledgments......Page 13
Preface......Page 17
Key Features......Page 18
Resources......Page 19
Microsoft Office 2016......Page 20
Case Study Spotted Begonia Art Gallery......Page 21
Starting an Office Application......Page 23
Working with Files......Page 24
Using Common Interface Components......Page 27
Getting Help......Page 34
Installing Add-ins......Page 36
Hands-On Exercises 1 Getting Started with Office Applications......Page 38
Using Templates and Applying Themes......Page 43
Modifying Text......Page 45
Relocating Text......Page 49
Checking Spelling and Grammar......Page 51
Working with Pictures and Graphics......Page 53
Hands-On Exercises 2 Format Document Content......Page 56
Using Backstage View......Page 61
Changing the Document View......Page 63
Changing the Page Layout......Page 64
Inserting a Header and Footer......Page 67
Previewing and Printing a File......Page 68
Hands-On Exercises 3 Modify Document Layout and Properties......Page 70
Chapter Objectives Review......Page 75
Key Terms Matching......Page 77
Multiple Choice......Page 78
Practice Exercises......Page 79
Mid-Level Exercises......Page 83
Beyond the Classroom......Page 85
Capstone Exercise......Page 86
Case Study Ok Office Systems......Page 87
Exploring the Excel Window......Page 89
Entering and Editing Cell Data......Page 92
Hands-On Exercises 1 Introduction to Spreadsheets......Page 98
Creating Formulas......Page 102
Displaying Cell Formulas......Page 105
Hands-On Exercises 2 Mathematical Operations and Formulas......Page 107
Managing Columns and Rows......Page 112
Selecting, Moving, Copying, and Pasting Data......Page 117
Hands-On Exercises 3 Worksheet Structure and Clipboard Tasks......Page 123
Applying Cell Styles, Alignment, and Font Options......Page 129
Applying Number Formats......Page 132
Hands-On Exercises 4 Worksheet Formatting......Page 135
Managing Worksheets......Page 140
Selecting Page Setup Options......Page 142
Previewing and Printing a Worksheet......Page 148
Hands-On Exercises 5 Worksheets, Page Setup, and Printing......Page 150
Chapter Objectives Review......Page 154
Key Terms Matching......Page 156
Multiple Choice......Page 157
Practice Exercises......Page 158
Mid-Level Exercises......Page 164
Beyond the Classroom......Page 167
Capstone Exercise......Page 168
Case Study Townsend Mortgage Company......Page 171
Using Relative, Absolute, and Mixed Cell References in Formulas......Page 173
Hands-On Exercises 1 Formula Basics......Page 177
Inserting a Function......Page 180
Inserting Basic Math and Statistics Functions......Page 182
Using Date Functions......Page 186
Hands-On Exercises 2 Function Basics......Page 188
Determining Results with the IF Function......Page 193
Using Lookup Functions......Page 196
Calculating Payments with the PMT Function......Page 199
Hands-On Exercises 3 Logical, Lookup, and Financial Functions......Page 201
Chapter Objectives Review......Page 206
Key Terms Matching......Page 207
Multiple Choice......Page 208
Practice Exercises......Page 209
Mid-Level Exercises......Page 212
Beyond the Classroom......Page 215
Capstone Exercise......Page 216
Case Study Computer Job Outlook......Page 217
Selecting the Data Source......Page 219
Choosing a Chart Type......Page 221
Moving, Sizing, and Printing a Chart......Page 233
Hands-On Exercises 1 Chart Basics......Page 237
Chart Elements......Page 243
Adding, Editing, and Formatting Chart Elements......Page 244
Hands-On Exercises 2 Chart Elements......Page 254
Applying a Chart Style and Colors......Page 259
Modifying the Data Source......Page 260
Creating and Customizing Sparklines......Page 262
Hands-On Exercises 3 Chart Design and Sparklines......Page 265
Chapter Objectives Review......Page 268
Key Terms Matching......Page 270
Multiple Choice......Page 271
Practice Exercises......Page 272
Mid-Level Exercises......Page 276
Beyond the Classroom......Page 279
Capstone Exercise......Page 280
Case Study Reid Furniture Store......Page 281
Large Datasets......Page 283
Freezing Rows and Columns......Page 284
Printing Large Datasets......Page 285
Hands-On Exercises 1 Large Datasets......Page 289
Designing and Creating Tables......Page 294
Applying a Table Style......Page 299
Hands-On Exercises 2 Excel Tables......Page 301
Creating Structured References in Formulas......Page 306
Sorting Data......Page 307
Filtering Data......Page 309
Hands-On Exercises 3 Table Manipulation......Page 314
Adding a Total Row......Page 321
Applying Conditional Formatting......Page 323
Creating a New Rule......Page 328
Hands-On Exercises 4 Table Aggregation and Conditional Formatting......Page 331
Chapter Objectives Review......Page 336
Key Terms Matching......Page 337
Multiple Choice......Page 338
Practice Exercises......Page 339
Mid-Level Exercises......Page 343
Beyond the Classroom......Page 345
Capstone Exercise......Page 346
Case Study IVORY HALLS PUBLISHING COMPANY......Page 347
Subtotaling Data......Page 349
Grouping and Ungrouping Data......Page 352
Hands-On Exercises 1 Subtotals and Outlines......Page 354
Creating a PivotTable......Page 358
Modifying a PivotTable......Page 362
Hands-On Exercises 2 PivotTable Basics......Page 369
Filtering and Slicing a PivotTable......Page 374
Creating a Calculated Field......Page 378
Changing the PivotTable Design......Page 381
Hands-On Exercises 3 PivotTable Options......Page 383
Creating a Data Model......Page 388
Creating a PivotChart......Page 391
Hands-On Exercises 4 Data Modeling and PivotCharts......Page 393
Chapter Objectives Review......Page 398
Key Terms Matching......Page 400
Multiple Choice......Page 401
Practice Exercises......Page 403
Mid-Level Exercises......Page 408
Beyond the Classroom......Page 411
Capstone Exercise......Page 412
Case Study PERSONAL FINANCE: BUYING YOUR FIRST HOME......Page 415
Creating and Maintaining Range Names......Page 417
Hands-On Exercises 1 Range Names......Page 421
Creating a One-Variable Data Table......Page 425
Creating a Two-Variable Data Table......Page 429
Hands-On Exercises 2 One- and Two-Variable Data Tables......Page 432
Determining Optimal Input Values Using Goal Seek......Page 437
Using Scenario Manager......Page 438
Hands-On Exercises 3 Goal Seek and Scenario Manager......Page 443
Loading the Solver Add-In......Page 447
Optimizing Results with Solver......Page 448
Hands-On Exercises 4 Solver......Page 454
Chapter Objectives Review......Page 459
Key Terms Matching......Page 460
Multiple Choice......Page 461
Practice Exercises......Page 462
Mid-Level Exercises......Page 466
Beyond the Classroom......Page 469
Capstone Exercise......Page 470
Case Study TRANSPAYNE FILTRATION......Page 473
Using Date Functions......Page 475
Creating a Nested Logical Function......Page 476
Using Advanced Lookup Functions......Page 481
Hands-On Exercises 1 Date, Logical, and Lookup Functions......Page 485
Applying Advanced Filtering......Page 490
Manipulating Data with Database Functions......Page 494
Hands-On Exercises 2 Database Filtering and Functions......Page 497
Using Financial Functions......Page 501
Creating a Loan Amortization Table......Page 503
Hands-On Exercises 3 Financial Functions......Page 507
Chapter Objectives Review......Page 512
Key Terms Matching......Page 513
Multiple Choice......Page 514
Practice Exercises......Page 515
Mid-Level Exercises......Page 521
Beyond the Classroom......Page 525
Capstone Exercise......Page 526
Case Study EDUCATION EVALUATION......Page 529
Using Conditional Math and Statistical Functions......Page 531
Calculating Relative Standing with Statistical Functions......Page 535
Hands-On Exercises 1 Math and Statistical Functions......Page 539
Measuring Central Tendency......Page 544
Hands-On Exercises 2 Descriptive Statistical Functions......Page 550
Loading the Analysis ToolPak......Page 553
Performing Analysis Using the Analysis ToolPak......Page 554
Creating a Forecast Sheet......Page 557
Hands-On Exercises 3 Inferential Statistics......Page 559
CHAPTER OBJECTIVES REVIEW......Page 564
Key Terms Matching......Page 565
Multiple Choice......Page 566
Practice Exercises......Page 567
Mid-Level Exercises......Page 571
Beyond the Classroom......Page 574
Capstone Exercise......Page 575
Case Study CIRCLE CITY SPORTING GOODS......Page 577
Working with Grouped Worksheets......Page 579
Inserting Hyperlinks......Page 582
Managing Windows......Page 584
Hands-On Exercises 1 Multiple Worksheets......Page 588
Inserting Formulas and Functions with 3-D References......Page 593
Linking Workbooks......Page 596
Hands-On Exercises 2 3-D Formulas and Linked Workbooks......Page 600
Formula Audits and Data Validation......Page 604
Auditing Formulas......Page 605
Setting Up a Watch Window......Page 608
Validating Data......Page 609
Hands-On Exercises 3 Formula Audits and Data Validation......Page 613
Chapter Objectives Review......Page 619
Key Terms Matching......Page 621
Multiple Choice......Page 622
Practice Exercises......Page 623
Mid-Level Exercises......Page 628
Beyond the Classroom......Page 631
Capstone Exercise......Page 632
Case Study STOCK ANALYSIS......Page 635
Importing Data from External Sources......Page 637
Managing Connections......Page 643
Hands-On Exercises 1 External Data......Page 646
Converting Text to Columns......Page 649
Manipulating Text with Functions......Page 650
Using Flash Fill......Page 652
Hands-On Exercises 2 Text Manipulation......Page 653
Understanding XML Syntax......Page 657
Importing XML Data into Excel......Page 659
Hands-On Exercises 3 XML......Page 661
Using Power Pivot Functionality......Page 663
Importing Data with Power Query......Page 665
Visualizing Data with Power View......Page 667
Hands-On Exercises 4 Power Add-Ins......Page 669
Chapter Objectives Review......Page 675
Key Terms Matching......Page 676
Multiple Choice......Page 677
Practice Exercises......Page 678
Mid-Level Exercises......Page 683
Beyond the Classroom......Page 685
Capstone Exercise......Page 686
Case Study MARCHING BAND SENIOR DINNER......Page 689
Customizing Excel Options......Page 691
Changing Properties......Page 696
Hands-On Exercises 1 Workbook Customization......Page 699
Inserting Comments......Page 702
Sharing and Merging Workbooks......Page 705
Tracking Changes......Page 709
Hands-On Exercises 2 Collaboration......Page 713
Checking for Issues......Page 720
Protecting a Workbook......Page 724
Hands-On Exercises 3 Workbook Information......Page 728
Saving a Workbook in Different Formats......Page 732
Sending a Workbook to Others......Page 734
Hands-On Exercises 4 Workbook Distribution......Page 738
Chapter Objectives Review......Page 741
Key Terms Matching......Page 743
Multiple Choice......Page 744
Practice Exercises......Page 745
Mid-Level Exercises......Page 749
Beyond the Classroom......Page 752
Capstone Exercise......Page 753
Case Study STAFF ACCOUNTING SERVICES......Page 755
Selecting a Template......Page 757
Applying Themes and Backgrounds......Page 758
Applying Cell Styles......Page 760
Hands-On Exercises 1 Templates, Themes, and Styles......Page 764
Creating and Using a Template......Page 768
Protecting a Cell, a Worksheet, and a Workbook......Page 769
Hands-On Exercises 2 Custom Templates and Workbook Protection......Page 774
Creating a Macro......Page 780
Creating Macro Buttons......Page 783
Setting Macro Security......Page 785
Hands-On Exercises 3 Macros......Page 787
Creating a Sub Procedure......Page 792
Creating a Custom Function......Page 795
Hands-On Exercises 4 Visual Basic for Applications......Page 797
Chapter Objectives Review......Page 800
Key Terms Matching......Page 801
Multiple Choice......Page 802
Practice Exercises......Page 803
Mid-Level Exercises......Page 807
Beyond the Classroom......Page 809
Capstone Exercise......Page 810
Excel Introductory Capstone Exercise (Chs. 1–4)......Page 811
Excel Comprehensive Capstone Exercise (Chs. 5–12)......Page 814
Microsoft Office 2016 Specialist Excel Core......Page 818
Glossary......Page 826
C......Page 836
E......Page 838
F......Page 839
M......Page 840
P......Page 841
S......Page 842
T......Page 843
W......Page 844
Z......Page 845