International Keyboard Shortcut Day 2021: I shortcut, therefore I am

And now for something completely different. You might call this a philosophical reflection on, of all things, the use of keyboard shortcuts.

I did not do a post for IKSD last year (life got in the way a bit) and in previous years, my posts were aimed at teaching some aspect of using keyboard shortcuts. But this year, I want to take a step back and speak to the mouse-using fraternity and present a case for why we should all be using keyboard shortcuts more.

The most common argument for using keyboard shortcuts is that they help us work faster. And while that is certainly true, I suspect it probably isn’t very compelling. I think most people don’t care, and think to themselves that the way they work is just fine. Most people, truth be told, don’t want to work faster. If you disagree, and you’re still a mouser, you’re living a lie—you’d better start learning to use keyboard shortcuts, pronto.

But is there perhaps a more compelling motivation for using keyboard shortcuts than just “working faster”?

The answer, I’ve found, might be in the work of Marshall McLuhan and after him Neil Postman, who explored, amongst others, how technology affects us. I confess that I haven’t read any of McLuhan’s books (I’ve read about his work, but haven’t read his work), but will note that he is famous for his statement “the medium is the message.” I have read a number of Postman’s books, and while he qualifies McLuhan’s statement somewhat, he does point out that the medium has a very strong impact on the message, so much so that it can subvert and even reverse the message. His book Amusing ourselves to death on the problems of television and, more accurately, moving from a print culture to a visual image culture, is truly thought-provoking. Written in 1985, and thus predating the internet- and social media explosion, its message still rings true today.

Now as far as I know, neither McLuhan nor Postman wrote about the mouse and the keyboard, but I will take it upon myself to extend that idea here. Let me start by making the very simple observation that the keyboard and the mouse represent two very different ways of interacting with your computer. In the simplest way I can phrase it, the mouse integrates vision and action, while the keyboard dissociates vision and action. What do I mean by that? Well, when we move the mouse, we are not looking at our mouse-moving hand. Rather, we are looking at the mouse pointer on the screen as if it were where our hand was moving. There is an almost direct relationship between how we move our hand, and how the mouse pointer moves in response. The keyboard is different. If we can touch-type (type without looking at the keyboard), then what we see happening on the screen bears, at least in two-dimensional space, no relation to how our hands are moving. If the program allows, we can manipulate very different parts of the screen with the keyboard. In fact, with the keyboard, I can make things happen on different screens (when I have them set up), without having to drag the mouse pointer across from one screen to the other. Even if we cannot touch-type (i.e., we cannot type without looking at the keyboard), there is still dissociation—we are then looking at our hands moving across the keyboard, knowing that something different is happening on the screen.

Why would this be important? Well, the first issue again is the obvious one—speed. Because we are freed from the constraints of having to physically move (with our hand on the mouse) through the two-dimensional space of the screen, we can get things done faster. Additionally, we use one hand on the mouse, but two on the keyboard, which is, again, faster.

However, I think this still misses the most important point. This dissociation forces us to think differently about using the computer.

And I would argue, think better about using the computer. With the keyboard, with training, even the physical movement of our hands across the keyboard to execute a certain shortcut eventually becomes unconscious, just as, for a seasoned typist, all hand movement during typing is unconscious. When using keyboard shortcuts, then, we are only thinking about the command we want to employ or the action we want to perform, without actually having to think how to perform it. By contrast, using the mouse means that we always have to interrupt our thought about what we want to do with thought about how to do it—the additional mental effort of moving the mouse through the two-dimensional screen space. The mouse, in other words, is disruptive to our thought. And it is always so, even for seasoned users, because of the medium and the association with physical space in the screen. The keyboard, with practice, is not disruptive. It allows for a fluency of thought in our computer work which the mouse cannot. I would even go so far as to contend that, at least at present, speech controlled computing is still partially space bound (we have to tell the computer which word to select, etc.). At present, then, even voice-controlled computing is not as dissociative as keyboard computing (although it might be quicker for some).

Of course, this does not mean that the mouse had no place at all, and there is work which requires the mouse (e.g., CAD work), but it is normally work related to physical space. Text, for example, does not. User interface interaction, normally, also not.

So, to summarise. Why should we use keyboard shortcuts? Because a praxis which employs keyboard shortcuts is one that trains us to work, not simply faster, but at a deeper level, to work more efficiently, to think more clearly about the task we are performing. It is one where we can think freely about what we need to do, without having to think about how to do it. Keyboard shortcuts don’t just help us work faster, they help us work better. And that is something worth thinking about. I, at least, find it compelling. Do you?


