Working with spreadsheets can be a time-consuming task. Even if you’re very familiar with Excel, it’s important to make sure that your methods are as efficient as possible, so you’re not wasting any time.
Microsoft has packed Excel with all kinds of different ways to get things done quicker. However, you can’t take advantage of these features if you don’t know about them.
These ten techniques may only save you a few seconds every time you use them. That might not sound like much, but if you can integrate them into your workflow, you’re sure to reap the benefits over time.
1. Edit Cells With F2
Here’s something really simple that will save you time. The most obvious way to edit a cell in your spreadsheet is by double-clicking it. If you’re doing a large amount of data entry, switching from your mouse to your keyboard over and over again isn’t very efficient.
Pressing F2 will allow you to edit the currently highlighted cell. This means that you can navigate around your document and edit cells without ever taking your hands off the keyboard. It might take a little while for you to instinctively reach for the F2 key, but once it becomes second nature, it’s sure to speed up data entry.
2. Split Windows Instead of Freezing Panes
Freezing panes is a handy way to look at several different parts of a spreadsheet simultaneously. However, if you only need to do so temporarily, there’s a faster method.
Head to the View tab, find the Window section and click Split. This will split your window into four separate sections, which you can adjust by dragging the borders in between. It’s a lot more flexible than freezing panes if you just need this kind of overview for a brief period of time.
3. Use F4 to Repeat Processes
If you’re repeating the same task several times, the F4 key can often automate the process. For instance, I might want to delete several rows from this spreadsheet.
I can use Shift + Space to select an entire row at once.
Then I can use Ctrl + – to delete that row.
Now I can simply press F4 to repeat the delete command over and over again.
This technique works with all kinds of different processes. For instance, you can highlight a particular cell, then apply that same style to other regions of your document simply by pressing F4.
4. Insert the Time and Date
It’s not uncommon to need to insert the current date or time into an Excel spreadsheet. With that in mind, Microsoft has made it very easy to do so.
Ctrl + ; will insert the current date into the active cell. Ctrl + Shift + ; will insert the current system time.
5. Use the Alt Key to Fit to Cells
It’s easy to create a chart in Excel, but presenting it properly in your spreadsheet can be a little more difficult. Making your chart line up with surrounding cells looks neat and professional, but there’s no need to waste time orienting it by eye.
Create your chart, then hold Alt when you adjust its size. It will snap to fit with the cells adjacent to it automatically.
This functionality is standard across the Office suite, so if you want a particular element to snap to its surroundings, try holding down the Alt key.
6. Duplicate a Formula Exactly
When you drag the corner of a cell containing a formula, Excel will shift the cell references accordingly. This is very useful in most circumstances, but sometimes it would be more beneficial to insert exactly the same formula.
Go to the cell directly beneath the formula you want to duplicate and press Ctrl + ‘. This will populate the cell with an exact copy of the formula, with no changes to its cell references.
7. Set Up Your Default Spreadsheet Preferences
Many Excel users have a set routine when they create a new document, modifying the font, adjusting the number of sheets, and so on. However, you can adjust a few settings to customize the defaults for a new workbook, and save yourself the hassle of doing this every time.
Head to File > Options. In the General section, look for the settings titled When creating new workbooks.
As you can see above, you change the default font and its size, the default view, and how many sheets a new workbook will include. These are the nuts and bolts of any Excel document, so setting up your preferences is sure to save you time and effort in the long run.
8. Utilize the Quick Access Toolbar
Most Excel users probably use the Quick Access Toolbar. But do you know how to customize it?
Excel gives users access to save, undo, and redo functionality as standard in the top left hand corner of the screen. You can add a whole host of other commands by right-clicking the Ribbon and selecting Customize Quick Access Toolbar.
You can add everything from an easy way to insert charts, to shortcuts that will help you use Windows Ink instantly. It’s well worth sorting through the various options to find what benefits your typical workflow. Tailor Excel to your usage, and you’ll spend less time navigating around the Ribbon.
9. Explain Your Functions
Do you find yourself creating complex Excel spreadsheets, then having to explain how they operate to your co-workers? You might be able to skip this responsibility if you annotate your formulae.
Add the following to the end of a formula to give yourself an opportunity to explain your work:
+N("your comment goes here")
This makes for an unobtrusive way to add some extra insight into what’s going on behind the scenes.
Don’t overuse this functionality, because you can quickly end up introducing too much written information to your document. However, if you expect other users to need an explanation, leaving a quick note can save time later on.
10. Learn These Formatting Shortcuts
Formatting cells so that their contents display properly is a good way to perfect your spreadsheet. However, it can be time-consuming to use the dropdown menu in the Number section of the Ribbon to do so. Instead, try commit these shortcuts to memory:
- Ctrl + Shift + 1 — Format as a number to two decimal places.
- Ctrl + Shift + 4 — Format as currency.
- Ctrl + Shift + 5 — Format as a percentage.
- Ctrl + Shift + 6 — Format as an index number.
These shortcuts might seem difficult to remember, but they correspond with the special characters on a standard keyboard, with the exception of number formatting, which is the de facto default.
To format as currency, just look for the dollar symbol, for a percentage, look for the percent sign, and for an index number (otherwise known as scientific notation), look for the power symbol.
Save Time for the Important Stuff
At its core, Excel is a productivity tool. Its primary objective is to take the legwork out of working with spreadsheets. Creating a workbook might require a certain amount of menial work, but remember there are plenty of ways to speed up various parts of the process.
Expanding your knowledge of Excel isn’t always about learning new functions, or delving into complex functionality like VBA. Committing a shortcut to memory, or figuring out a way of doing a repetitious task quicker can be just as beneficial to your usage.
Do you have a tip on how users can save time and effort in Excel? Or are you looking for more information about one of the techniques in this article? Either way, why not join the conversation in the comments section below?
Image Credit: Volha Nalhachova via Shutterstock.com