The business world runs on Excel. I can’t think of a single business that doesn’t use it. In fact, I can’t think of a single person in my company that doesn’t use it to some extent. If you work in growth, marketing or analytics, you probably use it on a daily basis.
Unfortunately, chances are you really suck at excel.
And by suck, I mean you’re slow. Sure, you probably can get your work done, but you’re probably wasting time I don’t care how many times you’ve been endorsed on LinkedIn for MS Office. But, unless you’re an investment banker, and especially if you use the mouse in Excel, I’m gonna bet that you are wasting valuable time because of your Excel suckage.
Excel proficiency revolves around shortcuts. Believe it or not, there is a shortcut for nearly every single thing you’ll ever have to do in Excel, and by using these, you can easily triple your speed. Think about that – if you spend only 6 hours in Excel per week, and triple your speed, you’ve saved yourself 4 hours per week. What will you do with all the time saved?
How I got so wicked fast at Excel
I used to suck at Excel. In fact, I suck at most things that require finger-eye coordination – foosball, mario cart, call of duty. But if Excel was a game people liked to challenge each other to, I’d dominate most people I know.
When I started at Capital One, I had to do a ton of data analysis and modeling, most of which occurred in Excel. We were always on tight deadlines, so I was always looking for ways to improve my efficiency.
I knew from my banker friends that is was possible to get lightning fast with Excel, so I asked them. Turns out, they all had 3 week training classes that taught them all the shortcuts. We didn’t have such a class, and I certainly didn’t have 3 weeks to take off to learn skills. So I figured out a method to increase my speed without interrupting my workflow. Here’s what worked for me. Follow along, and before you know it you’ll be blazing fast in Excel.
Step 1 – Install Training the Street Macros
Excel has a bunch of built in shortcuts, but unfortunately it lacks a ton that us power users long for.
Fortunately Training the Street has provided us with this fantastic plugin that makes Excel much more powerful. Some of my favorites are:
- Autofill down / right – shortcut for something I do several times a day
- Formatting shortcuts – helps you keep your workbooks organized
Once you have it installed, you’re ready to get started.
Step 2 – Print out your cheat sheets and pin em on the wall
Now that you’ve expanded your shortcut capabilities, you need to print out two cheat sheets:
- The cheat sheet included in the Training the Street Macros that you previously installed
- The appropriate Wall Street Prep cheat sheet found here
Can you keep a digital copy? No! Print them out, and put them above your desk. The reason you need to do this will become apparent in the next step.
Step 3 – Learn as you go, and don’t you dare touch that mouse!!!
Between the two cheat sheets that you’ve printed out, you should be able to do 95% of everything you’ll ever need to do in Excel…without using the mouse. But how to train yourself to do this? Fortunately, it’s pretty easy.
First, stop using your mouse. Just stop it. It may feel uncomfortable at first, and may even slow you down temporarily. But resist the urge. The mouse is the devil.
Now…whenever you come across something that you need to do that you don’t have a shortcut for…glance up at your cheat sheet, and find the short cut.
It may take 3-5 times doing this before you remember the shortcut, and an additional 5 times before it becomes second nature. Once you do a shortcut without having to look up on your cheat sheet, highlight it. This is not only your badge of accomplishment, but it will help you find the remaining shortcuts much more quickly. Before you know it, they will be second nature to you.
Step 4 – Fill in the blanks
I mentioned that these cheat sheets will take care of about 95% of your Excel needs. What about the other 5%? Should you use the mouse?
You see, everything in Excel can be done using the keyboard. And there are some little-known Excel gems that will help you out a lot.
1 – Find your own shortcuts
For anything you want to do, you can probably Google a shortcut. But you can also find it yourself pretty easily.
Most commands begin by hitting “alt”. When you hit “alt” Excel will tell you the keys to hit to complete your desired action (see below)
Also, if you’re ever in a menu where you see an underline under one of the options, that is the shortcut key you use to access it. For example, suppose you’re in the menu below, and you want to change the horizontal alignment. “H” is the shortcut key.
2 – Control + 1
For all formatting needs, you can hit control + 1 (same screen as above) and it will open a modal with every formatting option. You can then use the method I mentioned in the previous step to navigate it.
For example, I need to merge cells at least a dozen times per day. Control + 1 + alt + m + enter does the trick.
3 – The macro recorder
Macros are functions that automate some sort of process in excel. Suppose you have a multi-step action that you want to to regularly in Excel. A macro is essentially code that allows you to repeat this by just clicking a button or hitting a shortcut key.
They are written in a programming language called VBA. Books have been written on VBA, but fortunately you don’t need to read them for most things. All thanks to the macro recorder.
Alt + t + m + r is the shortcut for the macro recorder, and it essentially records an action that you do manually.
You can then save it to a shortcut key. To Make sure that you don’t override another shortcut, hold “shift” before you choose your shortcut.
There are two options for the macro recorder: absolute reference and relative reference. What’s the difference?
Let’s say I spend a lot of time coloring cells hot pink. I’m currently on cell A4. I hit “alt + t + m +r” to start recording my macro, fill in cell A4 with the hot pink color, and hit save. If I had used “absolute reference”, I could be on cell B8 on a different sheet, and it will highlight that same cell A4. However, if I use relative reference, it will highlight that current cell.
When you choose a shortcut for your macros, always hold the shift key before you do it. This will prevent you from overriding other built-in shortcuts.
- Use a PC for excel: I hate pretty much everything about PCs. The UX gets worse with every release, they freeze, they lag, and if you even think about viewing a porn site they get a virus. I absolutely hate PCs for just about everything…except Excel. Mac Excel really sucks – it lacks shortcuts, and freezes up around 20k rows. Even though switching between Mac and PC is annoying, you can just get way more done on a PC in Excel.
- Keep a consistent formatting scheme: By keeping your formatting consistent, you can stay organized and avoid errors. It also makes it easier to come back to projects you haven’t worked on in awhile, and also makes sharing documents with others easier. It’s the equivalent of commenting code (also make sure to use the comment function (control + ~). Here’s an example of what I mean:
- Calculated cells: black
- Inputs that are meant to be toggled: blue, tan highlight
- Inputs that are pasted in: blue
- Links to other sheets: green
- Paste database code in your spreadsheets: This is another trick I picked up at Capital One that saved me a ton of time. If you are commonly querying SQL databases, it can be useful to paste your up-to-date queries inside your Excel workbook. For one, it helps you stay organized, and makes it easy to update spreadsheets and share with teammates. Additionally, with more complex queries, it can be faster to build the queries in Excel using the =concatenate() function.
There you go. Within 30 days or less, you’ll be an Excel wiz. What to do with all the time saved?