30 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, reoganize 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
Here we 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
- Press 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, until now. Now, press F2 and you are able to edit the current cell without the mouse.
- Press ENTER validate the edit and go to the cell below
- Press SHIFT + ENTER validate the edit and go to the cell above
- Press TAB validate the edit and go to the cell on the right
- Press SHIFT + TAB validate the edit and go to the cell on the left
- Press CTRL + ARROW 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!)
- Press 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 of a brutal shortcut. It is useful to go through rows faster than with directional arrows but not quite instantaneously.
Shortcuts to move between worksheets
- Press CTRL + Page up to move to the worksheet on the left
- Press CTRL + Page down to move to the worksheet on the right
Shortcuts to move between currently opened Excel files
- Press CTRL + TAB / CTRL + SHIFT + TAB to go to the next / previous opened files in this Excel session
Shortcuts to move between currently opened applications (Windows shortcut more than Excel actually)
- Press 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...)
- Press ALT + SHIFT + Arrow Left to Ungroup rows or columns
- Press 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:
- Press ALT + ; to Select visible cells only, 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.
- Press F4 to Repeat the previous operation
An alternative is to paste formats only:
- After you copied something, press CTRL + ALT + V then T then Enter : this will display the paste special menu, T will select the “past format” option and ENTER will validate the choice. It is not very elegant, but it works.
There are loads of other shortcuts taken from this article on excel shortcuts:
- CTRL + B: Put the content in the current selection into bold format.
- CTRL + I: Put the content in the current selection into Italic format.
- CTRL + U: Put the content in the current selection into Underlined format.
- CTRL + SHIFT + !: Put the selection into number format
- CTRL + SHIFT + $: Put the selection into currency format
- CTRL + SHIFT + %: Put the selection into percentage format
- CTRL + SHIFT + #: Put the selection into date format
Shortcuts to restructure the data: create table, graph…
Some people need to create quickly graphs and tables to exploit a piece of data.
- Press CTRL + T to convert the current area or the current selection into a data table
- Press F11 to create a graph sheet in the workbook with the currently selected data or the current area
Become an Excel shortcuts designer
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