This is the Final installment of a three part series. If you haven’t read part one, check it out HERE

 

Practical Excel Hacks that will save you time, headaches, and hair pulling

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.

If this tip doesn’t really help you in a practical way, you can at least use it to prank a coworker and drive them mad while they figure out what the heck is wrong with their sheet (hint, watch below):

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.

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!!!

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.

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:

1. you can password protect the entire workbook from anyone opening it OR…
2. 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.

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.

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.

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.

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)

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.

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])

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.

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.

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

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.

Useful Excel Shortcuts that will save you time

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.

pre>

 

Learn more about syncing powerpoint with Excel here