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
