Excel 2019 All-in-One For Dummies

Greg Harvey, PhD, is a veteran computer educator dating back to the days of DOS and Lotus 1-2-3. He has taught spreadsheet and database management courses at Golden Gate University and written dozens of books, including many in the For Dummies series.

• Introduction 1About This Book 2Foolish Assumptions 2How This Book Is Organized 3Conventions Used in This Book 7Icons Used in This Book 9Beyond the Book 9Where to Go from Here 10Book 1: Excel Basics 11Chapter 1: The Excel 2019 User Experience 13Excel 2019’s Sleek Look and Feel 14Excel’s Start Screen 15Excel’s Ribbon User Interface 16Getting Help 36Launching and Quitting Excel 39Chapter 2: Customizing Excel 2019 45Tailoring the Quick Access Toolbar to Your Tastes 46Exercising Your Options 49Using Office Add-ins 72Using Excel’s Own Add-ins 74Book 2: Worksheet Design 79Chapter 1: Building Worksheets 81Designer Spreadsheets 82It Takes All Kinds (Of Cell Entries) 92Data Entry 101 99Saving the Data 124Document Recovery to the Rescue 130Chapter 2: Formatting Worksheets 133Making Cell Selections 134Adjusting Columns and Rows 143Formatting Tables from the Ribbon 148Formatting Tables with the Quick Analysis Tool 152Formatting Cells from the Ribbon 153Formatting Cell Ranges with the Mini-Toolbar 156Using the Format Cells Dialog Box 158Hiring Out the Format Painter 177Using Cell Styles 178Conditional Formatting 183Chapter 3: Editing and Proofing Worksheets 193Opening a Workbook 194Cell Editing 101 201A Spreadsheet with a View 209Copying and Moving Stuff Around 216Find and Replace This Disgrace! 227Spell Checking Heaven 232Looking Up and Translating Stuff 236Marking Invalid Data 237Eliminating Errors with Text to Speech 238Chapter 4: Managing Worksheets 241Reorganizing the Worksheet 242Reorganizing the Workbook 257Working with Multiple Workbooks 267Consolidating Worksheets 270Chapter 5: Printing Worksheets 277Printing from the Excel 2019 Backstage View 278Quick Printing the Worksheet 284Working with the Page Setup Options 285Using the Print Options on the Sheet tab of the Page Setup dialog box 293Headers and Footers 294Solving Page Break Problems 301Printing the Formulas in a Report 303Book 3: Formulas and Functions 305Chapter 1: Building Basic Formulas 307Formulas 101 308Copying Formulas 321Adding Array Formulas 327Range Names in Formulas 332Adding Linking Formulas 341Controlling Formula Recalculation 343Circular References 345Chapter 2: Logical Functions and Error Trapping 347Understanding Error Values 348Using Logical Functions 349Error-Trapping Formulas 353Whiting-Out Errors with Conditional Formatting 356Formula Auditing 358Removing Errors from the Printout 370Chapter 3: Date and Time Formulas 371Understanding Dates and Times 371Using Date Functions 374Using Time Functions 383Chapter 4: Financial Formulas 387Financial Functions 101 387The PV, NPV, and FV Functions 388The PMT Function 392Depreciation Functions 395Analysis ToolPak Financial Functions 398Chapter 5: Math and Statistical Formulas 401Math & Trig Functions 402Statistical Functions 412Chapter 6: Lookup, Information, and Text Formulas 421Lookup and Reference 422Information, Please 433Much Ado about Text 438Book 4: Worksheet Collaboration and Review 445Chapter 1: Protecting Workbooks and Worksheet Data 447Password-Protecting the File 448Protecting the worksheet 456Chapter 2: Using Hyperlinks 469Hyperlinks 101 469Using the HYPERLINK Function 475Chapter 3: Preparing a Workbook for Distribution 477Getting Your Workbook Ready for Review 478Annotating Workbooks 484Chapter 4: Sharing Workbooks and Worksheet Data 491Sharing Your Workbooks Online 492Excel 2019 Data Sharing Basics 503Exporting Workbooks to Other Usable File Formats 511Book 5: Charts and Graphics 517Chapter 1: Charting Worksheet Data 519Worksheet Charting 101 520Chapter 2: Adding Graphic Objects 553Graphic Objects 101 554Inserting Different Types of Graphics 562Drawing Graphics 569Adding Screenshots of the Windows 10 Desktop 580Using Themes 581Book 6: Data Management 583Chapter 1: Building and Maintaining Data Lists 585Data List Basics 586Designing the basic data list 586Add new records to a data list 589Eliminating records with duplicate fields 595Sorting Data 596Sorting records on a single field 597Sorting records on multiple fields 597Sorting the columns of a data list 601Sorting a data list on font and fill colors and cell icons 604Subtotaling Data 605Chapter 2: Filtering and Querying a Data List 609Data List Filtering 101 610Filtering Data 611Using the Database Functions 628External Data Query 631Book 7: Data Analysis 647Chapter 1: Performing What-If Scenarios 649Using Data Tables 650Exploring Different Scenarios 656Hide and Goal Seeking 662Using the Solver 664Chapter 2: Performing Large-Scale Data Analysis 673Creating Pivot Tables 674Formatting a Pivot Table 683Sorting and Filtering the Pivot Table Data 688Modifying the Pivot Table 693Creating Pivot Charts 699Using the Power Pivot Add-in 702Using the 3D Map feature 709Creating Forecast Worksheets 714Book 8: Macros and VBA 719Chapter 1: Recording and Running Macros 721Macro Basics 722Assigning Macros to the Ribbon and the Quick Access Toolbar 728Macro Security 730Chapter 2: VBA Programming 733Using the Visual Basic Editor 734Creating Custom Excel Functions 747Index 757