Microsoft Excel 365 Bible

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 Excel’s 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 Excel’s 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 Excel’s 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