APA style multi-line table and figure numbering with Word Captions

This question was sent to me, and I thought the solution deserves a blog post, since if one person is struggling with this, there are bound to be more.

The APA seventh edition requires table and figure captions to look like this (amongst others):

number: The table number (e.g., Table 1) appears above the table [or below the figure] title and body in bold font. Number tables/figures in the order in which they are mentioned in your paper.

title: The table/figure title appears one double-spaced line below the table/figure number. Give each table/figure a brief but descriptive title, and capitalize the table/figure title in italic title case.

There is no need to get into a debate about whether this change from previous editions is warranted, etc. It is the current guideline, and most students (I think rightly, on this point), couldn’t care less about whether this looks better than what was recommended in previous editions—they just want to get it done.

So here’s how.

The problem

The problem, of course, is that Word uses the Caption style for table and figure (and other) captions. And the Caption style is a paragraph style. But now we need a multi-paragraph caption. And if we format all those paragraphs with the caption style, they will appear as multiple paragraphs in the List of Tables/Figures.

The solution

Note. The images below show non-printing characters. I do explain in detail in my book why you should be working with these on, and this post is a good example.

As with so many things in Word, the solution lies simply in recognizing that we don’t actually need a multi-paragraph caption, we just need our results to look like a multi-paragraph caption. And in Word, you can sometimes create exactly the look you want using some surprising tools. For example, in my book I recommend using (borderless, and thus invisible) tables to align complex components (e.g., multi-part figures) on a page (although this should be done sparingly). This is another example of using a tool (a tab, in this case) to create an effect that looks as if it was produced by another tool (a line/paragraph break).

We want the whole single caption paragraph to contain, on its first line, the number, and then on subsequent lines, the title. Also, the font (bold, not italic) of the number portion (the word “Table/Figure” followed by a space and an Arabic numeral) is different to that of the title (not bold, italic).

The trick is to realise that in a normal caption paragraph, the number is separated from the title by a tab. Normally, this tab is just a little wider that the full number portion (e.g., “Title 123”), so that it creates a uniform placement on the page for the start of the title portion (i.e., whether the number is 1, 12, or 123, the exact point from the left margin where the title portion starts remains constant). All we have to do is set the width of that tab to the inter-margin width of the page, and it forces the title portion onto the next line, making it look as if it is in a new paragraph. Then we use a character font to change the formatting of the number portion (a bit of a nuisance, that!), and we are done.

The steps

In your document or template file, add a caption. Note in Figure 1 that the paragraph is formatted with the Caption style and that the ruler shows that the width between the margins is 16.5cm. what is harder to see is that the tab ends at 2.54cm. The reason for this is Word’s default half-inch tab stop width which is converted to 1.27cm. Since the word “Table” with its following space extends past 1.27cm, the tab stop takes it to the next increment—one full inch, or 2.54cm. I have opened the tabs dialog to make this obvious.

Figure 1   Table caption

First we want to set the Caption style itself. The simplest at this point is probably to open the Styles pane (Ctrl+Alt+Shift+S), since we will want to use it again below. In the Styles pane, right click on the Caption style, and select Modify… (Figure 2).

Figure 2   Styles pane—Modify caption style

In the Modify Style dialog (Figure 3), set the font to italic and the paragraph spacing to double line spacing. If you are working in a document that is based on your template file, then remember to select the New documents based on this template option to carry the change through to your template, making this a permanent fix for all future documents based on this template.

Figure 3   Modify Styles dialog—Caption style

Next, in the same dialog, select Format, Tabs… (Figure 4).

Figure 4   Format Tabs

In the Tabs dialog (Figure 5), set a 16.5cm left tab stop (you may need to adjust this based on your actual page width and margin sizes).

Figure 5   Tabs dialog

OK all the dialogs, and you will get the result shown in Figure 6.

Figure 6   Caption paragraph, double-spaced (incorrect)

There is one problem evident in this, and that is that the tab is actually now introducing an extra (double-spaced) line. Anyone with experience of Word’s tabs will know that this is easily fixed by typing a single space after the tab (highlighted in Figure 7—it is visible because I have non-printing characters displayed).

Figure 7   Caption paragraph, double-spaced

Next, to create the character style, select the “Table 1” text and the tab after it in your document, and, in the Styles pane, select New Style (Figure 8).

Figure 8   New Style

