I once worked with an amazing consultant who could control all of Excel without using a mouse. It was extraordinary watching the speed at which she could manipulate and move data. While most of us do not need to achieve this level of Excel jujitsu, there are a few shortcut keys that you can use that will make you far more productive when using Excel.
1. F2 – Edit a Cell
Use this to edit the formula in a cell.
2. F4 – Repeat Last Action and Lock Cell References
2.1 Repeat last action:
The first use case for F4 is to repeat the last action. A common example is when you insert a row ‘ALT+I+R’ and then you want to add another row again. If you want to add an additional 3 rows, press ‘F4’ three times and it will insert 3 more rows for you.
2.2 Lock down cell references
When you are editing a formula, you may want to ensure that the cells you use do not change. This can be done manually by adding a ‘$’ sign in front of the cell reference to ensure it does not change. There are 3 different types of cell reference – relative, mixed and absolute. For example C2 (relative reference) will move when you copy the cell to a new location. $C2 (mixed reference) locks the C column but allows the rows to change, whereas $C$2 (absolute reference) locks both the column and the row. For more see Cell References.
You can do this much faster when editing a cell by pressing F4 when the cursor is over the reference and cycling through the options. I most commonly lock down cell references when using a VLOOKUP, where I want an absolute reference to the lookup table.
3. CTRL+ALT+V – Paste Special
This is one of my favourite shortcut keys. Note that when the Paste Special dialog opens, there are lines under the letters of the functions as per the below image. For example there is a line under the ‘v’ in ‘Values’. This is the shortcut key to execute that operation. Most commonly, I will use ‘u’ for the ‘Values and number formats’ option, so the full set of key strokes will be Ctrl+Alt+V+U. Note that Alt+E+S is also an alternative to Ctrl+Alt+V. For more information on paste special shortcuts see Paste Special.
4. CTRL+Arrow – Navigate the workbook
I often find that I need to get to the bottom of a column or the start of the sheet.
CTRL+↓ – Navigates to the bottom of a list
CTRL+end – Navigates to the last cell in the sheet
CTRL+home – Naviagates to the first cell in the sheet
If you want to select the region, you can use CTRL+SHIFT+Arrow. If you want to select everything in the sheet, then use CTRL+A.
5. CTRL + SHIFT + L – Toggle Autofilter
Often you will need to add a filter to your data to search a particular column. Select the header cells you want to add a filter to (hold down SHIFT and move your arrow across the headers), then press CTRL + SHIFT + L. You can toggle the filter off by pressing CTRL + SHIFT + L again.
6. CTRL + ~ – Show Formulas
If you quickly want to see all the formulas on a sheet to debug or understand what the sheet is doing, then you can do this by using CTRL +
~ (this is the accent key near the number 1.)
7. CTRL+SHIFT+! – Comma Number Format
Often you will want to see commas on larger numbers, use CTRL+SHIFT+1 for this. Other formats include:
CTRL+SHIFT+! – Comma format
CTRL+SHIFT+$ – Dollar format
CTRL+SHIFT+% – Percentage format
CTRL+SHIFT+^ – Scientific format
CTRL+SHIFT+~ – General format
If you wan to bring up the format cell dialog with all options, press CTRL+! .
8. CTRL+S – Save
There are a few shortcuts for working with files that are handy. It is always a good habit to save work as you progress, so CTRL+S is an easy way to save your file.
CTRL+S – Save
F12 – Save As
CTRL+O – Open file
9. CTRL+F3 – Open the Name Manager
This shortcut is important for when you need to keep a track of the named values for a cell. By using names, you can make your formulas much easier to understand and maintain e.g. instead of referencing a variable $C$31, you can name the value something you recognize, such as “interest_rate”. A named value in a cell can be set by entering a text name in the top left box of the screen, for more see Use Names in Formulas.
10. F9 – Calculate Formula
The F9 key refreshes the workbook and calculates formulas. Sometimes you need to turn off automatic calculations when your workbook becomes too slow. Use the Tools | Options command, switch to the Calculate tab, then select Manual. Every time you want to recalculate, you can do so with F9.
That wraps up the 10 most useful Excel shortcut keys for Business Analytics. There are many more shortcut keys you can use to increase your productivity in Excel. If you are interested in even more Excel shortcut keys, see below:
- A cheat sheet for excel shortcut keys from MIT, see Hotkeys.
- For even more shortcut keys see 222 Excel Shortcuts.