20 advanced Excel shortcuts that will transform you into an Excel Ninja
I have gathered the most powerful Excel shortcuts by themes, so you can use the synergies between them and leverage their impressive productivity improvement power! Move though the worksheet, edit, reorganize and format the data without even touching the mouse!
After a previous article on the most useful Excel shortcuts by my standards, I decided to make a second article about 10 more specific Excel shortcuts that can provide amazing value to an analyst but that are harder to find on the internet.
I have tried to gather shortcuts by theme so you can build-up a cluster of highly efficient shortcut reflexes.
Shortcuts to move quickly in the spreadsheet without ever using the mouse, like a ninja
With the shortcuts below, you will be able to jump through the spreadsheet, do modifications, then move through opened workbooks, files and even applications without ever using the mouse.
Shortcuts to edit the data and move
- F2 to enter into the selected cell to edit it. When you are selecting a cell, you usually have to click on it again to be able to modify its content. It is easy to move between cells but actually editing the cell required the mouse. Modifying the content in many different cells was actually very time consuming, until now. Now, just press F2 when you are selecting the cell and you are able to edit the cell without having to click on it.
- ENTER to validate the edit and go to the cell below
- SHIFT + ENTER to validate the edit and go to the cell above
- TAB to validate the edit and go to the cell on the right
- SHIFT + TAB to validate the edit and go to the cell on the left
- CTRL + ARROW (up/down/left/right) to move rapidly into your worksheet. This is some serious ninja shortcut! With this you can dash through the sheet in a glimpse of an eye!
- If the active cell has some content, this will send you to the end of the area with content in either direction you decide with the arrow keys.
- If the active cell is empty, or the adjacent cell is empty in the direction you are moving to, you move to the next block of content or to the edge of the worksheet (that can be pretty far!)
- Page Up / Page down to move quickly up or down. Page up and down are similar to arrows up and down, except you move by much more than one cell each time you press it. The distance isn’t linked to the content like in CTRL + ARROW, so it is less brutal. It is useful to go through rows faster than with directional arrows but not quite instantaneously.
Shortcuts to move between worksheets
- CTRL + Page up to move to the worksheet on the left
- CTRL + Page down to move to the worksheet on the right
Shortcuts to move between currently opened Excel files
- CTRL + TAB / CTRL + SHIFT + TAB to go to the next / previous opened files in this Excel session
Shortcuts to move between currently opened applications (a Windows shortcut actually)
- ALT + TAB to open a mosaic of the currently running application windows and you can then navigate by pressing TAB again to go to the next application
Shortcuts to reorganize the Excel spreadsheet in a second
Reorganizing a spreadsheet by adding columns and rows, hiding and grouping them are quite “mouse-intensive” operations. But it is actually among the easiest shortcuts to use. I won’t go through the CTRL + Plus (+) and CTRL + Minus (-) to add and delete rows, cells and columns, as I already did in the previous more general Excel shortcuts article.
Shortcuts to hide rows
There are two way to hide rows: shrink their height to 0, or “group” them. I tend to prefer the grouping option, as it is more transparent (you are a small + and – button to ungroup and group again, you can do subgroups...)
- ALT + SHIFT + Arrow Left to Ungroup rows or columns
- ALT + SHIFT + Arrow Right to Group rows or columns
- CTRL + 9 : Hide row (note that for this shortcut and the others, the 9 is not the one from the digital pad, but one instead the key that would output a “(“ if you press SHIFT, which is in the top row of keys on the keyboard)
- CTRL + 0 : Hide Column
- CTRL + SHIFT + 9 : Unhide row
- CTRL + SHIFT + 0 : Unhide Column
Then the following is a must-remember shortcut: copy only the visible cells:
- ALT + ; to select visible cells only from the current selection, which is very useful to exclude from the copy the cells that you just grouped or hid!
Shortcuts to become a formatting wizard
The following shortcut is a must-remember: repeat the previous action on the current selection is extremely useful to repeat a specific formatting (even if it can be used in loads of other cases). In the current use, it is similar to “paste format” except it only reproduces the last operation of formatting, and you don’t need to copy first.
- F4 to repeat the previous operation. This is useful to apply the same formatting repetitively.
There are loads of other shortcuts to apply predefined formatting, but they are already described there, so I won't repeat myself here.
Shortcuts to restructure the data: create table, graph…
Some people need to create quickly graphs and tables to exploit a piece of data.
- CTRL + T to convert the current area or the current selection into a data table
- F11 to create a graph sheet in the workbook with the currently selected data or the current area
Become an Excel shortcuts designer
Use the "accessibillity" shorcuts using ALT+...
Excel doesn’t provide shortcuts for everything. Or so it seems. Actually there is a way to access nearly any feature of the Excel ribbon using exclusively the keyboard. The relevant key sequences are not strictly speaking shortcuts, but provide mouse-free accessibility, which is crucial for visually-impaired people.
All these shortcuts start with ALT. If you press ALT now, you will see little tags or labels appear on the Excel ribbon.
For example ALT + A + M will run the remove duplicate action on the current selection.
In the example above, you can also infer that ALT + 1 will save the document and ALT + 2 will cancel the previous action. These “shortcuts” are clearly redundant with the CTRL + S (Save) and CT
Shortcuts that trigger specific macros
For VBA lovers among you, remember that VBA allows you to assign a shortcut to you macros.