In the Create New Style for Formatting dialog (Figure 9—the same dialog as in Figure 3, just with a new title), I chose to add the name Caption Number so that this new style will be closely associated with the Caption style in the Styles pane, set the Style type as a Character style, and then set the font as bold and italic. Why italic? Well, in one of Word’s little idiosyncrasies, the character style is applied on top of the existing paragraph style, almost as if the attribute is set (or clicked, if you want) each time. Since bold and italic are toggles, the paragraph style “sets” italic formatting on, and the character style then “sets” italic style off.

Figure 9   Create New Style from Formatting dialog

This produces our end result, which agrees with what the APA style definition requires (Figure 10).

Figure 10 Properly formatted Table caption

And lastly, since the List of Tables (created with the Insert Table of Figures tool) uses the Table of Figures style, which has its own tab widths, the wide (16.5cm) tab stop created above is negated in favour of a more reasonable tab width in the List of Tables (Figure 11).

Figure 11   List of Tables

And that is how it is done. It may seem complicated, but it really is actually a simple and efficient workaround. Once this has been done, you can add your table and figure captions as before, and all you have to do is the (somewhat annoying) extra step of applying the character font over the number to get it looking as it should.


InnoSetup: Checking whether Microsoft Word is running

I am working on an update to the Word uTIlities. As part of the process, I wanted to update the installer to check for whether Word itself is running, as that causes an error in the installation when updating the Word uTIlities (as opposed to a new installation).

I have used Inno Setup right from the start, and have been very happy with its functioning and flexibility. So how to get it to check for Word?

The first tack I chose was based on this Stack Overflow thread—trying to get AppMutex to work. That, however, failed, because even though I tried loading my modification to the recommended code into the Word uTIlities template file’s ThisDocument object, the installer was not detecting it:

Option Explicit
'This is from the InnoSetup Help files:
'[Setup]: AppMutex

'Place in Declarations section:
Private Declare Function CreateMutex Lib "kernel32" _
        Alias "CreateMutexA" _
       (ByVal lpMutexAttributes As Long, _
        ByVal bInitialOwner As Long, _
        ByVal lpName As String) As Long

Private Sub Document_Open()
    'Place in startup code (Form_Load or Sub Main):
    CreateMutex 0&, 0&, "Word_uTIlities"

End Sub

Next I found this page, but when the link to the PSVince DLL failed, I stopped right there. It would still be nice to get the installer to offer to close down Word on my behalf, but my experience with other installers not always doing this in the way I would have liked convinced me that asking users to do it manually was not all bad.

So then, after a little more digging, I came across an example in the sample code file that actually does test for the presence of Microsoft Word. Starting with that, it was not a lot of work to modify it to get the Inno Setup script code below, which works quite well, I think. Take note, though, of this when testing while compiling your installer from the Inno Setup IDE—essentially, you have to test it by running the *.exe file.

function InitializeSetup(): Boolean;
  Word: Variant;
    Word := GetActiveOleObject('Word.Application');
  if VarIsEmpty(Word) then
    Result := True
     //Do nothing, Word is not found
  else begin
    MsgBox('Microsoft Word is running. Please close it before resuming the installer.', mbInformation, mb_Ok);
    Result := False;

And that’s it! Again, Inno Setup proved itself up to the task, and I am satisfied with the end result.


International Keyboard Shortcut Day 2019: Social media keyboard shortcuts

Yep. It’s that time of the year again.

I am not going to make this a long post, but I have to do something for IKSD 2019. I can’t let the momentum flag….

Let’s face it, social media is probably more effort than it’s worth. I could curse the people who came up with the idea, and yet, it was probably inevitable that something along these lines would be what the internet has developed into. The specifics, though, might have looked different if more thoughtful, more benevolent people had developed the concept. Dream on!

I have the distinct feeling that the idea behind the Matrix—of humans plugged into a machine which harvests their very life force while feeding them a dream-world-existence to keep them going, has found its very cruel reality in social media.

