Your personal, hands-on guide to the latest and most useful features in Microsoft Excel 365

Excel 365 is Microsofts latest cloud-based version of its world-famous spreadsheet app. Powerful and user-friendly, its an ideal solution for businesses and people looking to make sense ofand draw intelligence fromtheir data.

TheExcel 365 Bible carries over the best content from the best-sellingExcel 2019 Bible while reflecting how a new generation uses Excel in Excel 365. The authoring team with their decades of Excel and business intelligence experience and recognition from the Excel community as Excel MVPs delivers an accessible and authoritative roadmap to Excel 365. Interested in the basics? Youll learn to create spreadsheets and workbooks and navigate the user interface. If youre ready for more advanced topics you can skip right to the material on creating visualizations, crafting custom functions, and using Visual Basic for Applications to script automations.

Youll also get:

Perfect for anyone new to Excel, as well as experts and advanced users, theExcel 365 Bible is your comprehensive, go-to guide for everything you need to know about the worlds most popular, easy-to-use spreadsheet software.

Michael Alexander is a senior consultant at Slalom Consulting with more than 15 years experience in data management and reporting. He is the author of more than a dozen books on business analysis using Microsoft Excel, and has been named Microsoft Excel MVP for his contributions to the Excel community.

Dick Kusleika has been working with Microsoft Office for more than 20 years. He was formerly a Microsoft MVP, having been awarded 12 consecutive years. Dick has written several books about Excel and Access.

Introduction xxxix

Part I: Getting Started with Excel 1

Chapter 1: Introducing Excel 3

Understanding What Excel Is Used For 3

Understanding Workbooks and Worksheets 4

Moving around a Worksheet 4

Using the Ribbon 9

Using Shortcut Menus 15

Customizing Your Quick Access Toolbar 16

Working with Dialog Boxes 18

Using Task Panes 20

Creating Your First Excel Workbook 20

Chapter 2: Entering and Editing Worksheet Data 27

Exploring Data Types 27

Entering Text and Values into Your Worksheets 30

Entering Dates and Times into Your Worksheets 31

Modifying Cell Contents 32

Applying Number Formatting 42

Using Excel on a Tablet 47

Chapter 3: Performing Basic Worksheet Operations 53

Learning the Fundamentals of Excel Worksheets 53

Controlling the Worksheet View 60

Working with Rows and Columns 66

Chapter 4: Working with Excel Ranges and Tables 73

Understanding Cells and Ranges 73

Copying or Moving Ranges 83

Using Names to Work with Ranges 95

Adding Comments to Cells 100

Adding Notes to Cells 104

Working with Tables 108

Chapter 5: Formatting Worksheets 121

Getting to Know the Formatting Tools 121

Formatting Your Worksheet 124

Using Conditional Formatting 135

Using Named Styles for Easier Formatting 146

Understanding Document Themes 150

Chapter 6: Understanding Excel Files and Templates 157

Creating a New Workbook 157

Opening an Existing Workbook 158

Saving a Workbook 161

Using AutoRecover 163

Password-Protecting a Workbook 165

Organizing Your Files 166

Other Workbook Info Options 166

Closing Workbooks 168

Safeguarding Your Work 168

Working with Templates 169

Chapter 7: Printing Your Work 177

Doing Basic Printing 177

Changing Your Page View 179

Adjusting Common Page Setup Settings 183

Adding a Header or a Footer to Your Reports 192

Exploring Other Print-Related Topics 194

Chapter 8: Customizing the Excel User Interface 199

Customizing the Quick Access Toolbar 199

Customizing the Ribbon 204

Part II: Working with Formulas and Functions 209

Chapter 9: Introducing Formulas and Functions 211

Understanding Formula Basics 211

Entering Formulas into Your Worksheets 218

Editing Formulas 224

Using Cell References in Formulas 225

Introducing Formula Variables 229

Using Formulas in Tables 232

Correcting Common Formula Errors 237

Using Advanced Naming Techniques 239

Working with Formulas 244

Chapter 10: Understanding and Using Array Formulas 247

