We live in a world of data which must be processed and analyzed fast. With the majority of people in the business world agreeing that there isn’t enough time in the day or week to accomplish all their goals, looking at time-saving and productivity has now become a very regular feature of many organizations.
A very good place to start is by looking at the daily habits within our workplace tools. And Excel, of course is a very used tool. In fact, Excel is the most used tool worldwide for dealing with data with over 750 million users every day.
So with that in mind, the setting is set for mastering a good list of keyboard shortcuts in Excel that will certainly save you some time.
1. Turn On/Off Autofilter
Filtering in a simple yet very powerful tool in Excel to analyze data. It offers the possibility to filter your data by a particular value, by values containing a defined string, by values greater/lower to another value and many more options. The traditional way to apply filters on selected columns is by going to the Data Menu and then select Filter.
The same action needs to be repeated to turn the Filter off, which can be a bit tiresome when using them very frequently. But the same result can be obtained by just pressing CTRL+SHIFT+L in our keyboard. Magic!
2. Edit a cell
Mistakes happen in spreadsheets. This usually calls for editing the content in a cell or set of cells. This content can be text, a number or a formula. To edit a cell you drag the mouse pointer to the formula bar and then press click in the desired location to start editing.
Doing it once is possibly okay, but when you have to edit lots of cells and becomes a regular action, it can be a really tedious process. The good news is that you can achieve the same by just clicking F2 in any cell and edit the selection text fast. This would also apply to cells containing formulas.
3. Add Hyperlink
Hyperlinks are references to a document, a location or an action that the reader can directly follow by selecting the link. These can open a file, a web page, send an email or jump to another destination cell within the workbook.
They can really make spreadsheets pretty, resourceful and tidy. Normally, to insert a hyperlink in any cell by going to the Insert Menu and select Hyperlink. However, this can be achieved even quicker with just a quick CTRL+K.
4. Show Defined Names
It is very useful to define names when you are working with complex spreadsheets and/or dashboards. They help understand and maintain the formulas used. You can define a name for a cell range, a function, a constant or a table. A good shortcut to use all the defined names is F3.
You can press F3 no matter where you are in a spreadsheet (conditional formatting, data validation, formula editing, etc.) and it will bring the Paste Name Dialog box from where you can pick a name for your desired action. Once you use it once, you will never stop using this shortcut.
5. Paste Special
The Paste Special dialog box allows you to copy items from a worksheet and paste them into the same or different worksheet using only specific attributes of the copied data, or a mathematical operation that you want to apply to the copied data. Among the attributes you can copy are values, formulas, formats, comments, validation, and many more.
To open the Paste Special dialog box you need to right click on the destination cell and Paste Special. But you can save one step by just clicking CTRL+ALT+V and get the same result!
6. Toggle Cell Reference Style While Editing Formulas
Sometimes you need to change the reference of a formula, whether it is from absolute to partially absolute or relative. To do so you need to type the correct $ in the correct side of the reference (for example, A2, A$2, $A2, $A$2), or you could just press F4, while editing the formula (after pressing F2).
This shortcut would toggle cell references from relative to absolute, to partially absolute, back to relative again. As you can imagine, it’s much faster and easier than typing $ characters manually!
7. Navigate Around the Workbook and Select a Group of Cells
A very quick way to navigate through a spreadsheet is using the CTRL+ Arrow keys shortcut. This will move to the edge of the current data region within a worksheet, depending on the pressed arrow. For example, let’s say you have a table with data in the range A1:D10, and you are in cell A2, pressing CTRL+ Right Arrow would jump to the cell D2.
Also, pressing CTRL+SHIFT+ Right Arrow would select all the cells from A2 to D2.
8. Format Cells
Formatting is one of the core actions in Excel. When you format cells in Excel, you change the appearance of the content in a cell without changing the content itself. You can apply a number format (0.1, $0.10, 10%, etc) or other formatting (alignment, font, border, colour etc).The best way to format all aspects of a cell is to bring the Format Cells Dialog Box, and for this you normally have to right-click and then format cells.
9. Open Visual Basic for Applications Editor
Sometimes you have spreadsheets with macros. A macro can be defined as the recording of a series of tasks. It’s the simplest form of automation. To view any aspects of the macros in the workbook you need to open the VBA editor. The standard way to open it is to go to the Developer Menu (it needs installing first) and then go to Visual Basic. Instead, you can just click Alt + F11 (as the image shows) and save yourself the hassle.
10. Fill Right and Fill Down
All users in Excel are aware that they can copy and paste cells, but a lot of times you need to do it quickly. To achieve this there are two simple keyboard shortcuts that let copy cells quickly.
You can move the cursor to a cell that you want to copy. Suppose you want to copy it down 3 times. Hit Shift+Down Arrow 3 times to select the 3 cells below the current one. Then press Ctrl+D. Whatever content is in the first cell is copied to fill the remaining cells. (Any content that was in those cells gets overwritten.)
You can do the very same thing to the right with Shift+Right Arrow and Ctrl+R.
_Oscar Toledano has been passionate about Excel ever since he first used it. Head over to his blog Wizard of Excel for Excel information, techniques, tips, and more._