Episode: 78 |
Will Bachman:
Mastering Excel:


Will Bachman

Mastering Excel

Show Notes

You wouldn’t hire a plumber who isn’t comfortable using a wrench, or a lumberjack who can’t handle a chainsaw.  A professional needs to master the tools of the trade.

For independent consultants, a fundamental tool is Excel. For most, it isn’t necessary to program in Visual Basic or write macros, but it is essential to master all the basic tools, including, for example, pivot tables, vlookups, if functions, date functions.

In this short episode, I share some suggestions on how to sharpen your Excel skills if they are rusty, and some particular Excel tools to prioritize learning.

One weekly email with bonus materials and summaries of each new episode:

Will Bachman: Would you hire a lumberjack who has a strong theoretical knowledge of trees, but can’t handle a chainsaw? Hey, welcome to Unleashed, the show that explores how to thrive as an independent professional. Unleashed is produced by Umbrex, and I’m your host, Will Bachman. Recently, a chief procurement officer who’s thinking about leaving his role reached out to me, asking for advice on how to transition to independent consulting. I asked him about his Excel skills, and he told me, “Well, I can do the basics, but not complicated stuff like VLOOKUPs.” This is not an isolated incident. I’m, regularly meet individuals who want to be independent consultants, and they have not mastered the tools of the trade. That either prevents them from getting projects that they could get otherwise, or I’ve seen people get staffed on projects and then rolled off because they don’t have the analytical skills that are needed.
If we have, let’s say consultant A has an MBA, but weak Excel skills, and consultant B has no MBA, but very strong Excel skills, and they’re kind of equally smart, in a lot of cases I’d frankly rather have consultant B on my team. If consultant C has an MBA and very strong Excel skills, obviously I’ll prefer consultant C over both of them. Now, an MBA costs, what, $100,000 and two years of your life. Getting really strong at Excel costs less than $100 and maybe 40 hours of focused study, so it obviously makes sense to invest the time to develop very strong Excel skills. In fact, I don’t think that there is a better investment of time in terms of return on investment. If a consultant has not done so and has weak Excel skills, it’s a strong negative signal about that person’s overall just dedication to the craft and to professional development.
Here’s a personal story on how I learned the hard way that I ought to learn the basics and master Excel. I started at McKinsey with very little knowledge of Excel. This was 2001. I had been in the Navy before, in the Submarine Force, where I had never touched Microsoft Excel. On my first project at McKinsey, we had this dataset of 100,000 bank customers, and my job was to split them into nine segments. I did that, and then I had to come up with, for each one of these nine segments, the total balance held by each of those segments, along with the number of ATM transactions, the total number of customers in those segments, how many additional products at the bank each one of those segments held, and a series of other factors.
As a novice Excel user, I did know about data filters, so I would filter all the data. I filtered it once, and then for one segment, and I added up all the columns, counted all the columns, and put all those numbers in a separate tab. That took me about an hour, and then I did the next segment, and the next segment, doing that eight times. Then the senior associate came back to me and asked me to adjust the definitions of the segments and to recalculate all the numbers. I ended up spending a few days doing nothing but filtering data in the spreadsheet and adjusting all those sums. Not the most highest-value-added time to the project.
Only after that project was done and over that I learned about pivot tables and discovered that each one of those recalculations would have taken me less than 60 seconds if I had known about pivot tables, so after that, I realized there were features of Excel that I didn’t know how to use and I didn’t even know that I didn’t know about them. They were Donald Rumsfeld’s unknown unknowns, and I figured I’d better master Excel or at least know what was in there and what existed.
This was my approach. I printed out a list of every single function in Excel. It was maybe 150. Then I used each function, I tried them out, and I just inserted each function. I used each function. Some of those I have never used or seen or heard from again, particularly all those math and science functions and engineering functions. Hyperbolic tangent, anyone? My goal was not to master these functions, to memorize them, but just to know that they exist, so if a situation came up, I would know that there was some solution available, and then I could search for it, but at least I’d know to search for it. That turned out to be a useful strategy.
Just here’s an example. Basic data cleansing. Let’s say you have some data where all the numbers are formatted as text instead of numbers, and you can’t add them up or do any calculations, and you’re trying to figure it out, because Excel is seeing that as just alphanumeric. It looks baffling. You have no clue what’s going on. If you haven’t kind of gone through all these functions, you might not know what to do. If you do have, then you know there’s a function called VALUE, V-A-L-U-E, and you just insert another column, say, “Value of,” and then the cell to the left, and you convert all that text to values, and off you go. Went through all the functions. Then I went through each buttons on the menu to figure out what they do and actually to learn how to do charts in Excel. I found that kind of complicated, so I bought a book. I also got another book on pivot tables.
If you learn best from books, there are dozens, probably hundreds of Excel books out there. My advice on books is, find one where you like the style, some maybe try to be humorous. Some are more business-oriented. Some are focused mainly on just financial modeling, but some are more broad. Most of them will have exercise data that you can download from some website, and that’s really the only way to learn Excel is to get your fingers busy on the keyboard. Use it yourself.
Nowadays, there’s also a nearly unlimited variety of courses online. LinkedIn Learning alone, I just looked at it. There’s 5,385 courses that come up for Excel. If you have a premium LinkedIn membership, they’re free. If you don’t, I’m not sure how much you have to pay. It’s the former, I think Lynda.com Excel bought them, or I’m sorry, LinkedIn bought them. One course, as just an example, Excel 2016: Pivot Tables in Depth by Curt Frye. Apparently, 111,000 people have viewed that course. It has exercise data and three hours of lectures. I am assuming that if you took that course, which is free if you have a premium LinkedIn membership, you would master pivot tables. Probably in less than a day, in a few hours.
Whether you decide to learn from a book or online course, or just exploring it methodically, here are some tips on what I found helpful. Number one, pivot tables, already mentioned them a few times, if you have millions of rows of data, and you want to be able to slice it and dice it, just explore it. A pivot table’s incredibly powerful. Number two, if statements. Number three, VLOOKUPs and HLOOKUPs. Those are very helpful if you have two tables, and you want to be able to pull in data from one table into another. Let’s say number three, number four, be able to freeze a screen. That’s very helpful if you have like a row at the top and you want to keep that row up there with your column headings as you scroll through the data. Moving around the screen with arrow keys, number five’s useful.
Number six, Sort command. Obviously, incredibly helpful if you just want to quickly see what are the top or the bottom values in your spreadsheet. One tip, though, is, don’t highlight a whole column and then do Sort, because that will sort just that column and destroy the data integrity of your table, which is real disaster. You just want to have one cell highlighted. With Sort, you can also sort by, first by this column and then by that column, so that’s nice, so you could sort first by a region and then by a sales rep in that region, as an example.
A data filter, mentioned that before. That’s great if you quickly just want to start seeing, being able to look at maybe just one region, or look at just one sales rep. A data filter’s also helpful if you’re trying to get rid of some data from your spreadsheet. If you, I don’t know, had four regions of data, and let’s say that you only want to look at the eastern region and do a bunch of manipulations on that, you could filter, say, “Show me everything that’s not, except for the east,” and then you can delete all those rows and unfilter it, and then you have just the east. Fill Down. Use that all the time, Control-D, so if you have a formula, and instead of copying that and pasting that into every cell, you can just go down all the way down to the bottom of your spreadsheet and then highlight all the cells above it and Control-D, it’ll copy that formula down. Super helpful. COUNT is a very helpful formula if you just want to count the number of things in a row or column.
Paste Special is something you should definitely master. Sometimes, you want to paste a value, so you want to copy something that is a formula, and you want to paste it as a value, because if you copy a formula and paste it as a formula, that formula will reference now some new random cells, and it’ll often be just garbage, but if you want to copy a group and paste just the values, you paste the values, sometimes you want to paste the formatting, so if you like the formatting you have set up one place, instead of reformatting other cells, you can copy and Paste Special the formatting.
Trace Precedents and Trace Descendants are super helpful when you’re trying to debug something. Customizing the toolbars, so maybe you have some things that you want to be able to just click on and do. Not every single function or not every single little button is actually included on the ribbons as the predefined. You may want to include some of your own. Print setup. When you go into Page Layout, Page Setup, sometimes you’ll find someone who, it’s just like a little bit too wide, and then it prints it out as two pages, so you kind of have to put them together. If you go into Page Setup, you can say, “Fit to one page wide,” and then just delete the thing for tall, and then your thing will print out just one page wide. It’ll squeeze it down, squeeze it. It’ll squeeze it sideways a little bit. Also, you can go on sheet and say what rows you want to repeat at the top. That’s very helpful, or for header footer if you want to have page one of X printed out on the bottom. Let’s see.
Insert Function. If you want to have a function, but you’re not quite sure, if you go to Formulas tab and then Insert Function, you can select any function, and it will, when you insert it, it’ll kind of walk you through what each of the different fields are. Let’s see. What else? I’m going to look at the tab here. On Control-1, useful shortcut that just brings up the Format Cells, use that a lot. In general, the keyboard shortcuts are really useful to master. I don’t know all of them, but there are some that just are handy, I find, like if you want to highlight a row, Shift-Spacebar, if you want to highlight a column, Control-Spacebar. You don’t need to memorize this. Just google like “Excel keyboard shortcuts,” and then my recommendation is actually, open a spreadsheet and try each one of those shortcuts.
Some you’ll say, “I’m never going to use this one. This is kind of totally random,” but some are, say, “Oh, that’s pretty helpful,” so just learn the ones that you personally find helpful, but let’s see. Other things, let’s see. On the Home tab, there’s Paste, Cut, being able to, the Alignment, just knowing how to align the cells. Conditional Formatting, it can be helpful. I don’t typically use that a lot. Let’s see, on the Insert tab some other things. Pivot … That’s where you’ll find pivot tables.
On PivotTable, if you have your data, just click on any cell inside the data table, and then you go to Insert tab, PivotTable, insert. That’s also where your insert charts. Page Layout. Being able to set Print Area is useful. It’s a pretty simple thing, Page Layout, Print Area, Set Print Area if you don’t want to print out everything. Formulas, Insert Function is, obviously, we mentioned that one. Trace Precedents. Trace Dependents. On Data tab is where you can do Text to Columns. Sometimes you can do that with formulas, but Text to Columns is quick. Sometimes, you’ll get some data where each row, instead of being split into columns, is just like one cell, and that’s annoying. Text to Columns will often split that out nicely for you into multiple, multiple columns. Let’s see. I don’t use the Review tab too often. The View tab, sometimes if you want to see what it’s going to look like when you print it out, you can get a Page Layout view or Print Preview.
Then those are some of the main things, but really, the key is to go through and explore particularly all the functions. As I think about some of the functions that are particularly helpful, date functions, they might … I often will go to, often you’re doing something with dates. Just recently, I was trying to figure out how many workdays were there going to be between, let’s say, June like 14th and the end of November. You could go to a calendar and just count them up, or you can go to NETWORKDAYS, and there’s actually a function for that, NETWORKDAYS. You put in the last, the first date and the last calendar date, and it will tell you the number of workdays between those two days. Sometimes, you’ll have some data, or for some reason, you want to find out what day of the week something happened on, there’s formulas for that. You can obviously subtract dates to just tell you how many days between them.
Date functions are useful. Statistical, I don’t do as much statistical stuff, but just about every kind of statistical operation. Lookup & Reference, often very, very handy, particularly the VLOOKUP and HLOOKUP, like we mentioned before, and then some of the text functions are helpful. Sometimes, if you have, like for example CONCATENATE. Great if you want to add two columns together and make a third column. If you have first name in one column and last name in another column, but you actually want to be able to paste somewhere else their full name, CONCATENATE first name, comma, you just put a space in there, comma, last name, and then you can, and then you get a column with just their full names, so there you go. Text functions.
I’ve gone on a bit. You’ll find for your own purposes what is actually the most helpful thing, but the only way you’ll do that is if you really explore the full tool. I hope you found this helpful. Love to hear your thoughts, how you’ve gotten better at Excel, what other tools you think we consultants need to master, and you can email me at unleashed@umbrex.com. If you go on our website at umbrex.com/unleashed, you can sign up for our weekly Unleashed email, where you’ll get the transcript of every episode, plus some bonus features available only to subscribers, what I’m reading, what I recommend, and some other tips and tricks. Finally, if you have listened this far, I would love it if you could write a review on iTunes, that would be awesome, or if you could share this episode on social media, share it with someone who you think could benefit from it. Thanks for listening.

Related Episodes


Automating Tax Accounting for Solopreneurs

Ran Harpaz


Integrating AI into a 100-year-old Media Business

Salah Zalatimo


Author of Second Act, on The Secrets of Late Bloomers

Henry Oliver


Third Party Risk Management and Cyber Security

Craig Callé