Excel 2019 All-in-One

by Harvey, Greg, Ph.d.

• ISBN13: 9781119517948
• ISBN10: 111951794X
• Format: Paperback
• Copyright: 2018-11-13
• Publisher: For Dummies

Summary

Make Excel work for you 

Excel 2019 All-In-One For Dummies offers eight books in one!! It is completely updated to reflect the major changes Microsoft is making to Office with the 2019 release. From basic Excel functions, such as creating and editing worksheets, to sharing and reviewing worksheets, to editing macros with Visual Basic, it provides you with a broad scope of the most common Excel applications and functions—including formatting worksheets, setting up formulas, protecting worksheets, importing data, charting data, and performing statistical functions.
   
The book covers importing data, building and editing worksheets, creating formulas, generating pivot tables, and performing financial functions, what-if scenarios, database functions, and Web queries. More advanced topics include worksheet sharing and auditing, performing error trapping, building and running macros, charting data, and using Excel in conjunction with Microsoft Power BI (Business Intelligence) to analyze, model, and visualize vast quantities of data from a variety of local and online sources.  

• Get familiar with Worksheet design
• Find out how to work with charts and graphics
• Use Excel for data management, analysis, modeling, and visualization
• Make sense of macros and VBA

If you’re a new or inexperienced user looking to spend more time on your projects than trying to figure out how to make Excel work for you, this all-encompassing book makes it easy!

Author Biography

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.

Table of Contents

Introduction 1

About This Book 2

Foolish Assumptions 2

How This Book Is Organized 3

Conventions Used in This Book 7

Icons Used in This Book 9

Beyond the Book 9

Where to Go from Here 10

Book 1: Excel Basics 11

Chapter 1: The Excel 2019 User Experience 13

Excel 2019’s Sleek Look and Feel 14

Excel’s Start Screen 15

Excel’s Ribbon User Interface 16

Getting Help 36

Launching and Quitting Excel 39

Chapter 2: Customizing Excel 2019 45

Tailoring the Quick Access Toolbar to Your Tastes 46

Exercising Your Options 49

Using Office Add-ins 72

Using Excel’s Own Add-ins 74

Book 2: Worksheet Design 79

Chapter 1: Building Worksheets 81

Designer Spreadsheets 82

It Takes All Kinds (Of Cell Entries) 92

Data Entry 101 99

Saving the Data 124

Document Recovery to the Rescue 130

Chapter 2: Formatting Worksheets 133

Making Cell Selections 134

Adjusting Columns and Rows 143

Formatting Tables from the Ribbon 148

Formatting Tables with the Quick Analysis Tool 152

Formatting Cells from the Ribbon 153

Formatting Cell Ranges with the Mini-Toolbar 156

Using the Format Cells Dialog Box 158

Hiring Out the Format Painter 177

Using Cell Styles 178

Conditional Formatting 183

Chapter 3: Editing and Proofing Worksheets 193

Opening a Workbook 194

Cell Editing 101 201

A Spreadsheet with a View 209

Copying and Moving Stuff Around 216

Find and Replace This Disgrace! 227

Spell Checking Heaven 232

Looking Up and Translating Stuff 236

Marking Invalid Data 237

Eliminating Errors with Text to Speech 238

Chapter 4: Managing Worksheets 241

Reorganizing the Worksheet 242

Reorganizing the Workbook 257

Working with Multiple Workbooks 267

Consolidating Worksheets 270

Chapter 5: Printing Worksheets 277

Printing from the Excel 2019 Backstage View 278

Quick Printing the Worksheet 284

Working with the Page Setup Options 285

Using the Print Options on the Sheet tab of the Page Setup dialog box 293

Headers and Footers 294

Solving Page Break Problems 301

Printing the Formulas in a Report 303

Book 3: Formulas and Functions 305

Chapter 1: Building Basic Formulas 307

Formulas 101 308

Copying Formulas 321

Adding Array Formulas 327

Range Names in Formulas 332

Adding Linking Formulas 341

Controlling Formula Recalculation 343

Circular References 345

Chapter 2: Logical Functions and Error Trapping 347

Understanding Error Values 348

Using Logical Functions 349

Error-Trapping Formulas 353

Whiting-Out Errors with Conditional Formatting 356

Formula Auditing 358

Removing Errors from the Printout 370

Chapter 3: Date and Time Formulas 371

Understanding Dates and Times 371

Using Date Functions 374

Using Time Functions 383

Chapter 4: Financial Formulas 387

Financial Functions 101 387

The PV, NPV, and FV Functions 388

The PMT Function 392

Depreciation Functions 395

Analysis ToolPak Financial Functions 398

Chapter 5: Math and Statistical Formulas 401

Math & Trig Functions 402

Statistical Functions 412

Chapter 6: Lookup, Information, and Text Formulas 421

Lookup and Reference 422

Information, Please 433

Much Ado about Text 438

Book 4: Worksheet Collaboration and Review 445

Chapter 1: Protecting Workbooks and Worksheet Data 447

Password-Protecting the File 448

Protecting the worksheet 456

Chapter 2: Using Hyperlinks 469

Hyperlinks 101 469

Using the HYPERLINK Function 475

Chapter 3: Preparing a Workbook for Distribution 477

Getting Your Workbook Ready for Review 478

Annotating Workbooks 484

Chapter 4: Sharing Workbooks and Worksheet Data 491

Sharing Your Workbooks Online 492

Excel 2019 Data Sharing Basics 503

Exporting Workbooks to Other Usable File Formats 511

Book 5: Charts and Graphics 517

Chapter 1: Charting Worksheet Data 519

Worksheet Charting 101 520

Chapter 2: Adding Graphic Objects 553

Graphic Objects 101 554

Inserting Different Types of Graphics 562

Drawing Graphics 569

Adding Screenshots of the Windows 10 Desktop 580

Using Themes 581

Book 6: Data Management 583

Chapter 1: Building and Maintaining Data Lists 585

Data List Basics 586

Designing the basic data list 586

Add new records to a data list 589

Eliminating records with duplicate fields 595

Sorting Data 596

Sorting records on a single field 597

Sorting records on multiple fields 597

Sorting the columns of a data list 601

Sorting a data list on font and fill colors and cell icons 604

Subtotaling Data 605

Chapter 2: Filtering and Querying a Data List 609

Data List Filtering 101 610

Filtering Data 611

Using the Database Functions 628

External Data Query 631

Book 7: Data Analysis 647

Chapter 1: Performing What-If Scenarios 649

Using Data Tables 650

Exploring Different Scenarios 656

Hide and Goal Seeking 662

Using the Solver 664

Chapter 2: Performing Large-Scale Data Analysis 673

Creating Pivot Tables 674

Formatting a Pivot Table 683

Sorting and Filtering the Pivot Table Data 688

Modifying the Pivot Table 693

Creating Pivot Charts 699

Using the Power Pivot Add-in 702

Using the 3D Map feature 709

Creating Forecast Worksheets 714

Book 8: Macros and VBA 719

Chapter 1: Recording and Running Macros 721

Macro Basics 722

Assigning Macros to the Ribbon and the Quick Access Toolbar 728

Macro Security 730

Chapter 2: VBA Programming 733

Using the Visual Basic Editor 734

Creating Custom Excel Functions 747

Index 757