Excel essentials — Keyboard shortcuts

This entry is part 3 of 3 in the series Excel — The Essentials

The soon­er you start using key­board short­cuts in Excel, the bet­ter. Not only they save time but often pro­vide much more con­fi­dence in one’s actions. There is a short­cut for almost every com­mand and count­less cheat-sheets are float­ing on the inter­net. I will focus on the com­bi­na­tions that I believe are the most wide­ly applicable.


The Excel Essen­tials is a series of posts on the most impor­tant Excel skills as deemed by me. I con­sid­er them the bare min­i­mum that any user should mas­ter regard­less of the indus­try or lev­el of involve­ment with spreadsheets.


Use the file attached to help you exer­cise the key combinations.


[F2]
Excel F2 Key

If there is one short­cut you should know, this is it. Press­ing [F2] allows you to edit the for­mu­la syn­tax. Not only this but the prece­dent cells are high­light­ed so it is eas­i­er for you to make sense of what data is used in your calculation.

[F2] (again)

How­ev­er, there are two “modes” that can be switched by press­ing the [F2] key.

First, you acti­vate the“Edit mode”. In it, you can use the arrow keys on the key­board to nav­i­gate inside the for­mu­la itself. You can add to or remove from the syn­tax but press­ing the left or the right arrow will sim­ply move the cur­sor around.

