Not all keyboard shortcuts are created equal

I thought I would do something different today for IKSD. Like so many things in life, one may find a whole spectrum of keyboard shortcut users. Let me elaborate:

First, there are those who don’t (but, I would say, who should). These are the people who use the keyboard to type letters and numbers. For everything else, there’s a mouse. Want to move the insertion point one character to the left? Use the mouse—there’s no need to bother with tapping that pesky little left arrow on the keyboard when you can reach over, drag the mouse to where you want it, click, and then move the mouse pointer out of the way!

Then there are those who discover one or two keyboard shortcuts that they find really useful, but never really venture into the realm of learning more. These are very often the Alt-set (not the alt-right!)—those who use Alt+xxx to enter special characters not available on the keyboard. You will often find a petite list of Alt codes pasted onto their PC box or the bottom of their monitors. They may even get someone more knowledgeable to help them create some custom shortcuts for certain special uses.

The next level is those who start learning keyboard shortcuts, and use the most basic set for certain tasks. These are the ones who learn about Ctrl+C/V/X (but somehow never seem to grasp the Cut/Copy/Paste—XCV keyboard layout connection) and some others (Ctrl+P, Ctrl+O, etc.). They generally only use system-wide shortcuts that work in all their programs for the most common tasks. And perhaps some program-specific exceptions like learning about Shift+F3 in Word (but don’t ask them what it does in Excel [it’s Insert Function, by the way—one I never use). These all, are in reality (or is that metaphorically), like the laity in the world of keyboard shortcuts.

After that, we move to the functionaries. Even here, we find different levels of progress, just like one progresses through degrees at university or coloured belts in karate. But all of these are the people who have embarked on the journey. The journey of self-improvement, or (before I start sounding like a movie-martial-arts-mystic), the journey of actively discovering and using more shortcuts. However, I recently came across shortcutfoo.com, and while I didn’t try it, I like the idea. Here, you can envelop yourself in the misty mountain, just you and your keyboard, no mouse to be heard lowing in the fields outside, and become one with the keyboard. Ok, I’ll stop now.

The point I want to make is that, while I readily admit that I am not the supreme grandmaster of keyboard shortcut-dom, there is a sure sign that someone has progressed beyond the level of just being an initiate, but is well on the way to becoming a master. And that is when they start understanding, not only that different programs have different shortcuts (that’s really for those who have just moved past initiation), or that certain shortcuts may do different things in different programs (getting into the coloured belts now…), but that even the same shortcut might work differently in different contexts within the same program (surely this must be black belt knowledge by now?).

Blackbelt keyboard shortcuts to master

I want to illustrate just a few of those, and the tool I will use for this is Excel, since it is probably the program I use that does this the most. I suppose I could list a number of others, but this is more of an effort to illustrate the point, than to provide a comprehensive list. Here is a simple workbook that you can use to practise these shortcuts and see the differences. It contains a small data range, duplicated across three worksheets (Normal sheet, Filtered list, and Table). Also, outside of that are list of numbers for rows ($J$1:$J$12) and columns ($A$17:$E$17) so that you can see when a worksheet row/column or only table row/column are being inserted deleted. Also, note that the discussion below is as for Excel 2016.

Consider the following keyboard shortcuts:

Shortcut: Ctrl + D

Normal data range

Fills cell from row(s) above. This includes all cell attributes (formatting (direct and conditional), validation, etc.) and contents.

Can fill down multiple rows and across multiple columns (although direction is always to fill down, not right).

Normal data range (filter applied)

When used on a single cell, fills cell from row directly above, even if that row is hidden by the filter.

When used down several rows, fills the top row down to all cells displayed by the filter, but leaves cells hidden by the filter unchanged.

Table (unfiltered)

As for normal data range.

Table (filter applied)

As for normal data range with filter.

Normal data range (and table) Normal data range and
table (filter applied)

Figure 1    Ctrl + D

Shortcut: Ctrl + `

Normal data range

Copies down a cell’s contents from the cell directly above. Only contents are copied, no other attributes.

Can only copy down from one row and one column (i.e., the cell above the active cell).

Normal data range (filter applied)

Copies down the value from the first row above not hidden by the filter (i.e., no longer the row directly above)

Table (unfiltered)

As for normal data range.

Table (filter applied)

As for normal data range with filter.

Normal data range (and table) Normal data range and table (filter applied)

Figure 2    Ctrl + ‘

Shortcut: Ctrl + +

Normal data range

Displays the Insert cells dialog, dynamically choosing between shifting cells down or right to make place for inserted cells.

Normal data range (filter applied)

Does nothing.

Table (unfiltered)

When one cell or several cells across one row are selected, automatically inserts a table row (not a worksheet row).

When and several cells across one column are selected, automatically inserts a table column (not a worksheet column).

When several cells across rows and columns are selected, inserts multiple rows or columns, based on which have more selected. In other words, if a symmetric range is selected (x rows by x columns), then x rows are inserted. If an asymmetric range is selected (x rows by x columns), then x rows will be inserted if x > y, and y columns will be inserted if x < y).

Table (filter applied)

Does nothing.

Normal data range (and table) Table (unfiltered)

Figure 3    Ctrl + +

Shortcut: Ctrl + –

Normal data range

Displays the Delete cells dialog, dynamically choosing between shifting cells up or left to fill up the deleted cells.

Normal data range (filter applied)

Deletes the entire row (but asks for confirmation).

Table (unfiltered)

When one cell or more than one cell within a single row are selected, automatically deletes a table (but not worksheet) row.

When cells across more than one row are selected, automatically deletes the table column (not the worksheet column).

When several cells across rows and columns are selected, deletes all selected rows or columns based on which have more selected (as with Ctrl + +).

Table (filter applied)

When one cell or more than one cell within a single row are selected, deletes the entire worksheet row (but asks for confirmation).

When cells across more than one row are selected, automatically deletes the table column (not the worksheet column). If the deleted column was filtered, then the filtering in the table for that column is removed (i.e., all rows hidden based on the filtering criteria specified for that column are shown).

When several cells across rows and columns are selected, deletes all selected columns (note the discrepancy with Ctrl++). If any of the deleted columns were filtered, then the filtering in the table for those columns is removed (i.e., all rows hidden based on the filtering criteria specified for those columns are shown).

Normal data range (and table)
Table (unfiltered) Table (filter applied)

Figure 4    Ctrl + +

I did not discuss Ctrl+R, which fills to the right, just as Ctrl+D fills down, because its functionality is not affected by context.

Another set which I could get into, but which I will for another day, is how copying and pasting to- and from filtered lists/tables differs from copying to- and from normal ranges. I think for IKSD 2018, I may do a post just on filtered list/table shortcuts, and their idiosyncrasies. If you don’t want to wait that long, you can embark on your own journey of discovery—the truth is, however, not within, but the truth is out there!

These are the first ones I could think of as I sat down to write this post. Are there others that you can add? Let me know in the comments of others that you would add to my list.

Share