So while I find myself in the niggly situation of having to use social media for various things (club and school notices are posted on Facebook, group pages are used for functional activity, etc.), I do try to limit the amount of time I spend on social media (which is a constant battle, since the very premise of almost all social media sites is to set up their algorithms to maximise the time we spend on social media, so that they can harvest the maximum amount of data from us, expose us to the maximum amount of revenue-generating advertising, and turn the maximum profit on us. Yes, look yourself in the mirror each morning, and say to yourself “I am a social media profit centre.” And then look back down at your phone and give them your time like a good little slave.

So some things I do to minimise my time on social media are:

  • Minimise the services I use (Instagram? No. Snapchat? No? etc.). If there is no compelling reason other than gawking at other people, then I try to avoid it.
  • Use time tracking tools like RescueTime or ManicTime (I actually use both) and even the tracking tools on my phone, to constantly monitor the time I spend on social media, so that I can see when I am losing my foothold and getting sucked in. And no, I am not getting a kickback for recommending RescueTime or ManicTime (which is why I can post the links twice without creating suspicion).
  • Avoid using the phone apps provided by social media companies. I lasted one day, I seem to recall, on the Facebook Android app. It just has too much control, and really allows them to create a gravitational force that I was not able to stand up to (yes, know your weaknesses). So I deleted the app, and only access Facebook via the website from my phone. Which leads me to…
  • Spend as little time on these services from your phone as possible, and rather access them from your desktop or laptop. There are several advantages to this strategy.
    • The inconvenience factor (it’s just much easier to browse social media from the phone, so it’s easier to get sucked in).
    • The access via the website, not the app, mentioned above (yes, I know Facebook has a desktop app too—I don’t use it for a reason).
    • The ability to use browser apps like SocialFixer, which I highly recommend (and which I recommend you support—no kickbacks for me, again). These tools really help cut the crap, and help limit the time you spend on social media (e.g., SocialFixer limits the Facebook posts shown at one time, and it has the ability to mark all the posts on your page as being read, so they don’t appear again—a major way the social media companies get us to stay on their sites, because we want to scroll past the stuff we have seen, to get to the stuff we haven’t seen).
    • And then also the ability to use keyboard shortcuts to speed up the process, which brings me to this post.

However, since the lists of keyboard shortcuts have been adequately enumerated elsewhere, I am not going to replicate them all here. Just provide you with some links to good lists, and recommend that you cultivate the discipline of using these, again with the aim of cutting down your time on social media. Set yourself a target, try to keep to it, and use the time you gain for some productive work, or rather put down the phone, turn off the computer, and use the extra time doing something you like with someone you love. That will have a far greater positive impact on your life than any amount of social media browsing you could ever cram in.

So, without further ado, go and work through some of these pages, and learn and use these shortcuts:


Facebook might just make it that little bit easier for hackers to get into your account

If you use Facebook and you do not at the very least have a love-hate relationship with it, then you probably just aren’t thinking about what you do there. I find myself often thinking that it’s time to just close my Facebook account, but on the other hand, having moved to Australia, I have found that a fair number of Facebook groups have become the de facto forums of the Social media era. As such, even though I try to limit my time on my main page, and post very little, Facebook’s groups still have some value to me.

But before I get to the actual topic of this post, perhaps just some comments on Facebook in general. First, remember that there is no such thing as a free lunch. Facebook offers us this awesome tool for connecting with other people from all around the world, even, to quote Philo Farnsworth, people we would ordinarily never invite into our homes (yes, in the Facebook era, we may even have to redefine the word “friend”). And Facebook gives us all this for free. That’s right—spend as much time as you want, upload as many photos and videos as you like, all at no cost. No subscription fees, no data fees, nothing. Except… that’s not true. We pay, and oh, do we pay! We pay for the use of the site with our time, with our data, with our photos. We willing submit ourselves to manipulation and marketing, from which they make millions, in exchange for the service they give us. Of course, since it’s a trade, one may argue that the price we pay is fair for the service we get. I will not get drawn into a debate about that now, since that is not what I want to discuss. Let’s just assume that most of us are perhaps paying a bit too much and getting far too little.

Nonetheless, if you do find yourself getting sucked in to Facebook, here’s some help.

  1. Install Social Fixer, and spend a few (really only a few) minutes figuring out how to use it. And if you like the service, make a donation. This one thing along will start killing the Facebook time-suck, and will transform your use of the site. I would argue that no-one should be using Facebook without this tool, but that may be akin to saying that I think Facebook’s default interface is dysfunctional, or that I think no one should ever be using the Facebook app on their phones (for which statements I make no excuses).
  2. I use the RescueTime service to monitor my productivity on my pc. Using this, I can monitor how much time I am spending on distracting things like Facebook, and my target is less than 15 min per day.

But now, on to the reason for this post. Facebook, as anyone who reads any real (not fake) news (i.e., who does not read news brought to them by Facebook), will know, Facebook has a really spotty history on protecting your privacy. Not only are they harvesting your personal information and mining it for their own profit, but they have been repeatedly accused of creating such confusing and user-unfriendly privacy settings (we shall see an example of this shortly), that many people are totally unaware of how much of their data is on display for the whole world to see.

Privacy? What’s that?

Take a look at this privacy setting, as an example. It is so obtuse to get to, that just trying to find it proves how confusing Facebook has made things. Did you know that, if your privacy settings are not set right, then people whose friend requests you ignore (no indication is given of what length of time is defined by Facebook as “ignore”) or even delete will automatically be rerouted to start following you? That means if you allow followers, and delete someone’s friend request, they automatically become a follower (when did that become a good idea?). This Facebook help page explains the process.

Having your Facebook account hacked, 101

However, what I noticed recently, and what prompted this post, was that Facebook, in an attempt to make life easy for their users, has inadvertently opened up what is, to my mind, a huge security hole. So I want to describe it, and make some suggestions as to what you can do about it.

What I noticed is that I normally access Facebook from my personal pc, where I have the “remember me” login setting turned on—this stores a cookie on the pc, allowing me to be logged in automatically when I navigate to their page. However, last week I tried to log on from a different computer, and seeing as I had entered my password so long ago (because of the “remember me” setting), I momentarily forgot it and typed in the wrong password. Lo and behold, I received an e-mail in all three of my e-mail accounts allowing me to log in by simply clicking on the link in the e-mail. Without the password. I couldn’t believe it when I saw it, but I thought that I had to test it out, and it really did just log me into my Facebook account, no password required (see my comment about two-factor authentication below).

So how is that a danger? Well, imagine a hacker gets your e-mail password. Presumably, they won’t have the password without the e-mail address, so that means that they can log into your e-mail account. Now they go to and try to log in with your e-mail address, and any password. Facebook helpfully sends this e-mail out, and the hacker, who has access to your e-mails, clicks the link, and is logged into Facebook. All they do then is permanently delete the aforementioned Facebook e-mail from your mailbox, and you will be none the wiser. This whole transaction can take only a matter of seconds, and if you were not also logged into your e-mail account and watching your inbox at precisely the same time, you would not even know it. But now your Facebook account has also been hacked, and, for example, they could go to the settings page and download all your Facebook data. Nice? Definitely not! Or they could change your Facebook password, and lock you out of your own account. And spam all of your Fakebook friends (sorry for the typo!).

I must add that this kind of hack is not new. Most websites have an “I forgot my password” feature that allows you to type in your e-mail address, get an e-mail with a special link, and then reset the password without knowing the existing password. Hackers have been able to exploit this in exactly the same way as I have described above. But at least that added a layer of complexity to the process, and that complexity makes it easier for you to discover that your accounts have been hacked. Facebook has just dispensed with that, and made it ultra-convenient for this hack. Sure, they could argue that your e-mail account being hacked is not their responsibility, but I don’t think leaving a back door for anyone to get into their service on the basis of a compromise elsewhere is justified. Imagine you had accounts with two different banks, and bank B had a function that allowed all your money to be withdrawn provided someone had your card and pin for bank A! “Ludicrous!” you would say, and I would agree. Which is precisely my point.

The second security setting to set on both your Facebook and e-mail accounts is to activate two-factor authentication, and not to use your e-mail address as your second authentication source (why this is a bad idea should be quite evident by now), but rather to use your cell phone number to receive an authentication code via SMS. Of course, this means surrendering yet another piece of your data to Facebook, but hey, relax, they are spying on—oops, not spying, monitoring you—so much, they probably have that data anyway. For example, Facebook is tracking your web browsing history).