Nev­er­the­less, you can press [F2] again and go into “Enter mode”. When in it, there are two options:

  • Your cur­sor can be behind a math­e­mat­i­cal oper­a­tor. Then press­ing any arrow key will allow you to insert a ref­er­ence (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 imme­di­ate­ly after a math­e­mat­i­cal oper­a­tor — your will exit edit­ing the for­mu­la and will move to the neigh­bour­ing one.

You can switch between the two at your will. It takes some time to get used to check­ing the cur­rent mode but once you start­ing doing so, you achieve anoth­er level.

The mode is dis­played in the bot­tom left cor­ner of the window:

Navigation

Move­ment in your work­books is much faster with the key­board. I con­stant­ly use the com­bi­na­tions below and have noticed most peo­ple being impressed with the speed I achieve. In fact, this is just a case of peo­ple not famil­iar with the full Excel potential.

[CTRL] + [Any arrow key]
Control + Arrow keys Excel keyboard shortcut
[CTRL] + [Any arrow key] — To the edge of the data — Hold­ing [CTRL] and press­ing an arrow will send you to the end of the data in the direc­tion on the key. If you hap­pen to be already there, you will get to the next block of data (if exist­ing) or to the last row/column in the spreadsheet.

[CTRL] + [Page up/Page down]
Control + Page Up / Page Down Excel keyboard shortcut
[CTRL] + [PgUp/PgDn] — Go to the previous/next sheet — Usu­al­ly spread­sheets have at least sev­er­al sheets. Cycling through adja­cent ones is much faster with the key­board than with the mouse. In my expe­ri­ence, this is true even when work­ing on lap­tops. Even though on mobile com­put­ers the [Page up/down] but­tons tend to be small and in awk­ward positions. 

[CTRL] + [Home/End]
Control + Home/ End Excel keyboard shortcut
[CTRL] + [Home/End] — Get to the beginning/end of the data — This will take you to the first/last cell with any data (includ­ing meta­da­ta like for­mats). The short­cut can be used to Locate and reset the last cell on a work­sheet.

[CTRL] + ([Shift]) + [Tab]
Control + Tab (+ Shift) Excel keyboard shortcut
[CTRL] + [Tab] — Cycles through the open work­books. If you have more than one open Excel win­dow and oth­er appli­ca­tions as well, use this com­bi­na­tion. It will switch the Excel work­books only.[CTRL] + [Shift] + [Tab] — Just like the pre­vi­ous one but the cycle goes in the oppo­site direction.
Selection

When you click in a cell, it is con­sid­ered a selec­tion. With the short­cuts below, you mod­i­fy it.

[Shift] + ([Shift]) + [Any arrow key]
Control + Shift + Arrows Excel keyboard shortcut
[Shift]+[Any arrow key] — Will increase or decrease your cur­rent selec­tion, regard­less of it being a sin­gle cell or a range, in the direc­tion you hit the arrows.[CTRL] + [Shift]+[Any arrow key] — Just like with the pre­vi­ous com­bi­na­tion but fol­low­ing the [CTRL] + [Arrows] log­ic, it will make the change all the way to the edge of the data.

[CTRL/Shift] + [Space]
Control + Space Excel keyboard shortcut
[CTRL] + [Space] — Select the entire column.

Shift + Space Excel keyboard shortcut
[Shift] + [Space] — Select the entire row.

If more than one cell is select­ed, press­ing one of the last two short­cuts will select all columns/rows at which the cells are locat­ed. This is true as long as it is not a mul­ti­ple range selection.

Others

The oth­er com­bi­na­tions I con­sid­er wide­ly applicable.

[CTRL] + [+/-]
Control + Plus/Minus Excel shortcut
[CTRL] + [+/-] — Insert/Delete cell; row; or col­umn. — The behav­iour of this com­bi­na­tion is sub­ject to the cur­rent selection.
  • If an entire row(s)/column(s) is select­ed (as with the [CTRL/Shift]+[Space] short­cut), a new one will be insert­ed before it or the select­ed one will be removed.
  • If the selec­tion con­sists of a range of cells, a dia­logue 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
Control + Alt + V Paste special Excel shortcut
[CTRL]+[Alt]+[V] — Paste Spe­cial — The win­dow that opens up is a pow­er­ful tool with many options that will be cov­ered in a sep­a­rate post. Long sto­ry short — it allows you to strip cer­tain type of data or for­mat from your clip­board and paste only it. Fur­ther, you can use the infor­ma­tion to do math­e­mat­i­cal oper­a­tions with it while pasting.
Excel - Paste Special dialog box.

Fun fact — Paste spe­cial is a run­ning joke among me and some friends & cowork­ers due to the some­times forced overuse of paste spe­cial val­ues. This is the rea­son why this blog is called Excel Spe­cial. Also, I need­ed a domain with Excel in the name because of SEO...

[F4]
Excel F4 Key
[F4] — Repeat the last action — For exam­ple, you might have just applied a for­mat to some cells. How­ev­er, you have to do the same 10 more times. Don’t set it all up every time. You can select the non-for­mat­ted cells and apply the same set of rules again in an instant.

Fur­ther, when you con­struct a for­mu­la, press­ing this but­ton after a cell/range will change the type of the ref­er­ence made. 

[CTRL] + [1]
Control + 1 Excel shortcut
[CTRL] + [1] — Open the For­mat Cells… dia­logue box. Nev­er right-click again. You don’t know how much time you lose try­ing to achieve the same via the con­text menu.

[F9]
Excel F9 Key
[F9] — Cal­cu­late — We might argue that the [F9] key is not essen­tial. Let me explain why I include it here:
  • In the nor­mal course of action, the [F9] trig­gers recal­cu­la­tion of the for­mu­las. This is use­ful if the auto­mat­ic cal­cu­la­tions have been switched off.
  • How­ev­er, when work­ing in the for­mu­la bar, this but­ton shows its poten­tial. If you select part of the for­mu­la and press [F9], it will be cal­cu­lat­ed and the result will replace the selec­tion. This applies if you pick a cell/range ref­er­ence or an entire func­tion. You save an enor­mous amount of time by not trav­el­ling around your work­books and check­ing the under­ly­ing values.

Universal ones

On Win­dows (for which I obvi­ous­ly write) there are plen­ty of short­cuts that are stan­dard­ized and have the same behav­iour not only in MS Office but most appli­ca­tions as well. In the unlike­ly case that you nev­er use key com­bi­na­tions, 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 reg­u­lar­ly and make habit of it.

[CTRL] + [W] — Close workbook.

[CTRL] + [C] — Copy cell/range. (I real­ly won­der if there is some­one who does­n’t know this one?)

[CTRL] + [V] — Paste. I have become so accus­tomed to using [CTRL] + [Alt] + [V] that I don’t use this one anymore.

[CTRL] + [F] — Find.

[CTRL] + [H] — Replace. I recent­ly saw a sea­soned man­ag­er hav­ing the replace com­mand as a short­cut on the Quick Access Tool­bar so maybe this is not as pop­u­lar as I expect it to be.

Do you need more?

There are hun­dreds more use­ful short­cuts that can save time. Check:

Do you need to know them all? No.
Do you need to know all of them that are use­ful to you? Also no. In fact, every time I skim through such a list, I find at least sev­er­al com­bos that I can use dai­ly. How­ev­er, start with a hand­ful of them and push it as far as you can while tak­ing your time.

Bonus round: Consider removing keys from your keyboard

One of the things I do that puz­zles oth­er peo­ple is remov­ing some keys from my key­board. This turned out to be one of the most use­ful tips that I read a bil­lion years ago when I was start­ing to use Excel as part of my job. 

The rea­son for this is obvi­ous — regard­less of how often you use short­cuts, chances are you are not always going to hit them cor­rect­ly. Pop­ping-out a key not only com­plete­ly removes the chance of press­ing it but can serve as a guide for your fin­gers as well.

Keyboard layout with marked the keys that can be removed to make use of Excel shortcuts easier.
The keys I take out from the keyboard.
The keys I opt to remove

I am usu­al­ly pop­ping-out the fol­low­ing buttons:

  • [F1] — This but­ton is the worst. It sits right next to the con­stant­ly employed [F2] and sum­mons (very slow­ly) Excel’s help. Don’t allow such dis­tur­bance in your con­cen­tra­tion. Fur­ther­more, if [F2] becomes the left-most F‑key, find­ing it when not look­ing in the key­board is much eas­i­er even if you have sig­nif­i­cant experience.
  • [Insert] — The key acti­vates the over­type mode in which the text you enter goes over the exist­ing char­ac­ters instead of push­ing them for­ward. I can see some use cas­es but giv­en that in Excel a great por­tion of the time is spent edit­ing for­mu­las, this but­ton is more like­ly to cause inconvenience.
  • [Scroll Lock] — If you press it, the arrow keys will no longer change the select­ed cell. Instead, the entire sheet will move in the direc­tion you pressed (with no change in the selec­tion). I con­sid­er this annoying.
  • [Caps Lock] — I would­n’t go as far as advo­cat­ing com­plete [Caps Lock] abo­li­tion but when it comes to Excel (and my office life alto­geth­er) I don’t see any ben­e­fit of hav­ing it. Extract­ing it also makes hit­ting [Shift] more certain.

Due to pure­ly mechan­i­cal rea­sons remov­ing keys does not apply to my lap­top. How­ev­er, I try hard to work in Excel on a desk­top where more real estate is avail­able for both key­board and screen — a set­up in which tun­ning your mechan­ics is possible.

Series Nav­i­ga­tion« Excel essen­tials — The Inter­face And Its Customization