How to Create Custom Keyboard Shortcuts in Excel 2007?
If you are new here, Subscribe to Full Feeds or by Email or follow us in Twitter and receive free daily updates on Office and Windows Tips, Tricks and Tweaks..
Excel has lot of functions and most of the functions doesn’t have any keyboard shortcuts as they are used in only very specific conditions and cases. But for some cases, you may be required to do those tasks quite often and you may like if you have a keyboard shortcut.
Let me give an example, we have covered earlier how to convert rows to columns or columns to rows in Excel and while using formulas. Its just the transpose feature in the special copy feature. But if you are required to use this task quite often, you may create your own keyboard shortcut to do the transpose paste like Ctrl + T.
So how to create custom keyboard shortcuts in Excel 2007?
Before we proceed on the section of how to do create custom keyboard shortcuts, this method is done using the macros in Excel. For some Macros may not be enabled (you may not be able to see Developer tab), please refer to our previous post on How to Enable Macro Features and Settings in Excel from Office 2007?
To create a custom keyboard shortcut, go to the Developer tab and press Record Macro.
Give the details of the macro, the keyboard shortcut, store macro in and description.
Side Note: If you have not saved any macros before in Personal Macro Workbook (Personal.xlsb), you may get the message like this. “Do you want to save the changes made to Personal Macro Workbook? If you click Yes, It will be available next time you start Microsoft Office Excel”. Click here to know more about Working with Personal Macro Workbook in Excel
Next, perform the function that you want to be repeated by a keyboard shortcut (Make sure you have done whatever function that usually precedes the function that is being recorded, like copying some cells in this case) . In our example, right click in the place where you wanted to paste special with transpose. Select Paste Special -> Transpose –>Ok. Stop recording the macro.
Now you can use the keyboard shortcut you have mentioned to perform this custom function.
[...] Learn how you can create a custom keyboard shortcut in Excel to perform this [...]
Thanks a ton for the tip man !! really usefull !!
Great post, I did not realise this was possible, I just thought its microsoft and touch luck, use what they given us.
[...] Create your own keyboard shortcuts in Excel 2007, knowing a few keyboard shortcuts in excel is a huge help. Lyte Byte describes a nifty way to create your own key board shortcuts in excel 2007 using macros. [...]
These tips are useful and will make my job much easier. I’m improving my excel skills and just glad I stumbled upon this post. keep it up!
Elsie
very nice post…
This info just saved me hours of cutting and pasting for a data analysis project I am working on. Thank you so much! If I knew you I would buy you a beer!
I tried to create this macro and I found that it would only record the Transpose function from the dummy cells that I used to create the macro. Is there something I am missing?
Clara – Macro will record anything you did while it was recording.
To get a “general” macro, you must NOT do things like selecting cells during recording, otherwise when you run macro it will do exact same things, for example:
step 1. click cell A5
step 2. press shift
step 3. click cell D8
step 4. …
So what you want when creating macro is NOT DO steps like 1/2/3 in my example.
I hope this helps, and to Mr. Byte – nice post! I forgot all about using macros for simple things like this one, good work!
After setting up the macro, I performed a test. When I tried to undo the command just performed, I couldn’t. The undo function is disabled.
Any ideas on why or how to change?
Thank You
David
@ David
Remember Macro outputs can never be undone (except for the workaround that you could save your file before testing your macro and revert to the saved file for an undesirable change done after executing the macro).
Anyone needing any help in designing or creating macros, would be more than happy to help