International Keyboard Shortcut Day 2018: The Ultimate Excel 2016 keyboard shortcut list

To download the list, click the button below. Please feel free to share it, but please do not remove the attribution. Read on for an explanation of how it all works.


Yeah, I know, I’m a little late. Not late for IKSD, but late for posting an Excel 2016 keyboard shortcut list. Come on, it’s 2018 already, and Excel 2019 has already been foisted upon the masses, which just again shows how marketing screws everything up–the same way retail stores are putting up their Christmas decorations at the same time they are taking down their halloween decorations (i.e., right about now), way before most of us are even thinking of Christmas, but rather just trying to get through the inevitable pile-up of work that accumulates at the end of each year.

But I digress. I think this list will still be fine for Excel 2019/Excel 365, and to commemorate International Keyboard Shortcut Day, I am sharing this on my blog. I made this list for a book I was writing on Excel (which has stalled a bit, due to my life circumstances changing), and I modified it considerably in prepation for a presentation I did for Danielle Stein Fairhurst’s Financial Modelling in Excel Meetup group in Sydney.

The ultimate Excel keyboard shortcut list

Now, let me defend my claim. Just do a search for “ultimate Excel keyboard shortcut list” and you will see that there are many claimants to that title, but I am going to be even more brash, and claim to have the ultimate, ultimate list.

Why do I say this?

  1. My list is the only list which is provided as an Excel workbook. Go figure. Imagine creating a list of Excel keyboard shortcuts, and not doing it in Excel? In fact, why do any list of keyboard shortcuts and not do it in Excel? Show some dedication to the cause! Excel is great for lists like this, because it provides us with a searchable, sortable, filterable list.
  2. My list is the most complete list I could find. Really. I challenge you to find more Excel keyboard shortcuts in one location than in my workbook.
  3. My list covers both Windows and Mac. I am not a routine Mac user, but I took a Macbook and worked through every keyboard shortcut there. I am aware that there might be some discrepancies between a desktop Mac and a Macbook, and I confess I did not dig into these, if there is one shortcoming.
  4. The extra information I provide on the keyboard shortcuts. See below.

How the list works

The worksheet has 11 columns, and it is the combination of information across these columns that I consider to be so valuable:

  • Access
  • Key
  • Keyboard shortcut Windows
  • Keyboard shortcut Mac
  • Function
  • Group
  • Subgroup
  • Scope
  • Contextual variant?
  • Toggle
  • Comment

Here is a short explanation of what the purpose of each of the columns is, in a slight re-ordering of their position on the worksheet.

Keyboard shortcut Windows & Mac

These two columns (C and D) contain the actual keyboard shortcuts. It might be worth noting that, for the Windows shortcuts, where keys on a standard keyboard layout contain two symbols that are governed by the Shift key (e.g., the number keys, or ” ‘), I have used the symbol which will be entered if that key is entered with/without the Shift key, depending on whether Shift is used in the shortcut (see Access below). This is evident even with keyboard shortcuts using letters, for example, Ctrl+a and Ctrl+Shift+A.

Access & Key

These two columns help with sorting and filtering. Essentially, most keyboard shortcuts (with the exception of function keys) consist of a normal key on the keyboard (which is listed in column B: Key), and a combination of one, two or three “access” keys (which are listed in column A: Access).
While these columns may seem to be superfluous in that this information is already in column C: Keyboard shortcut, I have elaborated a bit by, for example, listing both symbols found on a single key on a standard Windows keyboard layout. These columns are also useful in that it makes it slightly easier to see, for example, which keyboard shortcuts use a certain access key (granted, Excel’s filter allows that to be done on column C as well), or which keyboard shortcuts make use of a certain key (a little harder, but not impossible, to do with the filter on column C).

Function

This column describes the actual action performed by the keyboard shortcut, or, if it does different things in different contexts, then it elaborates those differences. Here, again, I believe my list has a lot more detail than other lists out there.

Group & Subgroup