In short, then, be savvy when you use Facebook. Accept that you are paying for the service you are using with your privacy, and decide whether that is an acceptable trade for you. If not, quit the site, delete your account, or at least start restricting your use of the site. There really is no other way. And make sure you have checked your privacy settings, and that you have a good password and two-factor authentication set up.


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).


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).


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.


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).


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.


Spurious correlations and Big Data

I have been a Time magazine subscriber for decades. And while I generally enjoy being informed, I also read Time knowing that they have a definitive ideological slant, which is very evident in their over-reporting of certain topics. So I read Time carefully, knowing that I do not agree with all their viewpoints, and also that I should not believe everything I read. Time is also not generally known for graphical excellence—in fact, you will easily find Time being used as fodder for examples of how not to do charts. And I also find it amusing to look at their charts and infographics, although I must add that there are definitely times that Time does get it right (and in all fairness to them, it is unfortunately so that when they do get it right, we don’t find people singing their accolades).

I am, as I have indicated in an earlier post, doing a lot of work with Google Trends data. One of the biggest challenges with this data is the problem of dimensionality or overfitting, which, simply put, means that when we have data on masses of predictor variables, we are bound to find some which, by chance, correlate well with our variables of interest—in other words, the more dimensions we add, the more likely we are to run afoul of spurious correlations.

