Excel 2019 All-in-One For Dummies

2018·23.33 MB·English

by  Greg Harvey

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