88 Excel Hacks That Will Save You Time and Stress: Part 1

You want to improve your Excel skills, am I right? Yeah, most people do…

But if you found this post then you probably aren’t most people. You don’t want to just get “better” at Excel…

…you want to blow your boss and colleagues’ freaking minds away!

You want to become so good that people in the office, whether they’re in your department or not, simply cannot ignore you.

Here’s a secret that most people don’t tell you: becoming “better” at excel isn’t about how many formulas you know, it’s about habits. Thats right; you need to focus on creating productive work habits that set yourself up for success later.

It’s intimate knowledge of different tricks and hacks and leveraging them to save yourself time, headaches, and overwhelm.

I know that you like to read actionable tips so I decided that the most effective way to serve my audience (that’s you) is to put together a comprehensive list of 88 Excel Hacks, Tips, and Shortcuts that will blow your boss’s mind away!

I also know that many of you feel like your time is being pulled in eighteen different directions (maybe it’s just me) so I understand that time isn’t a luxury. Each tip is meant to be sweet and straight to the point. There are visuals and short GIFs that will help clarify each point.

I also don’t want to overwhelm you so I’ve broken this into a three part series. Make sure to be on the lookout for the second and third installments.

Last thing before you begin; there may be 88 tips but don’t feel like you need to master all of them (if you do though, let me know how big of a pay increase you receive in six months).

Learn the most relevant ones to you right now, and reference this document later whenever you need the others.

Quantity is the best strategy for memory, quality is the best strategy for MASTERY. Choose your path wisely…

Hardcode data into formulas to test them

If you’re about to work on a formula that you concocted in your head but have no idea if it will work in your sheet, test your theory by hard-coding your variables into the formula first, then replace your hard-coded values with cell references later. It helps by making sure the foundational elements of your formula work before you scale it while minimizing wasted time.

But just remember….

Never leave formulas with hardcoded values

This mistake could lead to disastrous results. I see it all the time; you’re working hard trying to meet a deadline. It’s easier and less time consuming to hardcode variables into your formula instead of trying to make your formulas dynamic. A month later you come back to the same sheet and now you don’t remember what assumptions you made or where the hard coded values are.

This approach is prone to errors and time consuming. You also have no idea what mistakes are lurking in your sheet once you’re done.

I suggest creating an input sheet where you enter all your variables and assumptions and an output sheet that does all the number crunching for you. Then work to make your formulas as dynamic as possible. Your goal should be to dump your data and have the sheet spit out whatever result you need.

Customize your own keyboard shortcut with a Macro Recorder

Everybody and their pet goldfish recommends “automating your repetitive tasks using Macro Recorder”.

If you don’t know what a Macro Recorder is, It’s an Excel tool that records your actions and can repeat them, just like a camcorder. With a click of a button you can apply tedious formatting changes, enter formulas, rearrange columns, or all three (and more).

The problem is nobody stops in the middle of the madness that we call a “typical workday” to think “Hey, I can automate this :)”.

I would take the time to think about this when I’m not at work. There are always small tasks you can record into a Macro to save you some time.

Having trouble thinking of something to automate? Repetitive formatting changes are usually the go to thing for me to turn into a Macro shortcut (ie. Turn the cell’s font blue when I hit Ctrl + Shift + B).

Here are some tips published on PC World to help you with recording your Macro:

-Use relative (not absolute) cell references to keep formulas as dynamic as possible
-Always begin in cell A1
-Always navigate using directional keypads
-Keep the macro small (don’t over do it)

Use the Fill Handle to auto populate data/formulas

If you aren’t already, get used to using the fill handle. It’s a small, black crosshair that comes up when you hover over a cell’s bottom-right corner. Double clicking when this cross-hair comes up will automatically populate your table with any data you have in the active cell. It’s a nice time saver when you’re in a rush.

Move the formula hint bar out of the way.

This one is pretty straight forward, whenever the formula hint bar gets in the way simply hover over it and move the sucker away.

Expand formula bar

Working on a long formula? Expand the formula bar to get the full picture

Excel adds final parentheses automatically

When entering a simple formula, hit enter on your keyboard when you’re about done. Excel will automatically enter the final parentheses in your formula.

Use Excel tables to auto-populate formulas

Turning your data into an Excel table can save you a ton of time when entering formulas. If you add a formula to an adjacent cell, Excel will auto-populate that formula to all the rows in the table. Just highlight your data, hit Ctrl + T on your keyboard, and let the magic begin.

Use the Excel Auto-complete Tool to help write formulas faster

When you begin entering a formula, Excel will help you by guessing which formula you’re going to use. Excel will even help by typing out the formula for you so you don’t have to when you hit TAB on your keyboard while typing.

Hold CTRL to select multiple cells

I’ve written about this one before, but I’ll dive into it again. Holding CTRL on your keyboard allows you to make multiple selections. Excel even enters commas into your formulas without having to perform any extra steps.

This works outside of formulas as well.

Formula “Debugging” tool or F9

This is one of my favorites tips; when a complex formula breaks, nothing is more frustrating than having to debug. You might be there a while:

But here is a tool that can help:

This tool shows you, step-by-step, how excel is calculating your formula behind the scenes. It’s like watching your formula in slow-motion!

To activate it, go to Formulas> Evaluate Formulas

Another way to debug your formula is by highlighting any section of it and hitting F9 on your keyboard. Excel will display the result of the highlighted section and allow you to actually see what’s happening behind the scenes.

Ctrl + Shift + A enters formula placeholders

When you hit Ctrl + Shift + A while entering a formula, Excel will enter placeholders arguments and make it easier for you to finish out your formula. Use this trick whenever you’re building complex formulas and need help making sense of all the combinations.

Select blank cells with Go To Special

With go to special you can select all blank cells in a sheet (or inside of a selection).

Select cells containing formulas with Go To Special

Same thing as before, except you can select any cell with a formula!

Format formulas and hard coded values differently

This is more of a data analysis tip that I use in my daily work. Always format your hard coded values in one color and formulas in another. I usually like to make the text in cells containing formulas blue and hardcoded values black.

If I have cells containing assumptions, I’ll put a border around those cells and color them yellow. It really doesn’t matter how you choose to format your cells, the important thing is that you are able to differentiate them in your sheet. It helps you, and anyone else, look at your sheet and decipher the way it works behind the scenes.

Format millions into thousands using custom formats

I don’t like my sheets cluttered with excess data. So, whenever I’m dealing with numbers in the millions I usually hide the decimals (who needs ‘em!).

If that’s not enough though, I open up the formatting menu with Ctrl + 1 and enter “0,” in the custom format menu. This changes every number from millions to thousands and makes the spreadsheet look a lot cleaner and easier to read.

With keyboard shortcuts, focus on creating habits to that lead to muscle memory instead of mental memory. If you’ve played sports before you know what I mean; a baseball player doesn’t think about whether they are turning their hips during a swing, they just do it.

This works the same way. Your goal is to get to the point where your fingers just move directly to the keys that activate the intended action in Excel without you having to give it much thought. There are plenty of shortcuts that I use every day, but if you ask me to tell you which keys I press I’d give you a blank stare.

Read 88 Excel Hacks Pt 2