How do you customize Excel 2010 keyboard shortcuts?

44

9

How do you customize Excel 2010 keyboard shortcuts? This article from microsoft.com provides instructions for "Microsoft Office 2010" but seems only apply to Word, not Excel.

http://www.microsoft.com/athome/organization/compshortcuts.aspx

In Microsoft Office 2010:

  1. Click the File tab to open Backstage view, and then click Options.

  2. Click Customize Ribbon and then, next to the Keyboard Shortcuts heading, click Customize. You can enter the new key combination here.

Here's the dialog it points to shown in Word:

Word Customize Ribbon

But here's the same dialog shown in Excel:

Excel Customize Ribbon

Notice it looks almost exactly the same but no "Keyboard Shortcuts: Customize.." button.

So how do you customize the keyboard shortcuts in Excel?

Sam

Posted 2010-11-19T03:15:57.760

Reputation: 1 319

Right Click S - V is a little quicker than Alt-E-S-V for Paste Values... – None – 2012-11-06T06:13:16.097

see THIS INTERESTING READ

– None – 2014-04-04T07:49:17.397

Answers

21

There are 3 different kinds of keyboard shortcuts I know of:

  1. Ones like Ctrl-C for Copy
  2. Ones like Alt-E-S-V for Edit > Paste Special > Values
  3. A shortcut key combo you assign to your own macro

With # 1 I think only Word allows you to change these, e.g., you can change the shortcut for Bold from Ctrl-B to something else. I don't think Excel has ever allowed you to do this. Of course you can write a macro in Excel that changes the Bold property and then assign a keyboard shortcut to it, but that really falls under #3 above.

You use to be able to do #2 in Excel by going into Tools > Customize and then changing the location of the ampersand. For example you could change the name of the Edit menu from &Edit to &Zdit and the shortcut sequence in #2 above would be changed to Alt-Z-S-V. The only way I know to do this now would be to change the toolbar name in VBA (the old toolbars, File, Edit, etc. still exist behind the scenes in Excel 2007 and 2010 and can be accessed via code).

With #3 it's still the same as it was in earlier versions, just go into the Macro dialog and change the shortcut in Options. You only have the choice of Ctrl and one key, I believe. With VBA you can actually create longer combos like Ctrl-Shft-N (one of my favorites).

I hope that helps.

Doug Glancy

Posted 2010-11-19T03:15:57.760

Reputation: 1 756

So, if my French Excel uses "Ctrl+G" for Bold, I am not able to change it to "English" variant "Ctrl+B" (+change GotoPage also)? – Serge – 2016-03-08T17:47:11.753

"With # 1 I think only Word allows you to change these, e.g., you can change the shortcut for Bold from Ctrl-B to something else. I don't think Excel has ever allowed you to do this." -- Rubbish. I've always assigned "CTRL + SHIFT + V" to paste values only in previous versions of Excel. Very disappointed this is no longer possible. – Adambean – 2016-05-20T10:01:55.047

Here are the steps for #3:

  1. Press Alt+F8. A list of macros appears.
  2. Click a macro.
  3. Click Options.
  4. For the shortcut key, type a lowercase letter, uppercase letter, number, or symbol.
  5. Click OK.
  6. Close the list of macros.
  7. < – scenography – 2019-09-27T01:49:08.537

27

You can assign a command (like "format painter") to the quick access toolbar via the dialog shown above. Then you can use ALT + a number (varies on the position of the program in the quick access toolbar) as a shortcut!

For example, in the screenshot below, the Format Painter has been added to the quick access toolbar in the 7th place, so you can now call it via alt-7.

enter image description here

Marjolijn

Posted 2010-11-19T03:15:57.760

Reputation: 271

Oh, this is great. I've wanted to find some way to change CTRL-V to "Paste and Match Destination Formatting" (either through modifying default paste behavior or through changing keyboard shortcuts), but this is the best alternative I've found. I just hit ALT-4 and boom: exactly what I want! – BevansDesign – 2016-05-17T16:50:54.953

Wanted to note it's not the exact dialog shown above -- that is Customize Ribbon, while Quick Access Toolbar is the next choice below that. Looks identical though. Great tip! – Noumenon – 2017-09-25T17:54:23.083

4

The only (real) way is to create a macro that does what you want and assign a key combination to it. It's relatively easy to record a macro to, say, 'paste values' and then add the keyboard shortcut.

If you record a macro the first dialog allows you to assign a shortcut key, but only with the CTRL key -- and Excel doesn't tell you if you are overwriting an existing key combination (like CTRL+C). Be sure to assign it to your PERSONAL.XLSB file so it's available to all spreadsheets.

Haig Johnson

Posted 2010-11-19T03:15:57.760

Reputation: 51

3

To me all above answers are unsatisfactory because they limit what combinations I can use as custom shortcuts or what actions I can target, or they require way too much work for something that should be simple.

My solution is to use AutoHotkey to "translate" my desired key combinations into the existing shortcuts that Outlook 2016 (or Excel 2016 or what have you) expects for those actions.

So for example whenever I have an e-mail selected and press [Ins] I want it to be marked as read, therefore I have the AutoHotkey script replace that with a [Ctrl-Enter]. Or whenever I press [Ctrl-f] I want to go to the search box (d'oh!) but Outlook expects a [Ctrl-e] for that so I have the script send it a [Ctrl-e]. The script looks like so:

SetTitleMatchMode RegEx
#IfWinActive  - Outlook$
Ins::Send ^{Enter}
^f::Send ^e
#IfWinActive

The "#IfWinActive" ensures these particular key-press replacements only apply when the active window's title ends with the string " - Outlook" (the end is marked by the dollar sign, which works because I've enabled Regular Expression based string matching mode above). Then I disable the active window title detection with the empty #IfWinActive at the end.

Also, once the #IfWinActive is hit, I know exactly which window will receive the keys I'm sending, so I don't have to do anything special beyond a simple "Send <keys>" in response to each real key-press that is to be replaced.

Don Joe

Posted 2010-11-19T03:15:57.760

Reputation: 49

1

For those who are wondering, AutoHotkey can be downloaded here.

– Donald Duck – 2019-01-11T19:54:10.953

Thanks! Integrated link into the text. (I'm always paranoid about adding links and breaking some rule about promoting off-site materials. :) ) – Don Joe – 2019-01-18T10:22:01.183

3

Addressing the 'Ones like Alt-E-S-V for Edit > Paste Special > Values' (number 2 above - while not strictly a shortcut but still a quick way to do things with the keyboard).

These can be modified, but it has significant overhead to set-up the first time. The process involves creating an Add-In with special XML added that adds to or modifies the ribbon.

Instructions & tools can be found here. This was the only way I ever found to change the Ribbon in 2007, however it still works exactly the same way with 2010.

Andrew Crisp

Posted 2010-11-19T03:15:57.760

Reputation: 31

does this approach with the Custom UI Editor tool (which seems to not have been updated for many years) also work with MS Office 2013? – MostlyHarmless – 2015-07-24T16:15:07.040