I remember the first time I saw a spreadsheet. It was orange. All of it. (At that time, computer screens showed text in orange or green.) It was a Lotus 1-2-3 sheet, and the various tabs were activated by pressing the / key. I remember feeling both fascinated and tentative as I experimented a bit with it. It was owned by one of the senior managers in the organisation where I was working, so access was rather restricted. That particular spreadsheet added up numbers and did a few other things. Compared to a manual calculator, it was pretty impressive. It was a few more years before I really got to grips with spreadsheets, but the memory of my first stayed with me. (Dare I say the “first row is the deepest”?)
Excel is now the spreadsheet of choice for most users, and that is what this book is about. When I look at Excel, I feel the same way I feel when I am in the presence of great architecture or a beautiful piece of art or music. Yes, of course it has flaws, but I feel inspired by the fierce love and intelligence that have gone into its creation. It is like a great work of art in that every time I return to it, I find something new, I see something new, I learn something new, and I’m not ashamed to say I love it.
I have been teaching Excel for well over 20 years (I’ve got my 10,000 hours done!), and I still feel joy when I see people’s faces light up as they grasp what a pivot table can do for them or when they realise that printing is much easier than they thought. After you have read this book, my hope is that you start to see Excel’s power and begin to really leverage it. I feel so privileged when I help people realize that Excel can be their best office friend.
The reality is that in most workplaces—even those with very sophisticated systems—data usually ends up in Excel. If you can get to grips with the concepts in this book, you will soon feel comfortable working with Excel.
This book is for you if you are being asked to use Excel more than you ever expected and you are looking for a guide to take you to what is relevant and most frequently used. It’s also for you if you have seen that in your organization, it’s the Excel power users who get promoted, and you want to join them.
This book is also for you if you have done any of these things:
- Spent hours or even days trying to produce an analysis that can be done in minutes with a pivot table
- Cleaned up data by clicking on each individual cell (perhaps thousands of times) and pressing Backspace rather than using Trim() to do them all in one go
- Set up repeating data in separate sheets instead of all together in one and then gone through Hell trying to put it all together at the end of the year
- Called on tape, glue, scissors, and a stapler to get an Excel sheet printed on one page instead of on multiple pages
- Manually pulled in matching entries from separate workbooks instead of using the Vlookup() function
- Found yourself in a job where your boss is talking about pivot tables and Vlookup()s, and you have no idea what he/she is talking about—but you know you have to learn fast, and you are terrified
Let me give you a virtual hug if you’ve been through any of this. And let me reassure you: The madness stops here! It doesn’t have to be like this! Excel is on your side. This book is going to get you through. It is, after all, your Excel Survival Kit. One of the key things you need to understand about Excel is that it is susceptible to the “garbage in, garbage out” idea, so a good part of this book is about making sure the data you will be using is clean, consistent, and Excel friendly. That’s a part of using Excel that I rarely see addressed in Excel books.
This book is split into seven chapters:
- Chapter 1, “Back to Basics: What Do You Know Already?”: This chapter provides a quick intro to the absolute essentials you need to know to grapple with Excel. It shows you how to tackle common tasks like printing, sorting and filtering, and fixing cells. It is set up as a series of numbered steps that you can quickly work through to learn or get reacquainted with Excel basics.
Note This book comes with files you can practice on. Find these files at www.mrexcel.com/survivalfiles.html.
- Chapter 2, “Getting Your Data Together: Catching Your File”: This chapter is about getting your data together and, more importantly, assembling your data in a way that makes doing all the other clever stuff with Excel relatively straightforward. This is the secret sauce. This chapter shows you how to set up a list so that your data is entered accurately and completely. It shows you how to set up your data so that you can easily get whatever you need out of it later on. It shows you how to set up a simple list and how to create a list for your recurring data. It’s the piece that’s often omitted in many Excel books. You get it here.
- Chapter 3, “Further Cleaning, Slicing, and Dicing”: Yes, at this point you’ve already got your data into good shape, but you may still need to do further clean-up. This chapter shows you how to remove/complete blank rows, columns, and cells. You will learn some clever quick techniques to clean, combine, and amend existing data. You will also see examples of using formulas to extract specific pieces of data from a data set. You will learn how to identify and remove duplicates and to use conditional formatting to quickly identify the appropriate entries.
- Chapter 4, “The Vlookup() Function: An Excel Essential”: One of the key tasks in Excel is pulling in and assembling matching data from different sources. To do this, you need to know Excel’s Vlookup() function. This entire chapter focuses on this function, including how to use it and also its pitfalls and idiosyncrasies so that you know how to handle this function with speed and care. You will learn how to use it to compare lists and identify missing data (in minutes rather than hours).
- Chapter 5, “Creating Pivot Tables”: When you need to summarize and present all the data you have so lovingly gathered, cleaned, and assembled, you need to use a pivot table. You can use a pivot table to summarize thousands of rows of data in minutes. A learner once described it to me beautifully as “shrinking your data,” and that’s what a pivot table allows you to do. You can quickly and easily view your data by months or by various headings. If your boss wants to get a different view of some data and if you have a pivot table, you can deliver the goods in minutes. Moving from manual data organization using filters and sorting to using pivot tables is like moving from walking to driving.
- Chapter 6, “Using Power Query to Quickly Clean Up Data”: Before this point in the book, you have done a lot of data clean-up manually. In this chapter you learn how to use Power Query to do that work in minutes and, even more amazingly, how to store the steps you take so that all you have to do when you get next month’s data is change the data source. Power Query is a game changer, but it’s still not very well known beyond the Excel world, so if you learn to use it, you have the inside track. I have to say that every time I use Power Query to clean up data, I feel like a magician—and I want you to feel like that, too.
- Chapter 7, “Beyond the Pivot Table: Power Pivot”: If using pivot tables is like driving a car, using Power Pivot is like travelling by jet. Power Pivot allows you to assemble lots of different data sets together without using Vlookup()s and to generate pivot tables with formulas (measures) that allow you to look at your data in all sorts of new ways. Power Pivot is the future of pivot tables, and you’ll get a taste of it here.
Typographical Conventions and Special Elements
The following typographical conventions are used in this book:
- Italic indicates new terms when they are defined, special emphasis, non-English words or phrases, and letters or words used as words.
- Monospace indicates things you type or formulas.
- When I want you to press two keys simultaneously, I use the + symbol. For example, Ctrl+X means you should press the Ctrl and X keys simultaneously.
- When I want you to select multiple items onscreen, I use the | symbol. For example, if I say select File | New, I mean you should select the File tab of the ribbon and then select the New menu item on that tab.
In addition to these typographical conventions, there are several special elements. For example, each chapter includes numbered lists of steps. These lists walk you through processes, step by step. In many cases, I’ve included screenshots with these lists, to help you understand how what you’re seeing syncs up with the directions I’m giving you.
In addition, you will see Notes and Survival Tips:
Note Notes provide additional information outside the main thread of the chapter discussion that might be useful for you to know.
|
Survival Tip Survival Tips provide quick workarounds and time-saving techniques to help you work more efficiently. |
Also, to help you get hands-on practice, I have provided a set of worksheets that demonstrate the concepts described in this book. These files include all the worksheets from the book and sample data. To download the files, visit this book’s web page, at http://www.mrexcel.com/survivalfiles.html.
Sometimes when you open the book files, you get a yellow note at the top of the screen that warns you about enabling content (see Figure I-1). Click on this button to work on the file.
Figure I-1
Check out my website, www.the-excel-expert.com, for lots of free tutorials, tips, and hints for Excel novices and those of you who are more experienced, Excel adepts.