The sooner you start using keyboard shortcuts in Excel, the better. Not only they save time but often provide much more confidence in one’s actions. There is a shortcut for almost every command and countless cheat-sheets are floating on the internet. I will focus on the combinations that I believe are the most widely applicable.
The Excel Essentials is a series of posts on the most important Excel skills as deemed by me. I consider them the bare minimum that any user should master regardless of the industry or level of involvement with spreadsheets.
Use the file attached to help you exercise the key combinations.
If there is one shortcut you should know, this is it. Pressing [F2] allows you to edit the formula syntax. Not only this but the precedent cells are highlighted so it is easier for you to make sense of what data is used in your calculation.
However, there are two “modes” that can be switched by pressing the [F2] key.
First, you activate the“Edit mode”. In it, you can use the arrow keys on the keyboard to navigate inside the formula itself. You can add to or remove from the syntax but pressing the left or the right arrow will simply move the cursor around.
Nevertheless, you can press [F2] again and go into “Enter mode”. When in it, there are two options:
- Your cursor can be behind a mathematical operator. Then pressing any arrow key will allow you to insert a reference (to a cell or an entire range). You start from the cell next to the one you edit and make your way to the desired one(s).
- If not immediately after a mathematical operator — your will exit editing the formula and will move to the neighbouring one.
You can switch between the two at your will. It takes some time to get used to checking the current mode but once you starting doing so, you achieve another level.
The mode is displayed in the bottom left corner of the window:
Movement in your workbooks is much faster with the keyboard. I constantly use the combinations below and have noticed most people being impressed with the speed I achieve. In fact, this is just a case of people not familiar with the full Excel potential.
[CTRL] + [Any arrow key][CTRL] + [Any arrow key] — To the edge of the data — Holding [CTRL] and pressing an arrow will send you to the end of the data in the direction on the key. If you happen to be already there, you will get to the next block of data (if existing) or to the last row/column in the spreadsheet.
[CTRL] + [Page up/Page down][CTRL] + [PgUp/PgDn] — Go to the previous/next sheet — Usually spreadsheets have at least several sheets. Cycling through adjacent ones is much faster with the keyboard than with the mouse. In my experience, this is true even when working on laptops. Even though on mobile computers the [Page up/down] buttons tend to be small and in awkward positions.
[CTRL] + [Home/End][CTRL] + [Home/End] — Get to the beginning/end of the data — This will take you to the first/last cell with any data (including metadata like formats). The shortcut can be used to Locate and reset the last cell on a worksheet.
[CTRL] + ([Shift]) + [Tab][CTRL] + [Tab] — Cycles through the open workbooks. If you have more than one open Excel window and other applications as well, use this combination. It will switch the Excel workbooks only.[CTRL] + [Shift] + [Tab] — Just like the previous one but the cycle goes in the opposite direction.
When you click in a cell, it is considered a selection. With the shortcuts below, you modify it.
[Shift] + ([Shift]) + [Any arrow key][Shift]+[Any arrow key] — Will increase or decrease your current selection, regardless of it being a single cell or a range, in the direction you hit the arrows.[CTRL] + [Shift]+[Any arrow key] — Just like with the previous combination but following the [CTRL] + [Arrows] logic, it will make the change all the way to the edge of the data.
[CTRL/Shift] + [Space][CTRL] + [Space] — Select the entire column.[Shift] + [Space] — Select the entire row.
If more than one cell is selected, pressing one of the last two shortcuts will select all columns/rows at which the cells are located. This is true as long as it is not a multiple range selection.
The other combinations I consider widely applicable.
[CTRL] + [+/-][CTRL] + [+/-] — Insert/Delete cell; row; or column. — The behaviour of this combination is subject to the current selection.
- If an entire row(s)/column(s) is selected (as with the [CTRL/Shift]+[Space] shortcut), a new one will be inserted before it or the selected one will be removed.
- If the selection consists of a range of cells, a dialogue box will pop-out. You will be asked if you want to shift cells up or left; or if you want to delete the entire row(s)/column(s).
[CTRL] + [Alt] + [V] — Paste Special[CTRL]+[Alt]+[V] — Paste Special — The window that opens up is a powerful tool with many options that will be covered in a separate post. Long story short — it allows you to strip certain type of data or format from your clipboard and paste only it. Further, you can use the information to do mathematical operations with it while pasting.
Fun fact — Paste special is a running joke among me and some friends & coworkers due to the sometimes forced overuse of paste special values. This is the reason why this blog is called Excel Special. Also, I needed a domain with Excel in the name because of SEO...
[F4][F4] — Repeat the last action — For example, you might have just applied a format to some cells. However, you have to do the same 10 more times. Don’t set it all up every time. You can select the non-formatted cells and apply the same set of rules again in an instant.
Further, when you construct a formula, pressing this button after a cell/range will change the type of the reference made.
[CTRL] + [CTRL] +  — Open the Format Cells… dialogue box. Never right-click again. You don’t know how much time you lose trying to achieve the same via the context menu.
[F9][F9] — Calculate — We might argue that the [F9] key is not essential. Let me explain why I include it here:
- In the normal course of action, the [F9] triggers recalculation of the formulas. This is useful if the automatic calculations have been switched off.
- However, when working in the formula bar, this button shows its potential. If you select part of the formula and press [F9], it will be calculated and the result will replace the selection. This applies if you pick a cell/range reference or an entire function. You save an enormous amount of time by not travelling around your workbooks and checking the underlying values.
On Windows (for which I obviously write) there are plenty of shortcuts that are standardized and have the same behaviour not only in MS Office but most applications as well. In the unlikely case that you never use key combinations, here are some of them that you must memorize:[CTRL] + [A] — Select all.[CTRL] + [Z] — Undo.[CTRL] + [X] — Redo.[CTRL] + [S] — Save file. Use it regularly and make habit of it.[CTRL] + [W] — Close workbook.[CTRL] + [C] — Copy cell/range. (I really wonder if there is someone who doesn’t know this one?)[CTRL] + [V] — Paste. I have become so accustomed to using [CTRL] + [Alt] + [V] that I don’t use this one anymore.[CTRL] + [F] — Find.[CTRL] + [H] — Replace. I recently saw a seasoned manager having the replace command as a shortcut on the Quick Access Toolbar so maybe this is not as popular as I expect it to be.
Do you need more?
There are hundreds more useful shortcuts that can save time. Check:
- Microsoft’s own Keyboard shortcuts in Excel.
- Chandoo’s Comprehensive List of Excel Keyboard Shortcuts (the author being one of my favourite bloggers).
- Or that list with 333 combinations that is the longest I have came across.
Do you need to know them all? No.
Do you need to know all of them that are useful to you? Also no. In fact, every time I skim through such a list, I find at least several combos that I can use daily. However, start with a handful of them and push it as far as you can while taking your time.
Bonus round: Consider removing keys from your keyboard
One of the things I do that puzzles other people is removing some keys from my keyboard. This turned out to be one of the most useful tips that I read a billion years ago when I was starting to use Excel as part of my job.
The reason for this is obvious — regardless of how often you use shortcuts, chances are you are not always going to hit them correctly. Popping-out a key not only completely removes the chance of pressing it but can serve as a guide for your fingers as well.
The keys I opt to remove
I am usually popping-out the following buttons:
- [F1] — This button is the worst. It sits right next to the constantly employed [F2] and summons (very slowly) Excel’s help. Don’t allow such disturbance in your concentration. Furthermore, if [F2] becomes the left-most F‑key, finding it when not looking in the keyboard is much easier even if you have significant experience.
- [Insert] — The key activates the overtype mode in which the text you enter goes over the existing characters instead of pushing them forward. I can see some use cases but given that in Excel a great portion of the time is spent editing formulas, this button is more likely to cause inconvenience.
- [Scroll Lock] — If you press it, the arrow keys will no longer change the selected cell. Instead, the entire sheet will move in the direction you pressed (with no change in the selection). I consider this annoying.
- [Caps Lock] — I wouldn’t go as far as advocating complete [Caps Lock] abolition but when it comes to Excel (and my office life altogether) I don’t see any benefit of having it. Extracting it also makes hitting [Shift] more certain.
Due to purely mechanical reasons removing keys does not apply to my laptop. However, I try hard to work in Excel on a desktop where more real estate is available for both keyboard and screen — a setup in which tunning your mechanics is possible.