I have just again browsed through some of Tyler Vigen’s hilarious spurious correlations, like the 0.99 correlation between the number of lawyers and suicides or the .093 correlation between per capita cheese consumption and people who died by becoming tangled in their bedsheets. (Which in itself raises some interesting questions, like “How do between 400 and 900 people in the US get it right to get so tangled up in their bedsheets each year?” Perhaps the statistic includes infant deaths, which would be very tragic).

In fact, it seems you can “prove” anything with research these days, including, for example, that intelligent people are messy, cursing insomniacs (also here), who like trashy movies, and appear to be lazy (but note, again, how popular media slants research findings to support what they want it to say, not necessarily what it does say).

I also unearthed an old issue of Time (1 August 2016) to see this: Political foods.

   Time 2016-08-01 Vol188 no5 p17a  Time 2016-08-01 Vol188 no5 p17b  Time 2016-08-01 Vol188 no5 p17c

You can also read more on this page and this page.

This has to be one of the most spurious of all spurious correlations, and is a good example of overfitting. They have a big data set (Grubhub’s data). They choose 175 dishes. Then for each dish, they calculate the correlation between the number of orders and the two percentages of Democratic and Republication votes. That’s two correlations for each dish, for a total of 350 correlations. Presumably, each district is a data point: This page notes that they ran “correlations between the share of orders for 175 dishes and the average share of votes going to Democrats or Republicans in each district.” And then they just chose those dishes that showed the highest correlations.

Here, it is much more likely to say that certain foods enjoy more support in certain geographic areas (or are more freely available in certain areas, as Time does acknowledge), just as the Republican and Democratic parties also enjoy more support in certain geographic areas, but it is one of the most trite deductions imaginable to claim that one is more likely to eat certain foods because of one’s political affiliation (or, heaven forbid, that the food you eat determines how you vote—Time suggests, hopefully in jest, that “Of course, we all know that eating a hamburger makes you more likely to vote Republican”). the correlation should at least make sense. For example, certain religions have certain dietary prescriptions, and so an association between religious affiliation and dietary preference makes intuitive sense, but political affiliation being directly associated with dietary preference just makes no sense. Indirectly, maybe (e.g., if certain religious or cultural groupings tend to hold a certain political affiliation), but not directly.

Remember that correlation is only a measurement that shows the degree of association between two variables (not even agreement, as Bland and Altman pointed out). So remember that correlation does not prove anything. That also does not mean that nothing is proved by correlation. The main point is that correlation should be correctly understood. It only shows the association of two variables. But there must at least be some understandable link between the variables, and, more importantly, all spurious variables must be excluded.


Giving feedback in an Excel worksheet UI

I am creating a tool as part of my work at the University of Sydney to extract data from Google Trends via die Google Extended Trends API for Health. Really an exciting project, and a tip of the hat to Google for making this awesome tool available.

Part of the project is getting multiple samples of the data from the Google servers (I am hoping to get a publication out soon about the why and how of this kind of sampling), and then aggregating them (just calculating data-point averages, really), and charting them. Since most of the output will end up in an Excel workbook, it just made sense to put the whole thing in Excel.

The first part of this is creating a UI with which the user can specify the extraction that needs to be done.

I have built a worksheet-UI, in similar fashion to that recommended in the Excel developer’s “bible”, Professional Excel Development. Part of the UI involves giving feedback when input is still required or inappropriate. These feedback messages can, of course, be given in different ways. Using VBA, a message box can tell the user what is needed, but the problem with that approach is that message boxes can be cancelled, and the input still not corrected. So it makes sense to display on-sheet messages which persist for as long as the issue exists, and these can easily be done using in-cells formulas. The question then becomes, where to put these error messages.

An early version (slightly condensed) of the UI looked like this:

Figure 1    Early version of UI error messages

The two salient points from this figure are firstly that there are error messages in several places (below the API_Key, although no error is currently displayed, below the Target, and in the Messages block). This can clutter up the UI very quickly, so perhaps it is best to put all error messages in one location, like the Messages block. But that brings me to the second point, which is that each cell in the messages block contains one error-checking formula. So in Figure 1, the first four error messages are not displayed (because there are no errors), and the fifth is. This just looks weird. Of course, I could write my error checking formulas to display some kind of “All’s well” message for each formula, but my general aim is to reduce clutter, so I prefer to have the blank cells than a succession of “All’s well” messages.

