Regardless of your profession or the industry you’re in, chances are Excel plays an important part in your daily working routine. However, even though most of use Excel at work nowadays, there are a number of tricks and shortcuts that could save us a considerable amount of time – the problem is, many people don’t know about them.
So, to help you improve your speed and efficiency in using Excel and complete your tasks faster, I have created a list of 12 time saving Excel tips you may not know about below. And, as an added bonus, some of these tips are universal, so they can be used across other Microsoft applications as well.
One of the best ways to speed up your work process and save a considerable amount of time is to use one of Excel’s many keyboard shortcuts. Although there are hundreds of shortcuts available, learning even a few of them will help you increase your productivity in the long run. Below are some of the best – and possibly most important – ones I thought you should know about.
1. Create a chart in seconds
If you’d like to add a chart to your spreadsheet, use the F11 function key, or Alt + F1 if you’re looking for a floating chart. However, always make sure to highlight your data first before using these shortcuts. This article on the Office Tool Tips blog explains how to use this feature in more detail.
2. Navigate between sheets without using your mouse
The best way to quickly move between your worksheets is to use Ctrl + Page Up for moving to the left, or Ctrl + Page Down for moving to the right. This shortcut is especially useful on a laptop or if, like me, you don’t like using the touchpad and you have lots of worksheets to deal with at the same time.
3. Jump to any cell
If you want to go to a particular cell, you can use the F5 key. This will show all the named cells on your worksheet – simply type in a reference like Z100, then press Enter.
4. Open and save excel files quickly
The quickest way to open a file is to use the Ctrl + O shortcut. Similarly, if you want to save your document, you can use either Ctrl + S, or F12 if you want to use the ‘save as’ feature. The latter opens a browser window for you to easily find your files, and allows you to either save the updated file or save it under a different name.
5. Fix formulas with absolute references
The F4 key has two main functions in Excel. If you press F4 but you’re not actually editing a cell, Excel will repeat the last action. However, it can also be used for cycling through all 4 types of cell references.
6. Double click to copy down a formula
If you’d like to continue down a formula, just double click the small square in the bottom right corner of the cell. This trick is especially useful when you have hundreds or thousands of rows using the same formula – just click the square and let Excel do the work for you.
Christy’s article on the Pryor blog explains this – and other alternatives – in more detail.
7. Zoom in and out easily
Another feature many people don’t know is how to zoom in and out. You can zoom in easily by pressing Ctrl and moving the scroll wheel on the mouse up or down. Try zooming to 25% and you will see any named ranges in the worksheet.
8. Format your cells quickly
Instead of right clicking, you can open the ‘Format Cells’ window very easily using the Ctrl + 1 shortcut. Needless to say, not only is this is a handy way to format your data cells in the quickest time possible, but right clicking, then selecting the ‘Format Cells’ option and what exactly you want are all extra mouse clicks you don’t need to do.
9. Shortcuts to everything in Excel
Use the Alt key to activate the Ribbon Tab shortcuts to every command in Excel. For example, Alt + W + VG will switch off the gridlines in your sheet so that it looks more like a report. Although this only applies to Excel 2007 and newer versions of Excel, the beauty of this shortcut is that you don’t need to remember them all, only the Alt key itself.
Other useful tips
10. Pasting across multiple sheets
Want the same data across multiple sheets? Then pre-select all the sheets needed and copy paste or type in the data and every selected sheet will have the same data on it ready for you.
11. Quickly create an excel table
Convert your data to a smart data table using Ctrl + T. This way, you will have striped rows, filtered headings, and the ability to add a “Totals” row.
12. Name cells for easy access
And last but not least, another feature that can save you a considerable amount of time and help you navigate more easily is naming your cells. For example, you can name a cell as VAT or MARKUP and if you need to get to it quickly, simply use the F5 key to go to a cell and your names will be in there.
Apart from single cells, you can also name whole tables of data, or even columns and rows. Find the ‘Name Manager’ in the ‘Formulas’ tab for creating these, or use the name box above column A. Additionally, you can use F3 to paste names into a cell or formula because in a few weeks’ time you may not remember the names you have created.
If you want some more ways to save time, there’s an interesting new section recently launched on the Activia Training website which has how-to videos and free templates for you to download.
Can you think of any other time saving Excel tips you would add to the list? Let us know in the comments below.