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…
1. 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….
2. 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.
3. 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)
4. 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.
5. 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.
6. Expand formula bar
Working on a long formula? Expand the formula bar to get the full picture
7. 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.
8. 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.
9. 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.
10. 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.
11. 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.
12. 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.
13. 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).
14. Select cells containing formulas with Go To Special
Same thing as before, except you can select any cell with a formula!
15. 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.
16. 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.
17. Turn positive numbers into negatives with paste special
You have a list of numbers and need to reverse the signs? Enter -1 into a cell and use paste special.
Once you have a cell containing -1, copy that cell, select the numbers you want to reverse the signs for and copy, right click and select “Paste Special” (or Ctrl + Alt + V).
Once you have the window open, select values, then multiply, and hit ok.
18. Use paste special to transpose a table
Have a table or a list and you need to transpose the rows into columns (or columns into rows)? Paste special can also help with that.
19. Use numbering system to help make VLOOKUPS easier
You love using VLOOKUPS (who doesn’t, right?!) but hate counting columns. Easy fix, just add column numbers along the top of your table to make it easier on you to reference.
20. Become a formula NINJA with named ranges!
Named ranges are to an Excel user what a lightsaber is to a Jedi knight. You can continue struggling to enter formulas and later deciphering what your formulas do, or you can use named ranges and make your life easier.
When you name a range you’re giving meaning to an otherwise meaningless Excel range. A1:C25 turns into hourly_rate and all of a sudden your formula doesn’t look like Japanese.
You can write “hourly_rate” inside your formula and excel automatically knows exactly where to go to find its data
21. Select arguments via formula tip window
You can make it easier on yourself to enter formula arguments by selecting them inside the formula tip window. Better yet, you can make it easier on yourself to audit your formulas with this little trick.
22. Break complicated formulas into smaller pieces
Sure, it’s impressive to others when they see your work and Instead of trying to write a complicated formula in one cell, break it out into pieces in separate cells. Make sure each piece works as intended and make sure you ensue each piece is labeled and descriptive to a new user.
This does three things, it makes it easier on you to explain your sheet to others, makes it easier for you to understand how to replicate and update your sheet when necessary, and when bugs arise it is easy to figure out which step in the chain is broken.
Easy, peasy!
23. You can always combine the individual pieces and impress everyone
Still want to impress everyone by having extra long, complicated formulas? No problem, just follow the last tip, select the necessary arguments using the formula tip window, and just copy and paste!
24. Use custom formatting to create descriptive labels
Numbers mean nothing without context, but excel lets you add that context right into your cells without compromising the cell’s data. You can use custom formatting to make numbers and labels more descriptive.
This works with dates as well.
25. Use concatenate, or ‘&’, to merge text values
You can merge text strings from different cells into one by using concatenate, or “&”.
If you’re wondering how this seemingly minimal trick can be used in a practical scenario…
26. VLOOKUP with multiple lookup criteria.
One of VLOOKUP’s many limitations is that you can only use one criteria to lookup data in another table. But, concatenating text strings allows you to create a unique index and perform VLOOKUPS with multiple criteria.
27. Make changes to multiple tabs at once
If you select multiple tabs while holding CTRL and enter something into a cell on one tab, this value gets entered in the exact cell on all tabs you have selected.
28. Spot duplicates
If you have a long list of values and you want to see if you have any duplicates, you can easily use conditional formatting to highlight any duplicates, then use your filters to display those values.
29. Remove Duplicates
Building on the last tip, you can also automatically remove duplicate values in your sheet by using Excel’s “Remove Duplicates” tool.
30. Extract specific text within a cell
=mid([cell extracting from],find([text],[cell extracting from],1),len([text]))
FIND – Finds the text within the text string and returns which character that text string begins. For example, the word “Stark” begins in character number 7 within the text string “Sansa Stark”.
LEN – Returns the number of characters in the word “Stark” (5).
MID – Uses the number 7 returned by FIND and begins extracting characters at this point in the text string. Since LEN returned the number 5, the formula knows to only extract 5 characters to get the word “Stark” from the string “Sansa Stark”.
If you want to eliminate the errors when excel doesn’t find what you’re looking for, wrap it in an IFERROR
=IFERROR(mid([cell extracting from],find([text],[cell extracting from],1),len([text])),”Not Found”)
31. Check if it is a first occurrence within a list of values
This trick is handy whenever you need to figure out which values are unique in a list.
=if(isna(MATCH([Value You’re Checking],[List, ie. $A$2:A6],0)),”FIRST OCCURRENCE”,””)
MATCH – Checks whether the value you’re evaluating is a first occurrence in the list by searching for this value within the list before it. As the formula is copied down the range expands thanks to the combination of absolute and relative cell references (ie. $A$2 stays fixed while A6 shifts to A7, A8, and so on). The MATCH function returns an #N/A error when it is a first occurrence.
ISNA – Evaluates whether the result of MATCH is an #N/A error or not. If it is (which means it’s a first occurrence, it returns a TRUE ir FALSE.
IF – Takes the TRUE or FALSE returned by ISNA and returns the text string “FIRST OCCURRENCE” when its true and blank (indicated by entering “”) if its is FALSE.
32. Calculate the business days between two dates
NETWORKDAYS calculates approximately how many business days there are between two dates.
=NETWORKDAYS([Start date],[End date])
There have been approximately 7,915 business days since Halley’s comet visited earth (at the time of this writing 🙂 )
33. Get exact number of months, years between two dates
DATEFID gives you the amount of time between two dates represented by month, days, or years depending on what you specify.
Please note that this function is a secret Excel function. Reason is because it’s not listed anywhere and when you try and use it Excel will offer no help for it. Instead, you need to memorize the syntax and use it like a pro despite the lack of help 🙂
=DATEDIF([Start date],[End date], unit)
In this function, unit is a string abbreviation of the unit of time (ie. “M” = month, “Y” = year).
34. Change formula calculation options
If you’re ever working on an overloaded sheet and hate seeing formulas recalculate repeatedly, slowing you down in the process, you can actually tell Excel to hold its horses for a few minutes while you finish what you’re doing.
Set Excel’s calculation options to manual is easy, but when you do Excel will not attempt to recalculate your sheet. Just remember to set it back to automatic calculation when you’re done.
35. Use your mouse to change/expand cell references
I’m no fan of using your mouse in Excel, but this time I break my own rule. When you are writing/editing a formula, you can actually use your mouse to move and expand those colorful cell references. Makes things pretty convenient on you not having to type these out yourself.
36. Use print view to modify how a sheet will look before it’s printed.
This is one of my favorite tips. I hate printing a report only to realize that the last column was cut off and printed on a separate page.
You can actually use print preview to print exactly what you want and on which page. You can even modify how big/small you want your data to appear on the page.
Quick side note, for the love of god please stop using standard letter size paper for larger tables and print them on legal size instead!!!
37. Make other people’s lives easier and print column headers on each page
I hate when I’m handed a multiple page list from Excel on printed paper and the column headers only appear on the first page. Having to constantly flip back to the first page to see what the heck i’m looking at is a real pain.
Avoid this by printing the column headers on each page of the report.
38. Password protect your workbook
You are working on a masterpiece in Excel and you want to make sure people with too much curiosity stay out? Password protect it!
There are two ways to accomplish this:
- you can password protect the entire workbook from anyone opening it OR…
- You can password protect it but still let others who do not possess the password to open and view the workbook in View Only Mode. This is essentially a password against modifying the workbook.
The latter is useful when I’m working on something important but I don’t want anyone going in and accidentally messing with my formulas or the data itself.
39. Trace Dependents
I’ve written about this briefly in my last post, how to be awesome at fixing excel errors:
If you go to your Formulas tab in ribbon you’ll see a button that says “Trace Dependent”. Select this option and Excel will point out whatever is being affected by the cell you have highlighted.
You can also trace where a cell’s value is being pulled from by using “Trace Precedent” In the same menu. Arrows show you what cells are being used to obtain a result, which can be useful whenever you are debugging a formula or looking to optimize your sheet’s performance.
40. Goal Seek
Goal seek is an excel tool that allows you to figure out a mystery value within a formula without having to perform multiple calculations to figure it out.
For example, let’s say you are budgeting to buy a car and you know that the most you can afford is a $300/mo car note. You know how much the car will cost you and you want to figure out what’s the highest interest rate you can afford in order to keep your monthly payments below $300.
Just insert the variables into goal seek and it will perform hundreds of calculations and a few seconds later spit out the result.
41. Insert subtotals in your table
Excel’s subtotals feature allows you to insert subtotal lines into your table automatically. Just go to the data tab, click on “Subtotal”, choose your options, and viola.
42. Use advanced filters to make filtering easy and efficient
Excel filters are awesome but they have a couple drawbacks. The main drawback is you can’t always see what it is you’re filtering so it’s easy to get lost or confused if you get distracted (which let’s face it, happens all the time).
Excel’s advanced filters let you see at a glance what it is you have filtered and lets you filter your data quickly and efficiently, especially when you have a large table with over 10 – 15 columns.
You can use advanced filters by going to Data>Advanced within the Sort & Filter Section (or Alt, then A, then Q on your keyboard)
43. Get today’s date and time and make sure it’s always updated automatically
If you’re building something in Excel that needs to be updated automatically depending on a specific date, you can always use either TODAY or NOW.
These functions will always update with today’s date and time (depending on which one you use) whenever Excel recalculates its formulas (ie. anytime you change any cell, open the sheet, or manually recalculate formulas).
TODAY gives you only the current date, while NOW gives you the current date and time.
44. Generate random numbers
Working on a model and need some test numbers before you scale it or apply it on your date? Use RANDBETWEEN to get any random number between two numbers.
=RANDBETWEEN([low number], [high number])
45. Remove unnecessary spaces in your cell
Ever run a VLOOKUP and the function doesn’t return what you’re looking for even though you checked your data and it looks like it matches what’s in the lookup table? (Side note: probably the nerdiest question you’ve gotten in a long time)
Chances are that you have an extra space in there somewhere.
With TRIM, you can take care of this and eliminate spaces before or after your value.
46. Insert file path in any excel sheet
There are multiple ways of doing this, but one way is to use the CELL function. It will insert the file path of any Excel file you’re working on.
Its pretty useful when you’re working on a server where many people are also working on. You print a report and give it to your manager, and while they’re reviewing it they notice something they need to look into.
Instead of coming to bug you about it, you can insert the file path on the sheet so that it prints and they can look for the file themselves.
47. Create heat maps with your data
Nothing is better than information that can be communicated effectively using a heat map. It’s essentially taking an excel table and using conditional formatting to assign colors to a cell depending on its value.
It’s a great way to impress everyone you work with and show off your Excel prowess!
Learn more about heat maps and conditional formatting here
48. Synchronize you Excel sheet with your powerpoint slides
Nothing is worse than manually updating multiple powerpoint charts and graphs when your data exists in Excel.
But with this trick, you can connect your powerpoint file with Excel, make any changes/updates you need in Excel and have powerpoint automatically update all your charts and graphs.
This is especially clutch when you need to update your slides periodically (ie. monthly financial updates). You can build some pretty slick dashboards and models and have powerpoint just pickup the new data each month.
Tip 20 vlookup with numbers is not needed, when you select the table with data, Excel automatic gives the numbers of rows and columns.
And tip 26 why only five vlookup as formula this can also been done by using index + match.
Pretty nice post. I just stumbled upon your
weblog and wanted to say that I have really enjoyed browsing your blog posts.
In any case I’ll be subscribing to your rss feed and I
hope you write again soon!