Figure 2 gives an idea of the structure of the worksheet, showing some of the error checking formulas. It can be seen that I have made extensive use of named ranges to simplify things, and that most of the formulas use a simple IF function with a blank string (“”) displayed if there is no error.

Figure 2    Error checking formulas in UI

So, I was a little dissatisfied with the look. If some issues are addressed and others not, then unsightly gaps are created between the various messages.

What I wanted was a list of messages, where the various issues are displayed top-down. That means that each cell in my list (range) of error messages must be able to display any of the error messages, or the list of error messages must “magically” rearrange themselves so that the displayed messages are at the top of the list.

This is how I went about doing it.

  1. The first thing to do was to create an off-area range where each message is displayed—off-area meaning a range of cells that will be hidden when the finalised version of the workbook is released. So I cut the original list of error messages and pasted them in cells $N$16:$N$23 (columns K to XFD will be hidden in the final version), as shown in Figure 3 (which actually shows the end result, but that is useful for understanding the process).

Figure 3    Moving the error checking formulas

  1. I added a single “All is well” message. One is fine, so long as it appears right at the top. In cell $N$24 I used this formula:
    {=IF(SUM(LEN(N16:N23))>0,””,”All inputs are completed. Data extraction can be commenced.”)}
    For the uninitiated, the braces {} indicate that this formula is an array-entered (or CSE) formula. You do not type the braces, but enter the formula and then press Ctrl+Shift+Enter (hence the CSE alias) to “transform” it from a regular formula to an array-entered formula. It essentially looks for zero-length strings in all the error message cells, and if so, returns the “All’s well” message shown.
  2. To figure out which of the error messages to show in the original message area, I added some formulas in columns L (with the heading “Index” in Figure 3) and M (with the heading “Display position” in Figure 3). The text descriptions in the “Refers to” column (Column O) are not used, and are just for my own reference, so that I can debug or modify the formulas later on.
    Column L has the formula =COUNTA($N$16:$N16) in cell L16, and so on down—i.e., L24’s formula is =COUNTA($N$16:$N24).
    Note that the first part is entirely absolute ($ signs before the column and row references), but that the second has a relative row reference. Thus, as it is copied down, the row changes. Essentially, it is counting the number of rows from the fixed starting point if $N$16, and using that as a means of “numbering” (or indexing) my error messages. Simple, really.
  3. Next is the magic bit. The formula in column M that tells Excel whether to display the error message or not, and if so, in what position. It again uses an absolute starting point, with a relative row end: =SUMPRODUCT(IF(LEN($N$16:$N16)>0,1,0)*(LEN($N16)>0))
    Essentially, what this formula does, is count the non-blank error messages. The first part, IF(LEN($N$16:$N16)>0,1,0), basically says, “If the cell length is longer than zero (i.e., not a zero-length string), then return a 1, but if its length is zero, return a zero). The second part, (LEN($N16)>0) returns True or False to the statement “length is greater than zero.” But True, in Excel, equals 1, and False, in Excel, equals 0. So when the position is multiplied by zero (i.e., when there is no error message), it returns zero. When the position is multiplied by 1 (there is an error message), then the position is returned. Perhaps not that simple, but it works. Figure 3 shows that the 1st, 3rd, 5th, 7th, and 8th error messages will be displayed in the 1st through 5th positions.
    the formulas (partly) are shown in Figure 4.

Figure 4    Error message index and display position formulas

  1. On the off chance that I will be adding more error messages, it makes sense to create a dynamic range name that encapsulates my list of error messages, with their indexes and positions. So I added a range name MessageBaseList with this formula:
    =’Query specification’!$L$16:INDEX(‘Query specification’!$O$16:$O$30,
    COUNTA(‘Query specification’!$N$16:$N$30))
    I somewhat arbitrarily used row 30 as the end point, as that is near the end of my visible interface, and I judged it unlikely that I would be adding more error messages than that!
  2. All that remains is to use a formula in my original message area to look up the error messages that have display positions (i.e., those values in column M greater than 0), and return them.
    Again, I use the absolute anchor with a relative end to allow the formula to adjust as it goes down:
    This formula essentially self-determines its position in the message display area using the COUNTA function, and then uses the INDEX function to check if there is an error message with the corresponding position, and if so, displays that. Again, perhaps not quite that simple, but it works!
    Note that the message area is essentially E16:E30, although I merged cells to get enough space to show the messages. Generally, I would advise against merging cells like this, but the layout and the fact that I had to make provision for up to 30 search terms (the limit allowed by the Google API) meant that I really had to make the best use of the space I had at my disposal. The solution works, so I will settle for that, but I do confess that it would be simpler if I had not merged cells.

