Top 8 Excel Tips and Tricks to Save You Time
The goal of Excel University isn’t simply to teach you how to get the work done, but how to get the work done faster! After all, making the most of Excel is all about learning how to use it efficiently. Whether you use the software for personal budgeting or large-scale data analysis, these Excel tips and tricks will help you shave seconds, minutes, or even hours off the time it takes to finish your next project.
1. Invest time in learning (some) keyboard shortcuts.
There are so many keyboard shortcuts available in Excel that it’s overwhelming to try and learn them all. However, it’s definitely worth getting a strong understanding of a few that you’ll use on a daily basis!
A few extra seconds to move your hand to the mouse probably doesn’t seem like much, but those seconds add up. The more your hands can stay on the keyboard, the more time you’ll save.
And if there’s no built-in keyboard shortcut for your favorite commands? You can create them with the Quick Access Toolbar!
2. Hyperlink to other tabs, cells, or sheets to easily find data.
If your workbook contains multiple worksheets with cross-referenced data, building a direct hyperlink allows you to navigate to them quickly. It’s so much more efficient than having to search through different sheets (especially when you have a lot of them)!
Linking to specific data is especially useful if you’re sharing a workbook and want others to be able to find the data quickly as well.
The only downside is that hyperlinks can break if the name of a sheet was updated. Luckily, you can prevent that problem by using defined names.
3. Use the SUBTOTAL function instead of SUM.
SUM is probably one of Excel’s most-used functions, but in many cases, you can actually use SUBTOTAL to increase both the accuracy and efficiency of your data entry!
That’s because the SUBTOTAL function excludes the other SUBTOTAL functions in the range. It can be used over and over again in the same column – and then once again at the bottom of the column – to calculate a grand total.
Aside from simply adding up numbers in a range, SUBTOTAL can also give you the average, maximum, and minimum totals for that range. It’s one of the best time-saving Excel tips and tricks out there!
4. Store your data in tables instead of ranges.
The table feature was introduced to Excel back in 2007. Like the SUBTOTAL function, it still remains one of the best ways to increase efficiency and reduce errors at the same time.
Essentially, the table function lets us turn a regular range into a table. Once data has been saved in a table, we can then use the table’s name in our formulas. Additional data points are automatically included in our formulas since tables auto-expand to include new rows. This auto-expansion feature is what aids in the creation of more accurate workbooks.
This Excel University tip was mentioned in our Journal of Accountancy article. See the article to learn more!
5. Use PivotTables to quickly analyze data.
Using the PivotTable feature allows you to easily evaluate and summarize data once it’s been entered into a table. Instead of having to parse a spreadsheet manually, a PivotTable puts all of your summarized data right in front of you.
Working with PivotTables can even be easier than working with complicated formulas. They can also summarize tons of difficult-to-read data with a companion Pivot Chart and make trends more apparent. For example, if you want to keep track of your daily bank balance so you can see spending habits, PivotTables have you covered!
6. Take advantage of AutoFill and FlashFill.
When it comes to data entry, you’ll inevitably run into instances where manually filling the cells is just too tedious and time-consuming. For example, if you wanted to enter a succession of dates or numbers, that can take a long time if you’re working with a huge spreadsheet. That’s where AutoFill comes in! To use AutoFill, just hover over the plus sign in the bottom right of a cell, and drag it over the cells you want to fill. This saves a ton of time for anyone who works with series of numbers, dates, times, lists, etc.
If you want to save even more time, check out Flash Fill, which is only slightly trickier to learn but much more powerful than AutoFill. It will essentially detect a pattern and fill that pattern down. This can replace complex formulas. For example, you can use Flash Fill to easily split a column of full names into separate first and last name columns. Or combine multiple columns into a single column. You give Excel the first sample value, and then Excel attempts to detect the pattern and fill that pattern down.
7. Use conditional formatting to avoid manually entering formatting information.
Let’s say you have rows of data that keep track of whether or not someone has made a payment. The last column contains a “Yes” if they have and a “No” if they haven’t.
That’s easy to read when you only have a few rows. But when you have hundreds of rows, and you need to quickly find out who’s already paid? Conditional formatting saves the day!
In only a few clicks, you can quickly format those “Yes” or “No” rows to look exactly how you want. If you plan to keep adding data, this conditional formatting can be applied to the full column or row rather than simply a block of cells. That way, the data will be formatted automatically as you enter it in the future!
8. Learn the small, everyday Excel tips & tricks that save you scrolls & keystrokes.
There are plenty of ways to save time in Excel just by making a few simple tweaks to your work process. Once you get in the habit of using them, creating your spreadsheets will become faster and easier.
These are some of our favorites!
- Freeze rows and columns when scrolling. Instead of having to constantly scroll back up or use Ctrl + F to find information to reference, freeze your rows or columns so they’ll stay static as you compare them to others.
- Use charts to quickly display data. Creating a chart typically only takes a few clicks, and it’s a great way to visually summarize all your data.
- Don’t worry about typing functions with uppercase. Despite always being shown in uppercase, you don’t actually have to type them that way. Also, you don’t have to add the final parentheses! Excel does both of those automatically for you.
- Add frequently-used functions to the Quick Access Toolbar. No more having to memorize your most important functions or trying to dig for them in menus where they may be buried. This is a super simple Excel tip that can save you massive amounts of time!
When you first start using Excel, you may find yourself in a situation where you have too much work and too little time. That’s the opposite of what Excel is about! Instead, we want the software to do the hard parts for us (and automate them as much as possible).
There’s always going to be something new to learn, but these Excel tips & tricks will get you on the road to making fast, efficient spreadsheets – without sacrificing any accuracy.
Do you have any other time-saving tips that we didn’t mention? Let us know what they are by posting a comment below!
Excel is not what it used to be.
You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.
Want to learn Excel?
Access all Undergrad and Masters lessons with a Campus Pass or CPE Pass. Includes on-demand training plus live office hours.