Overview
I’ve picked up many Excel tips and tricks over the years (primarily because I’m lazy). It’s the app that I’ve used more than any other, making up probably 80% of my working hours when you exclude emails and meetings. Early in my career, I was crunched for space and time, so I was somewhat forced to focus on efficiency. I’m glad I have, because I’ve often produced the equivalent of 2-3 people.
Before we get into my top 10 Excel time hacks, I’ll start by saying you should always be learning. Technology changes, best practices change, and what’s important changes. Make sure you carve out some learning time – on your own and chatting with others – so you can change along too!
The Top 10 List
USE KEYBOARD SHORTCUTS
Keyboard shortcuts are proven to be 3x faster than using the mouse, so for any apps you use frequently, it’s worth learning several of them. There are hundreds of keyboard shortcuts available in Excel. You probably know some of the basic ones like Ctrl+c for copy and Ctrl+v for paste, but there are so many common actions you’ll take in Excel, and keyboard shortcuts are the quickest way to execute them. Pay attention to the commands you use most and make an effort to learn the keyboard shortcuts for them.
Pro tip: Two super shortcuts you should learn quickly are Alt key and the Menu key (normally near the right side of the spacebar).
Alt lets you follow along the ribbon menus with a string of characters. The Menu key mimics the contextual right-click menu.
Alt lets you follow along the ribbon menus with a string of characters. The Menu key mimics the contextual right-click menu.
Resource: Get a workbook full of keyboard shortcuts for Excel, Word, PowerPoint, Outlook, File Explorer, and Google Chrome here.
USE MACROS
One of the most powerful Excel features is the ability to leverage macros to extend the Excel feature set and automate commands. Even better is the ability to record your actions to build your own macros (that’s how I got started!). Virtually any repetitive task or series of steps can be automated or condensed into fewer steps by leveraging macros.
Pro tip: Want to leverage the power of macros, but don’t want to build them? There are tens of thousands of templates and add-ins you can leverage containing professional-grade functions that help you work more efficiently or do things you couldn’t otherwise do in Excel.
Resource: Check out the XLEV8 Excel Add-in, which contains 350+ macros of all kinds, including the 10 examples in the graphic above!
USE POWER QUERY
Power Query, otherwise known as the “Get and Transform Data” tool is incredible at automating the many repetitive steps that users often perform when getting data into Excel and manipulating it to make it useful for further analysis and calculations. It is extremely flexible in the options of how you can automatically connect to data, as well as how you can transform that data to suit your needs.
Pro tip: Learn some of the most common Power Query actions first, such as filtering, grouping, calculating fields, transposing, and unpivoting.
LINK EVERYTHING YOU CAN
Most people have hard-coded, manually-entered data all throughout their Excel files. This isn’t just inefficient, it’s irresponsible! It’s virtually impossible to review every piece of data in most workbooks, so it’s crucial to minimize the inputs and have one source of the truth. Link all your analysis and reports to those few inputs and the source data itself in a way that’s easy to understand for others (and your future self!).
Pro tip: Another place you might store data values is in lookup sheets/tables. If you have data that will be used over and over again throughout your workbook, consider putting it in a lookup table so there is only one place to update as the values change.
USE TEMPLATES
Highly related to linking everything you can is leveraging templates. I’ve seen too many people run a raw report and manually make all kinds of formatting, structure, and calculation updates. Those changes aren’t necessary! Ideally that source data is in a clean, tabular format you can use for your Source Data sheet. Even if it’s not, you can often leverage the other items on this list (Power Query, Macros, and Dynamic Array Functions) to clean it up. The key is to leverage previous effort as much as you can. That might take a change in mindset!
Pro tip: I considered making the Instructions sheet it’s own time hack – that’s how important it is. We all spend more time than we’d like troubleshooting and rethinking the process. Include instructions and notes that are clear, simple, and comprehensive to make sure that you and others leverage your template as intended!
USE DATA VALIDATION
You’ve heard the mantra “garbage in, garbage out” and defines Excel possibly more than any other piece of software out there. Data validation gives you several options to guide users on how to enter data with text, number, date, drop-down list, and custom formula options. Keep in mind they aren’t bullet-proof – without protecting cells, it’s easy to clear or change data validation settings. If input data is crucial, it’s best to further verify accuracy with macros, a thorough review, or tools outside of Excel.
Pro tip: Dynamic drop-down lists – those that automatically update as the source list changes – are a great way to guide users. Custom formulas offer a ton of flexibility as well, such as requiring a valid phone number or email address format.
USE DYNAMIC ARRAY FUNCTIONS
Dynamic Array Functions (DAFs) are one of the biggest enhancements in Excel’s history. By updating and spilling into other cells automatically, you’re able to simplify formulas, use fewer helper columns, sheets, and manual manipulation steps.
Pro tip: DAFs are even more powerful if you combine them together! Consider a data set you need to transpose, then filter, then reduce to unique values, then sort. These steps can all be performed with nested DAFs!
USE CONDITIONAL FORMATTING
Of all the valuable features in Excel, conditional formatting might be the most underused. It helps you find errors, exceptions, variances, blanks, duplicates, and just about any kind of numeric or text search you can throw at it using custom formulas. It also helps you add a polished look to your data automatically – changing with your data.
Pro tip: Keep in mind that conditional formatting rules are based on priority and override fixed formatting. Before tearing your hair out wondering why your formats don’t work, review the conditional formatting rules on the sheet and verify their priority.
USE CHECKS AND BALANCES
We often look at data in several different ways, even calculating differently (i.e. top-down vs. bottom-up). It is vital to ensure that the sum of the parts equals the whole. Comprehensive tie-outs, checks, and balances are very helpful for ensuring completeness and accuracy. Anywhere you compare numbers between files and sheets, consider comparing them and call them out when they don’t match.
Pro tip: Use conditional formatting so checks easily stick out. Collapse or hide them when they would be otherwise distracting.
Keep a central, linked list of all the checks throughout your file so you can see them all in one place.
SAVE FILES THOUGHTFULLY
It’s amazing how much time we spend – hundreds of hours a year – looking for information, including digital files of all kinds . It is crucial to be consistent and specific when naming files, including where you save them. Many years ago, I accidentally stumbled upon the approach above – ending the file with a “_YYYY-MM-DD” date suffix. The mere act of typing the date has often helped me find files quickly, at least by narrowing down within a few months of when I created them.
Pro tip: If you can, have Excel name the file for you! I use several macros that help ensure that I save files with consistent names and locations.
Summary
Excel is more powerful than 99% of people give it credit for – so much more than just spreadsheets. Learning the time hacks above – and how they work together – will make you to go-to expert in your company and your sphere of influence. Like me, it can have a profound impact on your career, and prevent a lot of late nights performing work the slow way. I can’t make you choose to be more efficient in Excel (or any aspect of life really), but I can tell you it sure is worth it!
What’s your favorite Excel time hack? How would you change the list above? Let us know in the comments below!
Recent Comments