The basic concept is that the error messages that are active will be displayed, from the top down, in the display area. I can finally add my single “All’s well” message to indicate when there are no more errors, which does help with feedback but still minimises clutter (Figure 5).

Figure 5    “All’s good” message

I will be publishing the complete tool when I am done, and I will update this blog post with a link when I do, so that you, dear reader, can see the effect in action!


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, 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.


Getting back the old Word Spell Checker

If it ain’t broke, don’t fix it. This, I suppose, is easier to say than to do. Still, it is something that I have, on more than one occasion, wished that Microsoft would take to heart. Items from recent memory include replacing the ultra-efficient Print dialog and Print Preview with the ultra-inefficient Print-Preview-and-Print in Word 2010, which is jack of all trades and decidedly master of none. Or removing the Autocorrect option from the spelling error context menu in Word 2013. Or removing the red/green/blue wavy underline colour scheme for spelling/grammar/contextual errors (admittedly, problematic for the colour-blind) and replacing it with red for spelling and blue for grammar and context combined (which I am still not certain works for all types of colour blindness, although that is not my area of expertise). Or the way they keep messing with the most-recently-used (MRU) file list—each version since Word 2007 has seen this change, and from my perspective, the iteration that was most useful to me was Word 2010, now two versions back already.

The problem

Another of these “improvements-as-regressions,” which I am writing about here, is replacing the Spelling and Grammar dialog with the Spelling pane (also in Word 2013, and persisting in Word 2016). Microsoft has had a long history with panes, a history that, to me, seems to have been very conflicted. For example, moving the Mail Merge wizard to the Mail Merge pane, and then obviating the need for it with the ribbon (the pane can still be accessed, though). Personally, I find panes to be, err, a pain!

The Spelling pane is especially irritating.

Compare the pane to the old dialog:


Figure 1    Spelling and Grammar dialog vs Spelling pane

First let me say what is nice about the pane (to be fair). If you open the pane and leave it there (which I am very unlikely to do), then as you move around your document, when you place the I-beam on an error (misspelled word or grammatical/contextual error) then you immediately get the spell checking options for that error. And (in an American accent), the pronunciation of the suggested word you have selected if it was a spelling error (I do not know how this is going to help me make up my mind which is the correct word, but hey, it’s there!). And, in an insincere nod to the disabled, the Spelling pane options have accelerator keys highlighted (e.g., Change) which you cannot use (or at least, I have not figured out how to use them). Yes, I am being sarcastic. The “features” of the new Spelling pane are not really, in my mind, all that great. I could easily get by without them. Really.

What is so bad about the pane, you may ask? Well, it’s not what it’s got that’s bad, but rather what went missing from the dialog to the pane. As with the context menu, AutoCorrect is missing. As is Undo. Microsoft might argue that Undo was not used here a lot, but when I was zipping along through a long document, trying to whip the spelling into shape, then I found that I have often clicked the Change button a bit too hastily (perhaps it’s just me). Having Undo there was really a time saver. I could also immediately go to Options…, which, in all truth, I can still do in the usual way when using the pane. I could also focus only on spelling by turning the grammar checking off with the Check grammar box, which also helped me manage my workload. So yes, I miss those tools. Oh, and the keyboard accelerators actually work. How about that?

The solution

So what do we do when Microsoft throws us a curveball like this?

Do what I did with the Print dialog (you can read about it in my book or see the discussion I have in the Word uTIlities Help file)—take back what is (rightfully) ours!

The nice part about this is that you can decide how fully you want to revert—you can keep the Spelling pane and add the Spelling and Grammar dialog, or you can more fully replace the Spelling pane with the dialog.

The first part of this is to add a macro that will give us the old dialog. So, press Alt+F8. Then, in the Macro name box of the Macros dialog, type SpellingAndGrammarDialog and click on Create (Figure 2).


Figure 2    Creating the SpellingAndGrammarDialog macro

This will open the VBA Editor, with the Sub and End Sub statements for the macro already created. All you need to add is one line of code:

Sub SpellingAndGrammarDialog ()
End Sub

Press Alt+Q to get back to Word and just remember to save the Normal Template.

To add this newly created tool over and above the Spelling pane, just add it to the QAT. To replace the Spelling pane, assign the F7 key to the macro, instead of ToolsProofing. If you really can’t live without the Spelling pane now and then, you can still access it from the Review ribbon tab (modifying the ribbon to point to the macro is beyond where I want to go with this post). Now, to get back to real efficient spell checking, just press F7!