Table of Contents
About the Author v
Dedication v
Acknowledgements v
Introduction vi
Who This Book Is For vi
What This Book Covers vii
Typographical Conventions and Special Elements ix
Chapter 1 Back to Basics: What Do You Know Already? 1
Data Entry Tips and Tricks 1
Dropping Your Dread of Formulas 6
Getting to Know the Common Excel Functions 8
Understanding the Copying Functions 12
Rolling in the Worksheets 14
Printing in Excel 18
Charts, Charts, Charts 26
Meet the IFSSSSSS family 40
Co-authoring - Excel will play with more than one person at a time! 42
Hooray for Dynamic Arrays! 44
Is it a note? is it a comment? Er, no, it's an Understanding AKA Threaded Comments 45
Keyboard Navigation 47
Summary 48
Chapter 2 Getting Your Data Together: Catching Your File 49
Creating a Spreadsheet List from Scratch 50
Getting Data from a Data Dump 64
Sorting and Filtering 71
No More CSV Nagging 75
Summary 76
Chapter 3 Further Cleaning, Slicing, and Dicing 77
Removing/Completing Blank Columns/Rows/Cells 77
Cleaning, Combining, and Amending Existing Data 80
Extracting Specific Pieces of Data from a Cell to Refine a Data Set 87
Using the Iferror() Function 98
Summary 99
Chapter 4 The Vlookup() Function: An Excel Essential 100
Understanding the Vlookup() Function Syntax 100
Troubleshooting Vlookup(): Dealing with #N/A Errors 102
Understanding When to Use Vlookup() 104
How to Solve Common Vlookup() Problems 109
Getting to Know Vlookup()'s new sibling - Xlookup() 118
Summary 120
Chapter 5 Creating Pivot Tables 121
Assembling Data for a Pivot Table 121
Creating Your First Pivot Table 122
Understanding Summarize Values By 126
Tidying Up the Numbers in a Pivot Table 128
Refreshing Data in a Pivot Table 129
Grouping by Dates 130
Grouping by Amounts 131
Showing Values As 132
Using Filters 139
Setting Up Slicers (but Only if You Have Excel 2010 or Later) 140
Changing Pivot Table Layout 142
Adding a Chart 146
Adding Conditional Formatting 149
Adding to the Data Model 150
Setting Your Pivot Table Defaults 158
Troubleshooting Pivot Tables 159
Summary 161
Chapter 6 Using Power Query to Quickly Clean Up Data 162
Cleaning Up an Accounting Data Dump 163
Converting Unpivoted Data to a Pivoted Format 171
Creating a Query in Power Query to Merge Data Sets 174
Cleaning and Extracting Data 178
Splitting Data Entries onto Separate Columns Rows 193
Using the Column from Examples Feature to Transform Data 195
Summary 197
Chapter 7 Beyond the Pivot Table: Power Pivot 198
Installing and Locating Power Pivot 198
Before You Start Using Power Pivot 200
Getting Your Data into Power Pivot 201
Linking the Data Sets Together 203
Creating a Pivot Table from the Combined Data 205
Recommended Resources 215
Summary 215
Chapter 8 Meet Power B1, Your New Superpower 216
Downloading and Getting to Know Power BI 216
Case Study: What the Customer Wants 218
Summary 242
Appendix Data Validation Techniques 244
Restricting Number Sizes 244
Restricting Date Entries 246
Restricting Text Length 247
Extending Data Validation 247
Applying Data Validation to Another Part of a Sheet 247
Identifying Duplicate Entries in a List 248
Simple Normalization (Getting Crossways Data to Go Lengthwise or Vice Versa) 248
Index 250