Some keyboard shortcut lists are too rigid: Ctrl+A does this, Ctrl+B does that, Ctrl+C…. The better lists organise the keyboard shortcuts into various themes, and Group and Subgroup in columns F:G are my themes. I have 13 top level groups in column F, which are subdivided into 39 second level groups in column G (although 7 of the top level groups are not subdivided, and count as 1 each in that list of 39). To see my breakdown, go to the Lists worksheet. I have used some elementary conditional formatting (horizontal borders) to indicate the different groups (solid border) and subgroups (dotted border).

Scope

Keyboard shortcuts often work in a variety of contexts. It is useful to know this, because if you know that a keyboard shortcut works in more contexts than only the program where you learnt it, then you could be adventurous and try it in other programs, and so extend the time-saving benefit of that keyboard shortcut. I try to indicate this here using one of three different scopes:

  • Excel: This keyboard shortcut works like this only in Microsoft Excel (e.g., Ctrl+E for Flash Fill)
  • Office system: This keyboard shortcut works like this for all (or most) of the Microsoft Office programs (e.g., Ctrl+H for Replace, which is Ctrl+R in many other programs)
  • System: This keyboard shortcut works like this for most programs (e.g., Ctrl+C for Copy).

Contextual variant?

The aim of this column is to indicate whether the same shortcut does different things when used in different contexts. I have simplified things by using a formula to count whether the shortcut appears more than once in the list (which is indicative of such variations), but there are some exceptions, where I have entered the contextual variations all in one row. These are set manually, and indicated using some conditional formatting.

Toggle

Some keyboard shortcuts work like switches: Do it once, and it turns a feature on. Do it again, and the feature is turned off (e.g., Ctrl+B for Bold formatting). This is indicated in this column (column J).

Comment

As if all of the above was not enough, in this last column (column K), I provide some additional insights for a few keyboard shortcuts.

Duplicate shortcuts

There is no column for this, but some shortcuts are indicated in grey formatting across the row. Where you encounter this it will be on the second (or third) duplicate shortcut. I have sorted the list in a specific way, so that the shortcuts I commonly use for a task are listed first, and where more than one shortcut exists for a specific task, the preferred shortcut (IMHO) is listed first, and not in grey, and subsequent shortcuts that do the same thing are shown in grey. Ctrl+B vs Ctrl+2 for Bold formatting is an example of this.

Some interesting stats

As you will see from the explanations above, it becomes an interesting exercise to count keyboard shortcuts. Do we count contextual variants (the same shortcut that does different things in different contexts) once, or once for each variation? Do we count duplicate shortcuts (another shortcut that performs the same function as an existing shortcut) or exclude them? And some things are not really true shortcuts (Ctrl+Shift+Enter is an example). My list has 255 rows. If we exclude the variants and the duplicates, we can reduce that down to 207. Comparing OS versions of Excel, 205 of those work in Windows (i.e., two are unique to Mac), and only 147 work for Mac. Is the Mac OS inherently more keyboard-unfriendly, or did Microsoft neglect it’s duty in compiling keyboard shortcuts for Excel for Mac? It is noteworthy the amount of confusion Mac introduces with Ctrl vs Cmd, as some shortcuts work with both, some with only one, and some do different things for Ctrl than for Cmd (compare Ctrl+G to Cmd+G on Mac). Also, that comparison (205 vs 147) is unfair, because a lot of the Windows shortcuts relate to operating the ribbons and menus (or is it unfair?). So if I exclude them, using a pivot table with a slicer, I get this comparison of Windows and Mac keyboard shortcuts, according to my top level categories.

Group Windows Keyboard shortcuts Mac Keyboard shortcuts Mac vs Windows %
Calculation 5 1 20.0%
Editing 78 64 82.1%
File operations 6 5 83.3%
Formatting 9 9 100.0%
Help 1 1 100.0%
Macros 3 3 100.0%
Names 5 3 60.0%
Navigation 22 20 90.9%
Outlining 3 3 100.0%
Selection 33 32 97.0%
Tables & Filtering 3 3 100.0%
Windows 7 2 28.6%
Grand Total 175 146 83.4%

The ultimate list of keyboard shortcuts

Ok. That’s enough talk. Here is the list.

Please feel free to share it, but please do not remove the attribution.

Share