Exploring Microsoft Excel 2016 : comprehensive

Description

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