Understanding Legacy Array Formulas 248

Introducing Dynamic Arrays 250

Exploring Dynamic Array Functions 255

Chapter 11: Using Formulas for Common Mathematical Operations 271

Calculating Percentages 271

Rounding Numbers 278

Counting Values in a Range 282

Using Excels Conversion Functions 283

Chapter 12: Using Formulas to Manipulate Text 285

Working with Text 285

Using Text Functions 286

Chapter 13: Using Formulas with Dates and Times 301

Understanding How Excel Handles Dates and Times 301

Using Excels Date and Time Functions 307

Chapter 14: Using Formulas for Conditional Analysis 329

Understanding Conditional Analysis 329

Performing Conditional Calculations 336

Chapter 15: Using Formulas for Matching and Lookups 347

Introducing Lookup Formulas 347

Leveraging Excels Lookup Functions 348

Chapter 16: Using Formulas with Tables and Conditional Formatting 365

Highlighting Cells That Meet Certain Criteria 365

Highlighting Values That Exist in List1 but Not List2 369

Highlighting Values That Exist in List1 and List2 371

Highlighting Based on Dates 372

Chapter 17: Making Your Formulas Error-Free 379

Finding and Correcting Formula Errors 379

Using Excel Auditing Tools 388

Searching and Replacing 393

Using AutoCorrect 396

Part III: Creating Charts and Other Visualizations 399

Chapter 18: Getting Started with Excel Charts 401

What Is a Chart? 401

Basic Steps for Creating a Chart 408

Modifying and Customizing Charts 414

Understanding Chart Types 419

Newer Chart Types for Excel 431

Chapter 19: Using Advanced Charting Techniques 441

Selecting Chart Elements 441

Exploring the User Interface Choices for Modifying Chart Elements 444

Modifying the Chart Area 447

Modifying the Plot Area 448

Working with Titles in a Chart 449

Working with a Legend 450

Working with Gridlines 452

Modifying the Axes 452

Working with Data Series 460

Creating Chart Templates 473

Chapter 20: Creating Sparkline Graphics 475

Sparkline Types 475

Creating Sparklines 477

Customizing Sparklines 480

Specifying a Date Axis 484

Auto-Updating Sparklines 486

Displaying a Sparkline for a Dynamic Range 486

Chapter 21: Visualizing with Custom Number Formats and Shapes 489

Visualizing with Number Formatting 489

Using Shapes and Icons as Visual Elements 502

Using SmartArt and WordArt 513

Working with Other Graphics Types 515

Using the Equation Editor 516

Part IV: Managing and Analyzing Data 519

Chapter 22: Importing and Cleaning Data 521

Importing Data 521

Cleaning Up Data 529

Exporting Data 550

Chapter 23: Using Data Validation 553

About Data Validation 553

Specifying Validation Criteria 554

Types of Validation Criteria You Can Apply 555

Creating a Drop-Down List 557

Using Formulas for Data Validation Rules 558

Understanding Cell References 559

Data Validation Formula Examples 560

Using Data Validation without Restricting Entry 564

Chapter 24: Creating and Using Worksheet Outlines 567

Introducing Worksheet Outlines 567

Creating an Outline 570

Working with Outlines 574

Chapter 25: Linking and Consolidating Worksheets 577

Linking Workbooks 577

Creating External Reference Formulas 578

Working with External Reference Formulas 580

Avoiding Potential Problems with External Reference Formulas 583

Consolidating Worksheets 585

Chapter 26: Introducing PivotTables 595

About PivotTables 595

Creating a PivotTable Automatically 600

Creating a PivotTable Manually 602

Seeing More PivotTable Examples 611

Learning More 616

Chapter 27: Analyzing Data with PivotTables 617

Working with Non-Numeric Data 617

Grouping PivotTable Items 619

Using a PivotTable to Create a Frequency Distribution 626

Creating a Calculated Field or Calculated Item 628

Filtering PivotTables with Slicers 635

Filtering PivotTables with a Timeline 637

