Table of Contents
Typographical Conventions and Special Elements
Chapter 1 - Back to Basics: What Do You Know Already?
Dropping Your Dread of Formulas
Getting to Know the Common Excel Functions
Understanding the Copying Functions
Chapter 2 Getting Your Data Together: Catching Your File
Creating a Spreadsheet List from Scratch
Chapter 3 Further Cleaning, Slicing, and Dicing
Removing/Completing Blank Columns/Rows/Cells
Cleaning, Combining, and Amending Existing Data
Extracting Specific Pieces of Data from a Cell to Refine a Data Set
Chapter 4 The Vlookup() Function: An Excel Essential
Understanding the Vlookup() Function Syntax
Troubleshooting Vlookup(): Dealing with #N/A Errors
Understanding When to Use Vlookup()
How to Solve Common Vlookup() Problems
Chapter 5 Creating Pivot Tables
Assembling Data for a Pivot Table
Creating Your First Pivot Table
Understanding Summarize Values By
Tidying Up the Numbers in a Pivot Table
Refreshing Data in a Pivot Table
Setting Up Slicers (but Only if You Have Excel 2010 or Later)
Chapter 6 Using Power Query to Quickly Clean Up Data
Cleaning Up an Accounting Data Dump
Converting Unpivoted Data to a Pivoted Format
Creating a Query in Power Query to Merge Data Sets
Chapter 7 Beyond the Pivot Table: Power Pivot
Installing and Locating Power Pivot
Before You Start Using Power Pivot
Getting Your Data into Power Pivot
Linking the Data Sets Together
Creating a Pivot Table from the Combined Data
Appendix Data Validation Techniques
Applying Data Validation to Another Part of a Sheet
Identifying Duplicate Entries in a List
Simple Normalization (Getting Crossways Data to Go Lengthwise or Vice Versa)
Anne Walsh has been training users in Excel since 1997. She saw her first spreadsheet in the early 1990s and has been curious and intrigued ever since. She has been an MCT (Microsoft Certified Trainer) and MOS Master Instructor since Office 97. She has delivered thousands of hours of training to individuals and businesses, helping users save thousands of hours with the tips, techniques, and shortcuts she shares in class. She likes to bring wit and humour to her classes; one user evaluation said, “I never thought I’d put Excel and fun in the same sentence.” She lives in the west of Ireland, with her very non-techie husband and very techie son.
For Bill “MrExcel” Jelen. This is my first Excel book, and I am thrilled and delighted to be the first Irish person on his books.
Thanks, of course, to Bill “MrExcel” Jelen, for giving me this opportunity. I also want to thank the “believing mirrors” who have been with me on this journey: Dolores Cummins, Sharon Gaskin (and all the Success Shapers groups I have been part of), Claire Commins, Bróna Clifford, and Karen Gorey.
I also want to thank Kitty Wilson for her work on the book. In the words of my beloved Kate Bush, “It’s nearly killing me, but what a lovely feeling” (from “Them Heavy People” on The Kick Inside).
Big thanks to Deborah Taylor of www.booklaunchyourbusiness.com. She was the person who advised and helped in the early stages of this book and helped keep me steady when self-doubt was shrieking in my ears.
Also a big thank you to Bróna Clifford, who read the book toward the end and proofread it when I couldn’t see it anymore.
I also want to give a mention to my earliest English teachers: Sr. Pius, who lent me books; Mrs. Fennell, who encouraged my imagination and creativity; and Sr. Mary, who taught me accounting and typing—both skills that have been extremely useful to me.
Finally, I offer my deepest thanks to the organisations that have employed me to work with them and for all the great questions I have received from learners over the years.