The Robservatory

Robservations on everything…

 

Microsoft Excel

An even easier way to use Excel’s Paste Special dialog

I recently explained how to use the keyboard in Excel’s Paste Special dialog box, and this is a great timesaver on its own. But I use Paste Special a lot, especially with Formats, Formulas, and Values, so I made those three even easier to use via the keyboard…

Each one has its own direct keyboard shortcut, courtesy of Keyboard Maestro. Here’s how I set it up; these instructions should work (with some changes, of course) for any app that can script keystrokes.

First, I created these macros in an Excel group, so they’re only active when Excel is frontmost (no need to create global hot keys that you only use in one program). The actual macros are pretty trivial:

  1. Send Command-Control-V to bring up the Paste Special dialog
  2. Pause just long enough for the dialog to appear onscreen
  3. Send the chosen shortcut key—T, F, or V in my vase
  4. Send the Return key to execute the action

Then I just assigned each one to the same key used within the dialog, but with Command and Option to make it usable from anywhere within Excel.

(more…)

Use the keyboard in Excel’s Paste Special dialog box

When I’m working in Excel, I spend a lot of time in the Paste Special dialog box—pasting formulas, pasting all but formats, pasting only formats, etc. You can call up the dialog with a keyboard shortcut (⌃⌘V), but it then looks like you’re stuck using the mouse, because there aren’t any keyboard shortcuts for the various actions. But really, there are…

(Note: This applies to the current version of Excel, i.e. the one in Office 365. Based on the comments, it apparently also works in Excel 2011 if you add the Command key.)

On Excel for Windows, one character in each option has an underline, indicating that option’s keyboard shortcut. The good news is that these same shortcuts work on the Mac, even though they’re not shown. (There is one apparent oversight: The O key should select Operation: None, but it doesn’t seem to work on the Mac.)

Here are all the shortcuts, graphically:

Press the highlighted key, and that action will be selected; press Return to execute the chosen command, and you can use the Paste Special dialog without ever touching the mouse. (Note that the Paste Link action executes immediately when chosen, so it’s a one-key operation.)

Because graphics are horrid for web searching, the text version of each shortcut, in alphabetical order, is shown below.

(more…)

Randomly merge lists in Excel

This morning, while working on a customer request, I had to create a list of words by randomly choosing words from two lists, and then mashing them together. This isn’t something that I’ve ever done before, and I’m not sure how relevant it might be for others, but I’m documenting it here just in case someone is searching for such a solution.

Here’s how my little test spreadsheet looked when I was done with it:

I didn’t realize it at the time, but this could be a “modern paint color” name generator…”Yes, I’d like two gallons of the Pickle-Purple, please.” Anyway, the COMBINED column contains the final result, with the FOODS and COLORS columns showing randomly-selected entries from the two lists. Each time you recalculate the sheet, all the selections will change.

The key bit is the formula to grab a random entry from the list; here’s what that looks like in cell C12 (“Apple”):

=VLOOKUP(RANDBETWEEN(B$3,B$9),B$3:C9,2)

It’s just a basic VLOOKUP that uses RANDBETWEEN to grab a random row from the lookup table. Not rocket science, but nothing I’d tried before. (For this to work, your table entries need row numbers, obviously.) The COMBINED column is just a simple text formula, i.e. =C12&"-"&F12 to combine the two random values.

I also wasn’t aware of the RANDBETWEEN function—it returns a whole number between the values you specify. That is so much easier than using RAND and then having to multiply and round off, etc.

Feel free to download the workbook if you’d like to take a look.

The Robservatory © 2017 Built from the Frontier theme