Referencing Cells within a PivotTable 638

Creating PivotCharts 640

Using the Data Model 644

Chapter 28: Performing Spreadsheet What-If Analysis 651

Looking at a What-If Example 651

Exploring Types of What-If Analyses 653

Analyzing Data with Artificial Intelligence 668

Chapter 29: Analyzing Data Using Goal Seeking and Solver 675

Exploring What-If Analysis, in Reverse 675

Using Single-Cell Goal Seeking 675

Introducing Solver 678

Seeing Some Solver Examples 686

Chapter 30: Analyzing Data with the Analysis ToolPak 697

The Analysis ToolPak: An Overview 697

Installing the Analysis ToolPak Add-In 698

Using the Analysis Tools 698

Introducing the Analysis ToolPak Tools 699

Chapter 31: Protecting Your Work 709

Types of Protection 709

Protecting a Worksheet 710

Protecting a Workbook 714

Protecting a VBA Project 716

Related Topics 717

Part V: Understanding Power Pivot and Power Query 721

Chapter 32: Introducing Power Pivot 723

Understanding the Power Pivot Internal Data Model 723

Loading Data from Other Data Sources 733

Chapter 33: Working Directly with the Internal Data Model 747

Directly Feeding the Internal Data Model 747

Managing Relationships in the Internal Data Model 754

Managing Queries& Connections 755

Chapter 34: Adding Formulas to Power Pivot 757

Enhancing Power Pivot Data with Calculated Columns 757

Utilizing DAX to Create Calculated Columns 762

Understanding Calculated Measures 770

Using Cube Functions to Free Your Data 773

Chapter 35: Introducing Power Query 777

Understanding Power Query Basics 777

Getting Data from External Sources 792

Getting Data from Other Data Systems 797

Managing Data Source Settings 798

Data Profiling with Power Query 800

Chapter 36: Transforming Data with Power Query 805

Performing Common Transformation Tasks 805

Creating Custom Columns 823

Grouping and Aggregating Data 830

Working with Custom Data Types 832

Chapter 37: Making Queries Work Together 837

Reusing Query Steps 837

Understanding the Append Feature 841

Understanding the Merge Feature 845

Chapter 38: Enhancing Power Query Productivity 855

Implementing Some Power Query Productivity Tips 855

Avoiding Power Query Performance Issues 862

Part VI: Automating Excel 867

Chapter 39: Introducing Visual Basic for Applications 869

Introducing VBA Macros 869

Displaying the Developer Tab 870

Learning about Macro Security 871

Saving Workbooks That Contain Macros 872

Looking at Two Types of VBA Macros 873

Creating VBA Macros 876

Learning More 896

Chapter 40: Creating Custom Worksheet Functions 899

Introducing VBA Functions 899

Seeing a Simple Example 900

Learning about Function Procedures 902

Executing Function Procedures 904

Using Function Procedure Arguments 905

Debugging Custom Functions 910

Inserting Custom Functions 910

Learning More 912

Chapter 41: Creating UserForms 913

Understanding Why to Create UserForms 913

Exploring UserForm Alternatives 914

Creating UserForms: An Overview 918

Looking at a UserForm Example 923

Looking at Another UserForm Example 926

Enhancing UserForms 932

Learning More 933

Chapter 42: Using UserForm Controls in a Worksheet 935

Understanding Why to Use Controls on a Worksheet 935

Using Controls 938

Reviewing the Available ActiveX Controls 942

Chapter 43: Working with Excel Events 949

Understanding Events 949

Entering Event-Handler VBA Code 950

Using Workbook-Level Events 951

Working with Worksheet Events 955

Using Special Application Events 959

Chapter 44: Seeing Some VBA Examples 963

Working with Ranges 963

Working with Workbooks 972

Working with Charts 973

VBA Speed Tips 975

Chapter 45: Creating Custom Excel Add-Ins 979

Understanding Add-Ins 979

Working with Add-Ins 980

Understanding When to Create Add-Ins 982

Creating Add-Ins 982

Looking at an Add-In Example 983

Index 989