assign keyboard shortcut to macro excel

How to Run a Macro in Microsoft Excel by Assigning a Shortcut Key

assign keyboard shortcut to macro excel

Last updated on April 14, 2023 By Victor Chan

Excel macros are potent tools that can help you automate repetitive tasks or complex workflows, such as formatting data, creating charts, or updating pivot tables.

But how do you run your macros quickly and conveniently? Do you always have to go to the Developer tab and click the Macros button? Or do you have to remember the name of the Macro and type it in the Run dialog box?

There is a better way: macro shortcut keys. Macro shortcut keys are combinations of keys that trigger a macro when you press them.

For example, you can assign Ctrl+Shift+C to run a macro that copies a range of cells and pastes them as values. Or you can assign Ctrl+R to run a macro that refreshes all your pivot tables. I personally find shortcut keys fun to create and use. I think you will too!

If you are familiar with Excel macros, then using macro shortcut keys can make your work faster and easier, especially if you have to run the same macros frequently or on different workbooks.

In this article, you will learn how to create and use macro shortcut keys in Excel using three methods: the Macro Options window, the Application.OnKey method with VBA, and the Quick Access Toolbar. You will also learn some tips and tricks to make the most of your macro shortcut keys.

By the end of this article, you will be able to automate your Excel tasks with macro shortcut keys and save time and effort. Let's get started!

Method 1: The Macro Options Window

The Macro Options window is a simple and convenient way to assign a shortcut key to a macro. You can access it from the Developer tab, the View tab, or the keyboard shortcut Alt+F8. Here are the steps to create a macro shortcut key using this method:

  • 1. Go to the Developer tab and click on the Macros button. Alternatively, press Alt+F8 to open the Macro dialog box.
  • 2. Select the Macro that you want to assign a shortcut key to and click on Options.
  • 3. In the Macro Options window, enter a letter, number, or symbol in the Shortcut key box. The shortcut key will be in the form of Ctrl+<letter> or Ctrl+Shift+<letter>. For example, if you enter C, the shortcut key will be Ctrl+C. If you enter Shift+C, the shortcut key will be Ctrl+Shift+C.
  • 4. Click OK to save your changes and close the window.

Now you can run your macro by pressing the shortcut key that you assigned. The shortcut key will be displayed in the Macro dialog box and in the Visual Basic Editor screen.

To delete or change a shortcut key, repeat the steps above and either delete or replace the character in the Shortcut key box.

Here is an example of how to create a macro shortcut key using this method:

assign keyboard shortcut to macro excel

In this example, we have created a macro named CopyPasteValues that copies a range of cells (A1:E10) and pastes them as values in another range (G1:K10). We have assigned Ctrl+Shift+C as the shortcut key for this macro using the Macro dialog box.

Here’s the code for this basic macro:

To run this macro, we just need to press Ctrl+Shift+C on our keyboard.

As you can see, this method is very easy and straightforward. However, it has some limitations:

  • You can only use letters, numbers, or symbols as shortcut keys. You cannot use function keys (F1-F12), arrow keys, or other special keys like Home, End, Page Up, or Page Down.
  • You can only use Ctrl or Ctrl+Shift as modifiers. You cannot use Alt or other combinations.
  • You may accidentally override existing shortcut keys that are used by Excel or other applications.

If you want more options and flexibility with your macro shortcut keys, you can use the following method: the Application.OnKey method in VBA.

Method 2: The Application.OnKey Method in VBA

The Application.OnKey method is another way to create macro shortcut keys using VBA code. To use this you need to be in the VB Editor.

When I first tried this method I was excited because for the first time I realized I could create powerful custom keyboard shortcuts!

You see, this method gives you more options and flexibility with your keyboard shortcuts, such as using function keys (F1-F12), arrow keys, or other special keys like the ALT key.

Here are the steps to create a macro shortcut key using Application.OnKey:

  • 1. Go to the Developer tab and click on Visual Basic. Alternatively, press Alt+F11 to open the Visual Basic Editor (VBE).
  • 2. In the VBE, create a new module or use an existing one where you want to write your code.
  • 3. Create a new subroutine (Macro) and name it something like CreateShortcut (or whatever you choose).
  • 4. In your subroutine, type Application.OnKey followed by a space. This is the VBA method that allows you to create or delete keyboard shortcuts for macros.
  • 5. The Application.OnKey method has two parameters: ‘Key’ and ‘Procedure’. The Key is the keyboard shortcut combination represented by key codes. The Procedure is the name of the Macro that will be called when you press the Key.
  • 6. Enclose both parameters in quotation marks and separate them by a comma. For example: Application.OnKey "+%v", "CopyPasteValues". This means that when you press Ctrl+Shift+V, it will run a macro named CopyPasteValues.
  • 7. Press Enter.

This is what you’ll see in code:

First, run the new macro CreateShortcut(). Place the cursor inside the subroutine and press F5. This binds the macro CopyPasteValues to the keyboard shortcut Alt+Shift+V.

Now you can run the macro “CopyPasteValues” by pressing the shortcut key that you assigned. In this case, we used Alt+Shift+V.

To remove a shortcut key, use Application.OnKey with only one parameter:

This means that the shortcut key Ctrl+Shift+C will be disabled.

Now you might be wondering what keys you can use for shortcuts and how to assign them. Fear not. Here’s a table of shortcut key combinations for your inspiration!

Table of Shortcut Key Combinations for Application.OnKey method

Shortcut to run macro that refreshes all pivot tables.

Here’s another example of how to create a macro shortcut key using the Application.OnKey method, this time for something that could be more useful than copying and pasting values.

In this example, we create a new macro named RefreshPivotTables that refreshes all the pivot tables in the active workbook.

Then we assign Ctrl+Shift+R as the shortcut key for this macro.

Here is the modified VBA code for binding and unbinding shortcuts to both macros:

After running the CreateShortcut macro, we can use the new keyboard shortcuts. To run the CopyPasteValues macro we press ALT+SHIFT+V and to run the RefreshPivotTables macro we press CTRL+SHIFT+R.

assign keyboard shortcut to macro excel

As you can see, this method gives us more options and flexibility with our macro shortcut keys. However, it also has some drawbacks:

  • You need to know the key codes for each key that you want to use as a shortcut key. You can find a list of key codes here . This page provides a table of key codes for different keys and modifiers, such as function keys, arrow keys, Ctrl, Shift, Alt, etc. For example, the key code for F5 is "{F5}", the key code for Ctrl is "^", and the key code for Ctrl+F5 is "^{F5}".
  • You need to write VBA code for each shortcut key that you want to create or delete. This can be tedious and error-prone if you have many macros and shortcut keys.
  • You need to run the code that creates the shortcut keys every time you open the workbook or start Excel. Otherwise, the shortcut keys will not work.

Keycodes For Application.OnKey Are Not Case Sensitive

If you are sharp-eyed, you may have noticed that we used Application.OnKey with a lower case “v” and an upper case “R”.

In the context of Excel VBA's Application.OnKey method, the case of the letter (uppercase or lowercase) does not matter.

So for instance, the key codes "+^{R}", "+^r", and "+^R" are all valid and represent the same keyboard shortcut: Shift + Ctrl + R.

Here is an example of assigning a macro to the Shift + Ctrl + R shortcut using each of these variations:

All three of these variations assign the same keyboard shortcut (Shift + Ctrl + R) to the macro named "MacroName".

Just remember to replace "MacroName" with the actual name of the macro you want to assign the shortcut to. And unbind the shortcut keys when you don’t need to use them.

Method 3: Assign a Keyboard Shortcut to a Macro in the Quick Access Toolbar

The Quick Access Toolbar is the line of icons found at the very top of the Excel window. It uses a built-in shortcut combination to run commands or macros.

You can customize it by adding buttons that will run macros when you click on them. You need an existing macro to use.

assign keyboard shortcut to macro excel

To assign a keyboard shortcut combination to a macro button on the QAT, follow these steps:

  • Click the File tab and then click Options . This will open the Excel Options dialog box.
  • In the Excel Options dialog box, click Quick Access Toolbar on the left side. This will show you the options for customizing the QAT.
  • In the Choose commands from drop-down list, select Macros . This will show you all the macros that are available in the current workbook or in your Personal Macro Workbook.
  • Select the macro that you want to add to the QAT and click Add . The macro will be added to the list of buttons on the right side.
  • To change the name or icon of the macro button, select it in the list and click Modify . This will open a small window where you can type a new name and choose a new icon for the button. Click OK when you are done.
  • Click OK again to close the Excel Options dialog box.

You can now see and run the macro on the QAT by clicking on its button.

assign keyboard shortcut to macro excel

You can also use a keyboard shortcut combination to run the macro. The keyboard shortcut is in the form of Alt + n , where n is the button number.

For example, if your macro button is the fifth button on the QAT, then its keyboard shortcut is Alt + 5 . You can see the number above your macro button when you press and release the Alt key on your keyboard.

You can also rearrange or remove buttons by using the up and down arrows or the remove button in the Excel Options dialog box.

Just remember that if you assign a macro from your Personal Macro workbook then it will be available whenever you open Excel. But if you assign a macro from a specific workbook to your Quick Access Toolbar, that macro will only be available if the corresponding workbook is open.

Tips and Tricks for Using Macro Shortcut Keys

Now that you know how to create and use macro shortcut keys in Excel, here are some tips and tricks to make the most of them:

  • Be careful not to override existing shortcut keys that you frequently use, such as Ctrl+C to copy or Ctrl+Z to undo. One way to avoid this is to use more complex combinations, such as Ctrl+Shift+<letter> or Ctrl+Alt+<letter>.
  • You can also use function keys (F1-F12), arrow keys, or other special keys as shortcut keys, but make sure they don't conflict with other applications or commands. For example, F1 is usually used for Help, and F5 is used for Refresh.
  • You can assign multiple shortcut keys to the same Macro or assign the same shortcut key to different macros in different workbooks. However, this can cause confusion and errors, so it's better to keep your shortcut keys consistent and unique.
  • You can use the Application.OnKey method to create dynamic shortcut keys that change depending on the context or condition. For example, you can use an If statement to assign different shortcut keys based on the value of a cell or the name of the active workbook.
  • You can also use the Application.OnKey method to create toggle switches that turn a macro on or off with the same shortcut key. For example, you can use a Boolean variable to store the state of the Macro and change it every time you press the shortcut key.

Bonus: Macro Toggle Switch

Here's an example of using Application.OnKey to create a toggle switch that turns a macro on or off with the same shortcut key. In this example, we will use the shortcut Ctrl + T to toggle the macro execution.

First, let's create a macro named "ToggleMacro" that toggles the execution of another macro called "MyMacro":

Now, we need to assign the Ctrl + T shortcut to the "ToggleMacro" using Application.OnKey:

Call the AssignShortcut macro to assign the shortcut. After that, pressing Ctrl + T will toggle the execution of "MyMacro" on and off. You can test this by calling the "MyMacro" macro, which will execute only if the MacroEnabled variable is set to True.

Remember to replace the code inside "MyMacro" with your own macro code.

Here are some useful references for learning more about macro shortcut keys in Excel:

  • Microsoft Online Documentation: Application.OnKey method
  • Microsoft Support: Customize keyboard shortcuts (US)

Summary: Run Your Macros with Keyboard Shortcuts!

Macro shortcut keys are a great way to run your macros faster and easier in Excel. They can save you time and effort, especially if you have to run the same macros frequently or on different workbooks. I love using keyboard shortcuts to speed up my workflow. I hope you do too!

In this article, you learned how to create and use VBA macro shortcut keys in Excel using the Macro Options window, the Application.OnKey method, and the Quick Access Toolbar. You also learned some tips and tricks to make the most of your macro shortcut keys.

Now it's your turn: try creating and using some macro shortcut keys for your own macros and see how they improve your workflow. And if you need more help with macros or VBA, check out our other articles and tutorials on this topic.

Thank you for reading and happy Exceling!

FAQs: Excel Macro Shortcut Keys

Here are some frequently asked questions about macro shortcut keys in Excel:

Q: What are the pros and cons of using the Macro Options Window method for keyboard shortcuts?

  • Easy to set up, making it appealing for those intimidated by writing code.
  • Limited key options, as you can't use special keys such as Home, End, or Page Up.
  • Potential conflicts with existing user-assigned shortcuts, leading to unpredictable behavior based on alphabetical macro name order.
  • Difficulty in tracking assigned shortcuts, as there is no built-in list or index. You can create a macro to create a list, but that's extra work.

Q: What are the pros and cons of using the Application.OnKey method for keyboard shortcuts?

  • Easy to find assigned shortcuts using the Find window (Ctrl+F) in the VBA Editor by searching VBA code for the word "onkey."
  • Greater control over shortcut priority and behavior. Shortcuts created with OnKey take priority over those created using the Macro Options window. This means that using the OnKey method will ensure that the Macro it refers to is executed when the shortcut key is pressed.
  • Easy to delete or remove shortcuts, with options to create macro buttons or toggles.
  • More key options, including special keys (Page Up, Page Down) and the Ctrl+Alt combination.
  • Dynamic shortcuts can change the called procedure based on workbook conditions.
  • Requires updating code if the macro name changes.
  • Must run the Macro to assign shortcuts.

Q: Which method is recommended for creating keyboard shortcuts in Excel macros?

A: While both methods have their merits, the OnKey method is generally preferred due to its ease of finding and managing shortcuts, greater key options, and better control over shortcut behavior. This method is especially recommended for setting up multiple shortcuts in your Personal Macro Workbook.

Q: How do I find out what shortcut keys are assigned to macros in Excel?

A: There are two manual ways to find out what shortcut keys are assigned to macros in Excel:

  • In the main Excel window , click on the Developer tab and click on Macros. This brings up the Macro dialog box, in which you will see a list of macros. Click on a macro and click on Options. The Macro Options dialog box shows the assigned shortcut key (if any).
  • In the Visual Basic Editor (VBE) , open the module where your macros are stored. In the code window, you will see comments at the top of each Macro that indicate the shortcut keys that were assigned when the macro was recorded (if any). This isn't updated when the shortcut key is changed, so don't rely on these comments.

Q: How do I change or delete a shortcut key assigned to a macro in Excel?

A: There are two ways to change or delete a shortcut key assigned to a macro in Excel, depending on how the shortcut key was assigned:

  • Macro dialog box: Go to the Developer tab and click on Macros. In the Macro dialog box, select the Macro that you want to change or delete the shortcut key for and click on Options. In the Macro Options window, either change or delete the character in the Shortcut key box and click OK.
  • Application.OnKey: Go to the Visual Basic Editor (VBE) and open the module where your macros are stored. In the code window, either change or delete the line of code that uses the Application.OnKey method to create or delete the shortcut key for the Macro.

Q: How do I run a macro without a shortcut key in Excel?

A: There are several ways to run a macro without a shortcut key in Excel:

  • Go to the Developer tab and click on Macros. In the Macro dialog box, select the Macro that you want to run and click on Run.
  • Go to the View tab and click on Macros. In the Macro dialog box, select the Macro that you want to run and click on Run.
  • Press Alt+F8 to open the Macro dialog box. Select the Macro that you want to run and click on Run.
  • Go to the Visual Basic Editor (VBE) and open the module where your macros are stored. In the code window, place your cursor inside the Macro that you want to run and press F5.

Q: How do I record a macro with a shortcut key in Excel?

A: There are two ways to record a macro with a shortcut key in Excel:

  • Go to the Developer tab and click on Record Macro. In the Record Macro dialog box, enter a name for your Macro and optionally a description. In the Shortcut key box, enter a letter, number, or symbol for your shortcut key. The shortcut key will be in the form of Ctrl+<letter> or Ctrl+Shift+<letter>. Click OK and perform your actions. When you are done, click on Stop Recording.
  • Press Alt+T+M+R to start recording a macro. In the Record Macro dialog box, enter a name for your Macro and optionally a description. In the Shortcut key box, enter a letter, number, or symbol for your shortcut key. The shortcut key will be in the form of Ctrl+<letter> or Ctrl+Shift+<letter>. Click OK and perform your actions. When you are done, press Alt+T+M+R again to stop recording.

assign keyboard shortcut to macro excel

Connect on YouTube , LinkedIn , Twitter .

Hey, I'm Victor Chan

Are you struggling with complex Excel tasks? Feeling overwhelmed by spreadsheets that are hard to use?

Many people believe mastering Excel is about learning shortcuts, functions, and formulas. But this overlooks the importance of building practical, real-world applications. It's not just about knowing the tools. It's about using them effectively.

That's where I come in. You'll get a unique perspective to Excel training from me. I have over 20 years of experience at Deloitte and two global tech companies. And I know what can make a difference in your career.

Let me help you integrate Excel into your professional life. Starting today. Read one of my articles, watch one of my videos. Then apply the new technique to your work. You'll see the difference immediately!

Recommended Posts

assign keyboard shortcut to macro excel

6 Best Excel Courses 2023: Smart Ways to Easily Master Spreadsheets

assign keyboard shortcut to macro excel

21 Top Careers That Need Microsoft Excel Skills (with Salary Data)

assign keyboard shortcut to macro excel

Why Learn Excel? Here Are the Top 15 Epic Benefits of Mastering Excel

assign keyboard shortcut to macro excel

LEARN HOW TO AUTOMATE YOUR EXCEL WORKFLOW

Discover the PROVEN Blueprint for transforming your Excel skills, supercharging your productivity, and standing out in your career! My course helps you to learn Excel VBA and save hours of time even if you have zero prior experience with programming.

assign keyboard shortcut to macro excel

GET EXPERT EXCEL HELP FOR YOUR SPECIFIC NEEDS

Solve tricky Excel problems and take your work to the next level! Get customized solutions for your unique needs. Save time and gain insights with truly expert Excel solutions from only $97 per task.

assign keyboard shortcut to macro excel

Customizable Project Timeline Template »

Get a clear overview of your project progress using the Excel project timeline. Use it to communicate the big picture, track task progress, and stay on top of your project goals. Stay organized with our project timeline!

assign keyboard shortcut to macro excel

Code in Excel VBA With Confidence »

Our cheat sheets provide quick and easy reference to commonly used Excel VBA concepts and code snippets.

Unlock new levels of productivity and efficiency with our cheat sheets, and write VBA code like a pro in no time.

RECOMMENDED READING

assign keyboard shortcut to macro excel

BEST Excel Courses to Master Spreadsheets »

Are you looking to upskill and stay ahead of the curve? Excel is a powerful tool that keeps growing in demand. We round up the best online courses for learning Excel.

assign keyboard shortcut to macro excel

Why Learn Excel? The Top 15 Epic Benefits of Mastering Excel »

Are you looking to up your spreadsheet game? Excel is an invaluable tool that can help you stay organized and save time. From data analysis to budgets, Excel can do it all!

assign keyboard shortcut to macro excel

21 Top Careers That Need Microsoft Excel Skills (with Salaries) »

Today, having Excel skills is more critical than ever. Those who know how to use Excel are more likely to find higher-paying jobs. And get promoted faster.

JOIN FREE EMAIL NEWSLETTER

Step up your Excel game! Join our free email newsletter and get updates on how to become more awesome at Excel.

SOCIAL MEDIA

Site Policies

© 2011-2023 Launch Excel

  • Ablebits blog
  • Excel macro

How to run macro in Excel and create your own macro button

Svetlana Cheusheva

In this tutorial, we'll cover many different ways to run a macro in Excel - from the ribbon and VB Editor, with a custom keyboard shortcut, and by creating your own macro button.

Though running an Excel macro is a simple thing for experienced users, it might not be immediately obvious to beginners. In this article, you will learn several methods to run macros, some of which may completely change your way of interacting with Excel workbooks.

How to run a macro from Excel ribbon

One of the fastest ways to execute VBA in Excel is to run a macro from the Developer tab. If you have never dealt with VBA code before, you may need to activate the Developer tab first. And then, do the following:

Running a macro from the Developer tab

Tip. If the Developer tab is not added to your Excel ribbon, press Alt + F8 to open the Macro dialog.

Run a macro with custom keyboard shortcut

If you execute a certain macro on a regular basis, you can assign a shortcut key to it. A shortcut can be added while recording a new macro and to an existing one. For this, carry out these steps:

  • On the Developer tab, in the Code group, click Macros .

Click Options to edit the macro parameters.

  • For lowercase letters, the shortcut is Ctrl + letter .

Assign a shortcut to a macro.

  • Close the Macro dialog box.

Tip. It is recommended to always use uppercase key combinations for macros ( Ctrl + Shift + letter ) not to override the default Excel shortcuts. For example, if you assign Ctrl + f to a macro, you will lose the ability to call the Find and Replace dialog.

How to run macro from VBA Editor

If you aim to become an Excel pro, then you should definitely know how to start a macro not only from Excel, but also from the Visual Basic Editor. The good news is that it's a lot easier than you might expect :)

  • Press Alt + F11 to launch the Visual Basic Editor.
  • In the Project Explorer window on the left, double-click the module containing your macro to open it.
  • On the menu bar, click Run > Run Sub/UserForm .
  • On the toolbar, click the Run Macro button (green triangle).

Alternatively, you can use one of the following shortcuts:

  • Press F5 to run the entire code.
  • Press F8 to run each code line separately. This is very useful when testing and debugging macros.

Running a macro from the VBA Editor

Tip. If you like operating Excel from you keyboard, this tutorial may come in handy: 30 most useful Excel keyboard shortcuts .

How to create a macro button in Excel

The traditional ways of running macros are not hard, but still might present a problem if you are sharing a workbook with someone who has no experience with VBA - they simply won't know where to look! To make running a macro really easy and intuitive for anyone, create your own macro button.

Inserting a button in a worksheet

  • Click anywhere in the worksheet. This will open the Assign Macro dialogue box.

Assign a macro to a button in Excel.

  • If the text does not fit in the button, make the button control bigger or smaller by dragging the sizing handles. When finished, click anywhere on the sheet to exit the edit mode.

Excel button to run macro

Tip. You can also assign a macro to an existing button or other Form controls such as spin buttons or scrollbars. For this, right-click the control inserted in your worksheet and choose Assign Macro from the pop-up menu.

Create a macro button from a graphic object

Regrettably, it is not possible to customize the appearance of button controls, because of which the button we created a moment ago does not look very nice. To make a really beautiful Excel macro button, you can use shapes, icons, images, WordArt and other objects.

As an example, I'll show you how you can run a macro by clicking a shape:

Inserting a shape

  • In your worksheet, click where you want to insert the shape object.
  • Format your shape-button the way you want. For example, you can change the fill and outline colors or use one of the predefined styles on the Shape Format tab. To add some text to the shape, simply double-click it and start typing.

Assigning a macro to the shape

How to add a macro button to Quick Access Toolbar

The macro button inserted in a worksheet looks good, but adding a button to each and every sheet is time-consuming. To make your favorite macro accessible from anywhere, add it to the Quick Access Toolbar . Here's how:

  • Right-click the Quick Access Toolbar and choose More Commands… from the context menu.
  • In the Choose commands from list, select Macros .

Adding a macro button to Quick Access Toolbar

  • Click OK twice to close both dialog windows.

Quick Access Toolbar button to run a macro.

How to put a macro button on Excel ribbon

In case you have a few frequently used macros in your Excel toolbox, you may find it convenient to have a custom ribbon group, say My Macros , and add all popular macros to that group as buttons.

First, add a custom group to an existing tab or your own tab. For the detailed instructions, please see:

  • How to create a custom ribbon tab
  • How to add a custom group

And then, add a macro button to your custom group by performing these steps:

  • Right-click the ribbon, and then click Customize the Ribbon .
  • In the list tabs on the right, select your custom group.
  • In the Choose commands from list on the left, select Macros .
  • In the list of macros, choose the one you wish to add to the group.
  • Click the Add button.

Adding a macro to a custom ribbon group

  • Click OK to save your changes and close the main dialog box.

Three macro buttons are added to the Excel ribbon.

How to run a macro on opening a workbook

Sometimes you may want to run a macro automatically on opening a workbook, for example, to display some message, run script or clear a certain range. This can be done in two ways.

Run macro automatically by using Workbook_Open event

Below are the steps to create a macro that automatically runs whenever you open a specific workbook:

  • Open the workbook in which you want the macro to be executed.
  • Press Alt + F11 to open the Visual Basic Editor.
  • In the Project Explorer, double click ThisWorkbook to open its Code window.
  • In the Object list above the Code window, select Workbook . This creates an empty procedure for the Open event to which you can add your own code like shown in the screenshot below.

Run a macro on opening a workbook

For example, the following code will display a welcome message each time the workbook is opened:

Trigger macro on workbook opening with Auto_Open event

Another way to run a macro automatically on workbook opening is by using the Auto_Open event. Unlike the Workbook_Open event, Auto_Open() should sit in a standard code module, not in ThisWorkbook .

Here are the steps to create such a macro:

  • In the Project Explorer , right-click Modules , and then click Insert > Module .
  • In the Code window, write the following code:

A macro runs automatically whenever the workbook is opened.

Here's an example of the real-life code that displays a message box on workbook opening:

Note! The Auto_Open event is deprecated and available for backwards compatibility. In most cases, it can be replaced with the Workbook_Open event. For more information, please see Workbook_Open vs. Auto_Open .

The message box is displayed every time you open the workbook.

Now that you know lots of ways to run a macro in Excel, you just need to choose the one best suited for your needs. I thank you for reading and hope to see you on our blog next week!

You may also be interested in

  • Excel macro tutorial for beginners
  • How to record a macro in Excel
  • How to insert VBA code
  • How to enable macros in Excel
  • Personal Macro Workbook in Excel
  • User-defined functions vs VBA macros: pros and cons

Table of contents

Ablebits.com website logo

13 comments

assign keyboard shortcut to macro excel

I have created some macro buttons on Excel Quick access toolbar. But if I move my Macro.xlam to other folder (change path), although I have already loaded Macro.xlam (by Developer/Excel Add-in), the macro buttons could not run. How could I do that only load the Macro.xlam and run the macro buttons, not depend on the file location? Thank you.

assign keyboard shortcut to macro excel

Hi! In order for Excel to find and automatically load your XLAM file, you must tell Excel where the file is located. If you have moved the file to another location, load it again by using the Developer menu.

assign keyboard shortcut to macro excel

I wrote a macro to hide certain rows and columns, print a specific selection and then unhide the pertinent columns and save the workbook. All this is attached to a button. When it prints I get blank pages

Range("b1:F117").Select Selection.PrintOut Copies:=1, Collate:=True

Any suggestion regarding what I am doing wrong?

Your request goes beyond the advice we provide on this blog. If you have a specific question about the operation of a function or formula, I will try to answer it.

assign keyboard shortcut to macro excel

Is it possible to assign a macro button to a toolbar that can then be opened on any pc? It seems that when I forward my document with the macro buttons added to the toolbar, they disappear when the new user opens the document however the functions are still seen as listed macros in the document.

Please help

Hi! With a usual Excel file, you cannot transfer your toolbar settings to another user.

assign keyboard shortcut to macro excel

I need to run a macro to export a document to PDF, but I need to save to a different location each time I run it. Is this a possibility? I would ideally like to link this macro to a button.

Hello! To store the macros you use frequently, I recommend the Personal Macro Workbook. For more information, please visit: Personal Macro Workbook in Excel - make macros available in all workbooks .

assign keyboard shortcut to macro excel

How do I format the TEXT in a macro button, say BOLD, or FONT 14,,,etc

assign keyboard shortcut to macro excel

Right click/ edit text/ then just make the changes you want

assign keyboard shortcut to macro excel

Hi Svetlana!

Thank you for this useful post. I created a macro spreadsheet with keyboard shortcuts. I would like to ask if it is possible to convert those shortcuts into buttons in ribbon? And then have those shortcuts removed? ( I want to share the sheet with others, but don't want them to accidentally press those keys)

assign keyboard shortcut to macro excel

Yes, I found :)

assign keyboard shortcut to macro excel

Good Afternoon Svetlana,

I am trying to write two separate if statements with a nested LOOKUP so the responses in the relating cells don't return with a column heading nor an #N/A reply.

The first LOOKUP statement is as follows: =LOOKUP(2,1/($O$6:$O$19=Q2),$M$6:$M$19)

If the response is an amount, it lists the last amount, but if there isn't an amount -or the range is blank, I want it to reply with 0.00

The second LOOKUP statement is as follows: =LOOKUP(2,1/(K:K""),K:K)

The response is a date, but if there has not been a payment in the affecting range, I want it to reply with "No Payment Received"

Please help. Regards, Roger

Post a comment

Stack Exchange Network

Stack Exchange network consists of 183 Q&A communities including Stack Overflow , the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

How can I assign Excel keyboard shortcuts for macros that use keys other than Ctrl + {alpha character}

I would like to assign keyboard shortcuts to Excel macros that use keys other than the standard Ctrl + {alpha character}. Based on this post it sounds like it is possible but I am not sure how:

There are 3 different kinds of keyboard shortcuts I know of: Ones like Ctrl-C for Copy Ones like Alt-E-S-V for Edit > Paste Special > Values A shortcut key combo you assign to your own macro . . . 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).

Link to original

Doug, would you mind providing additional detail?

  • microsoft-excel
  • keyboard-shortcuts
  • microsoft-excel-2010

Community's user avatar

  • That's a 4 1/2 year old post. Doug hasn't visited the site in almost a year. Can you explain what aspect you're having difficulty with? –  fixer1234 Mar 26, 2015 at 20:34

You can use OnKey to assign a macro to just about any single keystroke. See:

https://www.youtube.com/watch?v=MbHYJOvdv8s

Gary's Student's user avatar

  • Please quote the essential parts of the answer from the reference link(s), as the answer can become invalid if the linked page(s) change. –  DavidPostill ♦ Mar 26, 2015 at 21:58

You must log in to answer this question.

Not the answer you're looking for browse other questions tagged microsoft-excel keyboard-shortcuts microsoft-excel-2010 vba macros ..

  • The Overflow Blog
  • Who owns this tool? You need a software component catalog
  • Down the rabbit hole in the Stack Exchange network
  • Featured on Meta
  • Upcoming privacy updates: removal of the Activity data section and Google...
  • Changing how community leadership works on Stack Exchange: a proposal and...

Hot Network Questions

  • Why was the standard distance set to 10 parsec and not simply to 1 parsec?
  • Newbie: How to identify a chip on a board, what is the best strategy?
  • Were any U.S. founding fathers present at the storming of the Bastille?
  • Does over refrigerator cabinet need support at both ends?
  • Is TSA Pre check a security risk?
  • Translating a sentence with a time element into first order logic
  • Can an Eidetic Spellcaster benefit from the Geometer feature that allows one page per spell in the spellbook?
  • Why did Jesus choose to perform his first miracle at the wedding in Cana by turning water into wine?
  • What is a "consecutive stay plan"?
  • How are apps selected to be pre-installed
  • What is the state space of digit recognition agent on a grid of 28 x 28 pixels?
  • Is Freyd's thesis available online anywhere?
  • How to revive an os after end-of-life
  • Why color depends on frequency and not on wavelength?
  • The Purpose of Writing
  • Emotive Plate Eyes
  • Is there a way to do Tukey's HSD test for the combined results of a one-way ANOVA and a two-way ANOVA? Or should both be followed by separate tables?
  • Inverse of a special triangular matrix
  • Why deplane all passengers and not the troublemaker?
  • A set of rotated/reflected L-shapes
  • Understanding the joke, "Make an 'ell, I say" (from The Crux)
  • Will there be another joint International Space Station (ISS) after the current is retired?
  • A canal between two rivers
  • What's the source of John Adams's quote against the two-party system?

assign keyboard shortcut to macro excel

MrExcel.com - Your One Stop for Excel Tips & Solutions

Assign a Macro to a Shortcut Key

November 10, 2021 - by Bill Jelen

Assign a Macro to a Shortcut Key

Problem : I need a fast way to run a macro that I’ve added to the Personal Macro Workbook.

Strategy : You can assign up to 26 macros to Ctrl+Shift+A through Ctrl+Shift+Z . Follow these steps.

1. Press Alt+F8 to display a list of macros.

2. Click on the desired macro name.

3. Click the options button. In the Options dialog, type a shortcut key. Note to assign a macro to Ctrl+Shift+S , you simply type Shift+S into the box. Click OK.

Additional Details : You can assign a macro to the unshifted shortcut keys. But, if you assign the ToggleSign macro to Ctrl+S , you will not be able to Save using Ctrl+S anymore. Plus, any time that you try to Save with Ctrl+S , you will be toggling the sign of selected cells.

This article is an excerpt from Power Excel With MrExcel

Title photo by Aryan Dhiman on Unsplash

  • Chat with a consultant

ARCHIVED: In Microsoft Excel, how do I assign a shortcut key to a macro?

In Microsoft Excel, it is often easier to assign a macro to a shortcut key combination (e.g., in Windows, Ctrl-r , and in Mac OS X , Option- Command -r ) than to a button. You can assign the key when you create the macro or after you've created it. Follow the appropriate directions below.

Note: The interface for Office 2007 programs is significantly different from that of older versions, with different terms and concepts; see

Assigning a shortcut to a new macro

In Excel 2003 and earlier, from the Tools menu, select Macro , and then Record New Macro... .

  • In the "Macro name:" field, enter a name for your macro.
  • In the "Shortcut key:" field, choose the key you would like to press in conjunction with the Ctrl key (in Mac OS X, Option-Command ) to trigger the macro. This can be any key, but you should check the key combination before assigning it to a macro. Excel has some predefined key combinations that you will erase if you assign one of your macros to the same key.
  • The save/store option allows you to choose where you would like to store the macro and key combination. If the macro is document-specific, from the drop-down menu, choose This Workbook . If you would like to save the macro and key in a new workbook, choose New Workbook . If you would like to save the macro to your permanent macro workbook, choose Personal Macro Workbook . This will keep the macro and key combination active no matter what you are working on.
  • Click OK , and then create your macro as usual.

Assigning a shortcut to an existing macro

In Excel 2003 and earlier, from the Tools menu, select Macro , and then Macros... .

  • A dialog box containing all macros in all open workbooks will appear. Click the macro for which you would like to create a key combination, and the name will appear in the field on top of the list.
  • Click Options... . A dialog box that contains information about the macro will open.
  • In the "Shortcut Key:" field, enter the key you wish to assign in conjunction with the Ctrl key (in Mac OS X, Option-Command ) to trigger the macro. This can be any key, but you should check the key combination before assigning it to a macro. Excel has some predefined key combinations that you will erase if you assign one of your macros to the same key.
  • Click OK as needed to close any open dialog boxes.

This is document ahbm in the Knowledge Base. Last modified on 2018-01-18 12:48:02 .

ManyCoders

How To Assign A Macro To A Keyboard Combination In Excel

February 4, 2024

Key Takeaway:

  • Creating and assigning a macro to a keyboard combination in Excel can save a lot of time and effort spent on repetitive tasks. This can be done by creating the macro in VBA and saving it in the Personal Macro Workbook for future use.
  • Assigning a macro to a specific keyboard combination involves opening the Excel Options window and selecting the Keyboard Shortcuts tab to create a new shortcut. It is important to choose simple and effective keyboard combinations that are easy to remember.
  • To ensure proper macro running, it is important to test the keyboard combination in a new workbook and check the functionality of the macro. Troubleshooting common macro issues such as verifying correct location, correct assignment and easy accessibility can help prevent errors in macros.

Struggling to remember complicated tasks in Excel? You can now create macros to streamline your workflow and save time. Assign a keyboard combination to your macro to access it quickly and efficiently with just a few clicks.

How to Create and Assign a Macro to a Keyboard Combination in Excel

Tired of the same tedious tasks in Excel? Macros can save you time and energy. Here’s how to create and assign a macro to a keyboard combination .

Start off with VBA (Visual Basic for Applications) .

Save the macro in your Personal Macro Workbook so you can access it whenever you need.

Experts say you can boost productivity by up to 10 times using macros to automate tasks! Get the most out of Excel by putting macros to work for you.

Creating the macro in Visual Basic for Applications (VBA)

To assign a macro to a keyboard combination in Excel, creating the macro in Visual Basic for Applications (VBA) is essential. To start, press Alt + F11 on your keyboard or select Macros from the Developer tab and click on Visual Basic.

When the editor is open, do these four steps:

  • Click Insert and pick Module from the drop-down menu.
  • In the empty module window, type Sub plus a name for the macro (no spaces). E.g. SubBoldText .
  • Write the code you want the macro to do below the name. For example: Range(“A1”).Font.Bold = True .
  • Save the changes before exiting.

Remember, there are many ways VBA macros can automate tasks in Excel, so it’s important to learn the capabilities of this tool.

Don’t use reserved keywords or punctuation marks as names for macros or variables, otherwise it could lead to errors.

Pro Tip: VBA macros can be copied, pasted between workbooks as text files or exported as .bas files. This is useful for transferring macros between Excel versions or sharing them with someone who can’t access the original workbook.

Then, save the macro in Personal Macro Workbook to assign a keyboard combination, which will be discussed soon.

Saving the macro in the Personal Macro Workbook for future use

Save your macros in a Personal Macro Workbook , and you’ll have access to them from any Excel workbook you open.

This means you don’t have to recreate useful macros each time you need them. The feature is useful for people who have regular workflow when working with Excel. It also helps create a library of customized macros.

For example, finance managers may use macros related to budgeting, taxation and data analytics . By saving these processes as macros in their personal workbook, they can easily load them from any Excel workbook.

It’s not clear where these macros are saved, so some people may be confused. My colleague was having trouble using his favorite shortcut macro in different sheets of an Excel document. He solved it by saving frequently used functions as individual macros in his personal workbook.

Finally, you can assign a keyboard combination to your new macro. This makes it easy to access them without going into the Macros list each time.

Steps to Assign a Macro to a Keyboard Combination

Ever wanted to perform Excel tasks with a single press? Assigning a macro to a keyboard shortcut is the answer! Here, we’ll explain how.

  • Firstly, open Excel Options window to access keyboard shortcuts.
  • Then, select the Keyboard Shortcuts tab and create a new one for the desired macro.
  • Last, create the shortcut using simple and effective combinations – it’ll save time in the long run!

Opening the Excel Options window to access keyboard shortcuts

To access the Excel Options window, click the File tab in the top-left corner . Then, select the Options dropdown. This opens the Excel Options window. On the left-hand side, select Customize Ribbon . On the right-hand side, click Customize to expand the list. Scroll down and select Keyboard Shortcuts . This screen shows all currently assigned macros and their keyboard shortcuts. Edit/create new shortcuts by clicking Customize at the bottom-left corner .

When I first started using Excel, I was doing a lot of repetitive tasks. But I found out how to assign macros to keyboard combinations, which saved me time and improved my workflow . We’ll guide you through assigning a Keyboard Shortcut tab to a macro.

Selecting the Keyboard Shortcuts tab to create a new shortcut for the desired macro

To create a shortcut for your desired macro, follow these steps:

  • Open your Microsoft Excel workbook and click File at the top left of the screen. Select ‘Options’ from this list to go to the Excel Options dialog box.
  • In the Excel Options dialog box, click ‘Customize Ribbon’ from the left side of the screen. You will find the ‘Keyboard Shortcuts’ tab there. Click on it to proceed.
  • Now, choose a category from the Categories column in the popup window. Under ‘Commands’ , select ‘Macros’ and select your desired macro name from here.
  • Click in the ‘Press new shortcut key’ text bar and press any keyboard keys combination you want to assign to that macro. Then, click ‘Assign’ below.

When creating a shortcut, remember to avoid using already assigned key combinations such as Ctrl+C or Ctrl+V , and also use one that’s convenient on all types of keyboards. This can save time and reduce hand strain when working with large amounts of data.

You can also use two different keyboard combinations using CTRL + ALT keys as an alternative, so if one key isn’t working, the other may. To learn more about creating shortcuts, note that you can use CTRL, SHIFT, or R keys.

Creating a shortcut using simple and effective keyboard combinations

Open the ‘Developer’ tab in Excel by clicking ‘File’ > ‘Options’ > ‘Customize Ribbon’ .

Tick mark the ‘Developer’ box.

Click the ‘Developer’ tab, select ‘Visual Basic’ to open VBA editor.

Insert a Module and type ‘Sub’ followed by a name for your macro. E.g. ‘SubBoldText()’

Write the task to be performed for the shortcut e.g. ‘ActiveCell.Font.Bold = True’ .

Save the Macro with Ctrl+S or File > Save . Close both windows.

Assign keyboard combinations e.g. Ctrl+Alt+b or Ctrl+Shift+c .

Test out the Macro.

Note: This applies to customizing Keyboard Combinations in Office 365 or any other Microsoft office program .

Pro tip: Pay attention to capturing each procedure step accurately, when creating a macro for multiple operations.

Testing the Keyboard Combination and Ensuring Proper Macro Running

Assigning macros to keyboard combos in Excel is key. Test the combo to make sure it works. Here are some tips for testing your macro:

  • Open a new workbook to check the macro.
  • Activate the combo and inspect the macro for any errors.
  • That’s it!

Opening a new workbook to test the macro

Start by opening Microsoft Excel . Click the “File” option in the top left corner and select “New.” Choose “Blank Workbook” for your own safety.

Testing macros before assigning them to keyboard combinations is a must! By opening a new workbook, you can detect any minor mistakes and fix them before you use the macro in your project. Otherwise, you might face data loss and other unwanted consequences.

It’s best practice to open a new workbook each time you’re experimenting with macros or other functions. Don’t wait; open a new workbook now! That way, you can identify errors before they cause major issues.

Finally, use the keyboard combination to ensure the macro runs smoothly.

Activating the keyboard combination to confirm smooth running of the macro

Here is how to activate a keyboard combination in 4 easy steps :

  • Launch Excel .
  • Go to the Developer tab in the ribbon and click Macros .
  • In the Macro dialog box, select the macro assigned to a keyboard combination and click Run .
  • If everything runs smoothly, you have successfully activated the keyboard combination!

Testing your macros is essential before you can save time and boost efficiency. Be conscious of any potential problems, glitches or errors when activating your keyboard combination. If there are any messages or errors that appear while trying to run the macros, take note of them. This will help to ensure that your macros are functioning properly and can prevent any issues from arising. Now is the time to make use of macros in Excel. Test them out today!

Checking the macro’s functionality and any potential errors

Go to the “Developer” tab on Excel and click on “Macros” . Select the macro you’d like to test, then press the “Run” button. See if it runs smoothly and does all assigned tasks without any interruptions.

Verify if your macro works as intended. Analyze the output to make sure all cells, formulas, and calculations are correct .

Check for mistakes by examining the code line-by-line. Look for wrong syntax, missing variables, or faulty logic in your programming. If issues are found, debug them.

Test various custom scenarios to determine if every possible situation is accounted for .

It’s essential to check for errors before executing your Macro. Unresolved issues can cause malfunctions in other spreadsheets. This mismanagement can lead to extra time spent, reduced productivity, and potential losses.

I once worked on an accounting macro project for a client’s firm. I accidentally missed indenting three lines of code, which caused dysfunctional behavior and a huge loss.

Finally, troubleshoot common Macro Issues that you might face later.

Troubleshooting Common Macro Issues

As an Excel user, I rely heavily on macros for automation and productivity. But what if they don’t seem to be working? Let’s figure out the common macro issues and how to troubleshoot them.

First, save the macro in the correct place – the Personal Macro Workbook. Assign a keyboard shortcut, too. Double-check that the macro is enabled in the Excel Options window, so you can access it when needed. There we go!

Verifying the macro is saved in the correct location, i.e., the Personal Macro Workbook

Open Excel and click on the Developer tab. Select Personal Macro Workbook from the View button. If it is not listed, make one by clicking on Record Macro and choosing “Store macro in” -> “Personal Macro Workbook”.

This workbook stores all personalized macros. This enables using them across multiple workbooks without copying and pasting.

If the macro is still not found, check if macros are enabled – File -> Options -> Trust Center -> Trust Center Settings -> Macro Settings . Debugging messages and errors may prevent it from running properly. Try restarting Excel or the computer.

Don’t forget to assign keyboard shortcuts for macros! Confirm that they are correctly assigned to the desired shortcut key combination.

Confirming the macro is correctly assigned to the desired shortcut

To make sure the macro is assigned to the desired shortcut, follow these four easy steps:

  • Open the Excel workbook with the macro.
  • Press Alt + F8 to open the Macros dialog box.
  • Select the macro and click ‘Options’.
  • Ensure a key combination not in use is selected.

Double-check it’s working. Return to the workbook and try the shortcut. If the macro runs, you’ve successfully assigned a macro! However, if nothing happens or there are issues, read on.

Issues may be due to incorrect references or syntax errors. Another cause could be conflicting shortcuts. Windows has special shortcut combinations, like Ctrl+Alt+Delete . If a combination is in use by another application, it could interfere.

If this is happening, try changing the hotkeys. If none of these tips help, contact Microsoft support for assistance.

Checking the macro is enabled in the Excel Options window for easy access

Need to access a macro in Excel ? Follow this simple guide!

  • Open Excel and click ‘File’ at the top left corner.
  • Then, click ‘Options’ at the bottom left of the menu.
  • In the left-hand column of the Excel Options window, select ‘Customize Ribbon’. This opens a new window with two sections: ‘Choose commands from:’ on the left and ‘Customize Ribbon’ on the right.
  • Under ‘Customize Ribbon’, expand ‘Macros’ and make sure your macro name is listed there . If not, close Excel and re-open the workbook file.
  • Look for an option to ‘Add New Tab’ or ‘Add New Group’ in the right side of the window. Click the appropriate ‘New’ button and give it a relevant name.
  • Select this new tab or group with the ‘Up’ or ‘Down’ arrow buttons.
  • Then, select “Macros” from ‘Choose commands from:’. On right side, checkmark the new Tab/Group along with the Macro name option below it .
  • Hit “Ok” button at the bottom-right corner of Customize Ribbon window.

Remember, disabling macros can help protect against malware. So, ensure any macros used are from trusted sources before enabling them in Excel Options.

Five Facts About How to Assign a Macro to a Keyboard Combination in Excel:

  • ✅ Macros in Excel are used to automate repetitive tasks. (Source: Microsoft Office Support)
  • ✅ Assigning a macro to a keyboard combination can save time and increase efficiency. (Source: Excel Easy)
  • ✅ The process of assigning a macro to a keyboard combination involves going to the Developer tab and selecting “Macros” and “Options”. (Source: Tech Community-Microsoft)
  • ✅ The keyboard combination can be a single key or a combination of keys, such as “Ctrl+Shift+A”. (Source: Excel Off The Grid)
  • ✅ It is important to choose a keyboard combination that is not already assigned to another function in Excel. (Source: Excel Campus)

FAQs about How To Assign A Macro To A Keyboard Combination In Excel

How to assign a macro to a keyboard combination in excel.

Assigning a Macro to a Keyboard Combination in Excel involves a few simple steps:

  • Press Alt + F11 to open the Visual Basic Editor.
  • Click on the ‘Insert’ option and select ‘Module’ to create a new module.
  • Type the Macro code that you want to assign to a keyboard shortcut.
  • Switch back to Excel and press Alt + F8 to open the Macro dialog box.
  • Select the Macro that you want to assign to a keyboard shortcut and click on ‘Options’.
  • Assign a unique keyboard shortcut key by typing it in the ‘Shortcut key’ box and click ‘OK’.

Can I assign a Keyboard Combination to an existing Macro in Excel?

Yes, you can assign a Keyboard Combination to an existing Macro in Excel:

  • Press Alt + F8 to open the Macro dialog box.
  • Select the Macro that you want to assign a Keyboard Combination to and click on ‘Options’.
  • Assign a unique Keyboard Combination by typing it in the ‘Shortcut key’ box and click ‘OK’.

What are the limitations of assigning a Keyboard Combination to a Macro in Excel?

The limitations of assigning a Keyboard Combination to a Macro in Excel are:

  • The Keyboard Combination has to be unique and cannot conflict with an existing Excel shortcut.
  • Keyboard Combinations assigned to Macros may not work if a user switches to a non-English version of Excel.
  • Assigning too many Keyboard Combinations to Macros can make it difficult to remember which Combination corresponds to which Macro.

Can I remove a Keyboard Combination assigned to a Macro in Excel?

Yes, you can remove a Keyboard Combination assigned to a Macro in Excel:

  • Select the Macro with the Keyboard Combination that you want to remove and click on ‘Options’.
  • Delete the content of the ‘Shortcut key’ box and click ‘OK’.

What happens if I assign a Keyboard Combination to a non-existent Macro in Excel?

If you assign a Keyboard Combination to a non-existent Macro in Excel, you will receive an error message. The Keyboard Combination will not perform any action.

Can I assign the same Keyboard Combination to multiple Macros in Excel?

No, you cannot assign the same Keyboard Combination to multiple Macros in Excel. Each Macro has to have a unique Keyboard Combination assigned to it.

404 Not found

Power Spreadsheets

Excel and VBA tutorials and training. Learn how to use Microsoft Excel and Visual Basic for Applications now.

Excel Keyboard Shortcuts: How To Quickly Get Or Create Any Keyboard Shortcut You Need

Keyboard shortcuts are, perhaps, one of the Excel topics that virtually any user can (or should) be interested in.

This makes sense. The following are some of the reasons that explain this:

  • Keyboard shortcuts are extremely useful for purposes of improving your speed and productivity when working with Excel. Not knowing the right keyboard shortcut means that, instead of quickly typing the relevant key combination, you need to (i) lift your hand from the keyboard, (ii) reach for the mouse in order to do what you want, and (iii) return to the keyboard.
  • In the most recent versions of Excel, keyboard shortcuts allow you to work with virtually every command or feature by using the keyboard.
  • Regardless of your level of Excel knowledge, you can always learn a new shortcut and, considering the huge amount of Excel keyboard shortcuts that you have available, is likely that you'll never memorize them all.

Excel Tutorial on how to get or create keyboard shortcuts

There are 3 basic reasons why you should consider learning more than lists when studying keyboard shortcuts :

  • Reason #1: Regardless of how comprehensive a keyboard shortcut list is, it's extremely difficult to map absolutely all of the keyboard shortcuts available in Excel. You may find that, from time to time, even massive keyboard shortcut resources (like my cheat sheet) don't list the keyboard shortcut for the precise item you need. Knowing how to find or create a keyboard shortcut by yourself can be quite valuable in these situations.
  • Reason #2: Keyboard shortcut lists and cheat sheets only include the built-in default keyboard shortcuts of Excel. This leaves out the options you have for creating custom keyboard shortcuts in Excel by using the Quick Access Toolbar or macros.
  • Reason #3: Most of us don't want or need to learn absolutely all the keyboard shortcuts that exist in Excel. Even if we tried to learn them all, is likely that only a few of us would succeed.

This is where this Excel tutorial comes in. My purpose with this blog post is to provide you with the knowledge you need to master keyboard shortcuts in Excel and become even more productive . Therefore, in addition to explaining the different types of keyboard shortcuts that are available in Excel, I show you some methods you can use to create your own custom keyboard shortcuts. The table of contents below shows the specific topics this blog post covers:

Table of Contents

Let's start with a preliminary introduction about how I suggest you approach keyboard shortcuts in Excel:

How To Approach Keyboard Shortcuts In Excel

Lists and cheat sheets with keyboard shortcuts are great. Most of us want to have them and, as I show below, lots of Excel bloggers and writers (including myself) have created their own version of them.

These amazing lists can, however, be slightly overwhelming if you don't know how to approach them.

More precisely, if you approach keyboard shortcut cheat sheets with the idea that you'll quickly learn them all by heart, you're likely to be disappointed.

This isn't a bad thing. After all, you probably don't need to know so many keyboard shortcuts . Furthermore, some of those shortcuts may be for commands or features that you rarely use.

And if it makes you feel better, you're in good company. You can rest assure that most other Excel users (including very advanced ones), don't know all the shortcuts either.

Therefore, before you begin to collect lists of keyboard shortcuts or trying to learn every single shortcut you come along, I have 1 basic suggestion :

Don't overload yourself by trying to learn too many keyboard shortcuts at the same time. You're unlikely to learn a huge amount of keyboard shortcuts in a very short period of time.

I suggest you start slowly and, perhaps, learn 1 or 2 shortcuts per week. Once you've mastered a particular shortcut, learn a new one. As you start gaining more familiarity with Excel keyboard shortcuts, you'll be able to handle more.

You'll notice the list of keyboard shortcuts you know and use grows consistently over time.

You may wonder, then, how should you choose which keyboard shortcuts to learn first. My suggestion is that you take the following 2-step approach to determine what keyboard shortcuts to master :

  • Step #1: Identify commands or tasks that you constantly execute while working with Excel. Due to the broad variety of activities people carry out with Excel, this varies from person to person. Your list of most commonly used commands may not look at all like mine. And that's fine: Just determine what works for you.
  • Step #2: Search and find a keyboard shortcut that allows you to execute that particular command or task.

Once you've determined the command or task you want to execute with your keyboard, you'll have 2 basic options for purposes of searching for the corresponding keyboard shortcut :

  • Option #1: Check out a list of keyboard shortcuts prepared by somebody else. I share several of such lists below.
  • Option #2: Find a built-in keyboard shortcut or, if there's no suitable option, create your own keyboard shortcuts.

This option #2 is the topic of this blog post.

I have already created (and update from time to time) a very comprehensive shortcut list that helps you with option #1. However, depending on factors such as the type of command or tasks you need to execute and your own personal preferences, the keyboard shortcuts included in such lists may not (for different reasons) work for you.

Fortunately, Excel provides several options for you to carry out a task or execute a command by using only the keyboard . In addition to the huge amount of built-in keyboard shortcuts that Excel has, you can create your own custom keyboard shortcuts (taking into consideration some requirements and limitations that I explain below).

The following section may help you avoid confusion with the, sometimes, irregular terminology used by different authors when talking about keyboard shortcuts. If you aren't interested in this particular topic, or already have a good grasp of the topic, please feel free to skip to the next section, which talks about regular keyboard shortcuts.

Excel Keyboard Shortcuts, Keyboard Accelerators, Key Tips And Hotkeys: Terminology

When reading about keyboard shortcuts for Excel, you may find that some authors use the terms “keyboard accelerators”, “Hotkeys” or “Key Tips”. You may find some discussions regarding the exact meaning of each term. Without going into more theoretical discussions regarding the technical meaning of each term, let's take a look at how I use them in this Excel tutorial :

  • Keyboard Shortcuts: I generally use the term “keyboard shortcuts” to refer to the combination of keyboard keys that allows you to carry out a particular task that usually requires the use of a mouse. In some contexts, you may see the term “keyboard accelerators” being used to refer to all of such combinations, while the term “keyboard shortcuts” is reserved to the type of keyboard shortcuts that I refer to as regular keyboard shortcuts.

Screenshot of Excel Ribbon with shortcuts

The terms listed above are, however, not necessarily used in exactly the same way by other Excel writers or documents . A possible variation of the terminology, for example, involves using the terms “Hotkeys” and “Key Tips” interchangeably.

Sometimes, authors use the term “keyboard accelerator” to refer to the keyboard shortcuts that you use when interacting with the Ribbon, the Quick Access Toolbar or dialog boxes. These are what I refer to here as Ribbon keyboard shortcuts, Quick Access Toolbar keyboard shortcuts and dialog box keyboard shortcuts. Since Key Tips and Hotkeys (as explained above) appear when you use keyboard shortcuts to work with the Ribbon, the Quick Access Toolbar and dialog boxes, you may see the term “keyboard accelerators” instead of Key Tips and Hotkeys.

You don't have to worry too much about these differences :

Once you've read this Excel tutorial, you'll have enough knowledge to understand other texts that talk about keyboard shortcuts. Therefore, you'll easily figure out how the relevant author is using each term.

Now that the terminology is clear, let's start taking a look at the different types of keyboard shortcuts that you can use in Excel. Let's begin with:

Regular Keyboard Shortcuts For Excel

When talking about keyboard shortcuts for Excel, most people refer to Ctrl combination shortcut keys.

As implied by their denomination, Ctrl combination shortcut keys involve pressing the Ctrl key in combination with some other(s) key(s) . In most cases, these keyboard shortcuts are of the following form :

Ctrl + Letter

Some of the most well-known and commonly used keyboard shortcuts fall within this group. Some examples are the following:

  • “Ctrl + B” to apply or remove bold formatting.
  • “Ctrl + C” to copy.
  • “Ctrl + V” to paste.

You can, however, find Ctrl combination shortcut keys that combine Ctrl with other keys. The following are some examples:

  • Ctrl + Additional Key. For example, you can use “Ctrl + 1” to display the Format Cells dialog box, “Ctrl + 5” to apply or remove strikethrough formatting or “Ctrl + [” to go to the direct precedents within the active worksheet.
  • Ctrl + Shift + Additional Key. Some examples of such keyboard shortcuts are “Ctrl + Shift + !” (also expressed as “Ctrl + Shift + 1”) to apply number format or “Ctrl + Shift + A” to insert function arguments (when the function name has already been typed and the insertion point is to the right of the function name).
  • Ctrl + Alt + Additional Key. Perhaps my favorite keyboard shortcut following this form is “Ctrl + Alt + V”, which displays the Paste Special dialog box.

There are, however, additional keyboard shortcuts that aren't Ctrl combination shortcut keys .

I can't list all the regular keyboard shortcuts that Excel has here. However, for illustrative purposes, the following are some examples of keyboard shortcuts that aren't Ctrl combination shortcut keys :

  • “Shift + Arrow Keys” extends the selection by 1 cell or, within a cell, selects the character to the right or to the left of the insertion point.
  • “Shift + F10” is the equivalent of right-mouse button click. In other words, it displays the context menu for the selected item.
  • Tab, among other functions, selects a suggestion provided by the autocomplete feature to complete a formula and move one cell to the right or to next unlocked cell.
  • “Alt + Page Down” and “Alt + Page Up” take you 1 screen to the right or 1 screen to the left of the current active sheet.
  • F7 displays the Spelling dialog box.
  • When a row or column is selected, “Right-Mouse Button + E” inserts copied or cut cells.
  • Within a dialog box, the Spacebar performs the action that corresponds to the selected button, or selects or clears a checkbox.

As you can see, these are plenty of different regular keyboard shortcut combinations.

All of the keyboard shortcut examples I provide above are (i) for Excel for Windows and (ii) using the English (USA) keyboard layout. If you're using Excel on a computer that doesn't meet these 2 requirements, there will (likely) be some differences in some keyboard shortcuts. For example, if you're using Excel for Mac, some of the Ctrl combination shortcut keys that Excel for Windows has don't work.

If you're interested in lists or cheat sheets with the actual keyboard shortcuts , you may be interested in Excel Shortcuts and Hotkeys: The Power Spreadsheets Cheat Sheet . This is my compilation of keyboard shortcuts, which I update from time to time. In addition to the actual blog post, you can gain immediate free access to the PDF file containing that list.

Ribbon Keyboard Shortcuts for Excel: Key Tips

Excel's Ribbon is the group of tabs and buttons that you see in the upper section of Excel.

Excel Ribbon

If you've used Excel before, you probably know that you can use the Ribbon to carry out the most common tasks in Excel.

In fact, my guess is that you use the Ribbon constantly. Also, if you're like most Excel users, you probably use the mouse to work with the Ribbon (at least most of the time).

However, thanks to Key Tips, you can get to any tab or button within the Ribbon using only the keyboard . As explained by John Walkenbach in the Excel 2016 Bible :

At first glance, you may think that the Ribbon is completely mouse centric (…). But in fact, the Ribbon is very keyboard friendly.

In this section, I explain what you need to know in order to work with the Ribbon using these Ribbon keyboard shortcuts .

When using Ribbon keyboard shortcuts, you don't need to hold down all the keys at the same time, as you do with regular keyboard shortcuts . In other words, with Ribbon keyboard shortcuts, you can:

  • Press the first key and let it go.
  • Press the second key and let it go.
  • And continue pressing and releasing the relevant keys that compose the keyboard shortcut.

Now that you know this, let's take a look at the 3 easy steps you can follow to use Ribbon keyboard shortcuts . After explaining these 3 steps, I show you an example of how you can use Ribbon keyboard shortcuts.

Step #1: Turn Key Tips On

To turn on Key Tips, press either of the following keys:

Remember that you don't need to hold down these keys while carrying out the next steps below.

Step #2: Press The Key(s) That Corresponds To The Ribbon Section You Want To Go To

Once you've turned Key Tips on, Excel displays the keys that corresponds to each Ribbon tab, the Backstage View (whose Ribbon label is “File”) and Tell Me (whose Ribbon label is “Tell me what you want to do”).

Tell Me was introduced in Excel 2016. Therefore, if you're using Excel 2013 or earlier, you won't have access to this feature .

The following image displays an Excel Ribbon with the Key Tips turned on:

Ribbon of Excel with keyboard shortcuts

The following table summarizes these Key Tips:

The Ribbon of your particular version of Excel likely looks different from that of the image above. For example:

  • You may not see some of the tabs that are displayed above. This may be the case, for example, if you don't have the Developer tab enabled .

Contextual Ribbon tabs with shortcuts

  • You may not see the Tell Me feature. This is the case if you're using Excel 2013 or earlier. The screenshot above was taken using Excel 2016.

However, the Key Tips for the Ribbon tabs remain the same . In other words, “Alt + H” always takes you to the Home tab, “Alt + N” always takes you to the Insert tab, and so on.

Regardless of how your Ribbon looks like, Excel always displays the Key Tips corresponding to all the sections (Ribbon tabs, Backstage View and Tell Me) you can access at that particular time. Just remember the rule you must follow once you've turned on the Key Tips:

Press the key(s) that corresponds to the section (usually a tab) of the Ribbon you want to activate .

In fact, as I shown in step #3 below, when you're working with the Ribbon, you just need to continue applying this rule:

Step #3: Continue Pressing The Key(s) That Corresponds To What You Want To Activate

Once you've turned the Key Tips on (as explained in step #1 above), Excel continues to display the Key Tips that correspond to the particular situation you're in. In other words:

At any given time, the Key Tips displayed by Excel show all of the options you have for your next step . You just need to determine what key you must press to go where you want to go, and press it . Depending on the particular context, Excel does one of the following:

  • Executes the command you have chosen, opens the relevant dialog box, or similar; or
  • Displays a new set of Key Tips that show all of your new options.

In some cases, a Key Tip may display 2 keys. In such situations, just press both keys . In the case of the Design and Format tabs above, this means pressing “J + C” or “J + A”.

If Excel displays a new set of Key Tips, just repeat this step #3 (press the appropriate keyboard key) as many times as necessary until you've reached your desired destination. Remember that you don't need to keep any key depressed while typing the other keys that compose the Ribbon keyboard shortcut.

Let's see how this looks in practice:

Let's assume that, on step #2 above, you press the letter H. This keyboard key takes you to the Home tab. In my version of Excel 2016, this looks as follows:

Keyboard shortcuts for Ribbon Home tab

Notice how the Key Tips displayed by Excel no longer correspond to the Ribbon tabs, as shown in step #2 above. The (many) Key Tips in the image above correspond to the different buttons, drop-downs and dialog launchers in the Home tab.

If, for example, you want to execute the Increase Indent command, to move the content further away from the border of a cell, you can now press the number 6. This key tip is shown in the image below:

Key Tip for Increase Indent command

In other words, if you want to execute the Increase Indent command using Ribbon keyboard shortcuts (or Key Tips), the full shortcut is “Alt + H + 6”.

To make the whole process of using Ribbon keyboard shortcuts (Key Tips) very clear, let's take a look at an additional example:

How To Use Ribbon Keyboard Shortcuts For Excel: An Example

The image shows the whole process of using keyboard shortcuts to access a command in the Ribbon.

In this case, I'm adding all borders to cell D2, which I select at the beginning of the recording. The full Ribbon keyboard shortcut for adding all borders is “Alt + H + B + A”.

Notice that, in this case, I need to press 4 keys (Alt, H, B and A) in order to achieve the desired result. In the example within the previous section, I only needed to press 3 keys (Alt, H and 6).

Therefore, the number of keys you need to press may vary, but the general steps to use Ribbon keyboard shortcuts remain the same :

  • Rule #1: Turn on Key Tips by pressing Alt or F10.
  • Rule #2: Use Key Tips to determine the key(s) that corresponds to the section of the Ribbon (for example, tab or button) that you want to go to, and press that key (or key combination).

These rules allow you to get to pretty much any command within the Ribbon by using the keyboard only. In fact, they also apply to any tabs or groups of commands that you add when customizing the Ribbon . In such cases, as explained in Excel 2016 All-in-One for Dummies :

(…) Excel automatically assigns hot-key letters to each of the custom tabs and commands you add to the Ribbon.

Now that you understand Ribbon keyboard shortcuts, let's take a look at a group of keyboard shortcuts that perform a similar function in the older versions of Excel which use menus instead of the Ribbon:

Old Alt + Menu Keyboard Shortcuts

Microsoft introduced the Ribbon in Excel 2007. Previous versions used a menu-based navigation, similar to the Visual Basic Editor .

These older Excel versions also have their own system of shortcuts. Several of these keyboard shortcuts continue to work .

However, if you choose to work with these keyboard shortcuts, you must know them by heart. Excel displays no Key Tips .

In fact, whenever you start typing one of these keyboard shortcuts, Excel enters into Office 2003 Access Key mode and displays a message stating that you're using a menu key sequence from a previous version of Excel. This message also displays the letter(s) of the keyboard shortcut that you've already typed. The following screenshot shows this looks like after I've typed “Alt + T”, which corresponds to the old Alt + Menu keyboard shortcut for the Tools menu.

Excel message when using old Alt + keyboard shortcuts

In my opinion, if you're currently learning keyboard shortcuts, it's a better idea to use one of the other keyboard shortcut types I explain this Excel tutorial . The old Alt + Menu keyboard shortcuts are better suited , as explained in Excel 2016 In Depth , for “power users” who “have a few of these commands memorized” .

If you've worked with previous versions of Excel and already know some of these old keyboard shortcuts, or are interested in rediscovering them, you can refer to Mr. Excel's (Bill Jelen's) Excel In Depth books. These books usually include a comprehensive list of these old Alt + menu shortcuts that continue to work in newer versions of Excel.

Not all of the old Alt + Menu keyboard shortcuts that worked in the older versions of Excel continue to work. As explained by Mr. Excel, who lists which of these commands continue to work in the Excel In Depth books that I quote above:

Several commands don't make sense in the framework of Excel 2016, so they have been deprecated.

However, you shouldn't be too worried about typing an old Alt + menu keyboard shortcut that no longer works. If you try to do this, and the command indeed doesn't work in the newer version of Excel, Excel generally doesn't do anything. In other words, nothing happens.

I actually continue to use some of these old Alt + menu keyboard shortcuts myself.

For example, I normally use the keyboard shortcut “Alt + T + O” to display the Excel Options dialog box. The following image shows how Excel looks like while I use this keyboard shortcut. Notice, in particular, the message displayed by Excel at the top of the screen and the lack of Key Tips.

Quick Access Toolbar Keyboard Shortcuts And Creating Custom Keyboard Shortcuts

The Quick Access Toolbar is the toolbar that, by default, appears on the upper left corner of Excel (just above the Ribbon).

Quick Access Toolbar in Excel

By default, the Quick Access Toolbar includes very few commands. Typically, the commands included in the Quick Access Toolbar are those that appear in the screenshot above:

If you're working on a touch-enabled device, the button for the Optimize Spacing Between Commands command may also appear.

The Quick Access Toolbar may not seem very exciting if you're looking to improve your productivity by using keyboard shortcuts. However, the Quick Access Toolbar has 2 characteristics that make it very interesting for Excel users who want to use more keyboard shortcuts :

  • Characteristic #1: The Quick Access Toolbar is customizable. As a consequence of this characteristic, you can add or remove commands from it.
  • Characteristic #2: You can easily access the commands in the Quick Access Toolbar using keyboard shortcuts. The reason for this is that Key Tips (which I introduce above when talking about Ribbon keyboard shortcuts) also help you to access any command within the Quick Access Toolbar.

One of the main consequences of these 2 characteristic is that you can use the Quick Access Toolbar to create custom keyboard shortcuts for almost any Excel command .

Before looking at the process you can follow to create such custom keyboard shortcuts, let's take a closer look at how Key Tips work with the Quick Access Toolbar:

Quick Access Toolbar Keyboard Shortcuts: Key Tips

As I explain above, you can use Key Tips to access any command in the Ribbon. The same applies to the Quick Access Toolbar.

The general steps to use Quick Access Toolbar shortcuts are, basically, the same as those that I explain above for using Ribbon keyboard shortcuts. More precisely, in order to access a command within the Quick Access Toolbar by using your keyboard , you simple need to follow these 2 easy steps:

  • Step #1: Press Alt or F10 to turn on Key Tips.
  • Step #2: Use the Key Tips displayed by Excel to determine the key that corresponds to the item (usually a command) of the Quick Access Toolbar that you want to select, and press it.

Up until here, Quick Access Toolbar keyboard shortcuts work pretty much the same as Ribbon keyboard shortcuts. However, there is a key difference:

The Quick Access Toolbar is independent from the Ribbon tab currently being displayed. Therefore, the Quick Access Toolbar is (almost) always visible , regardless of which Ribbon tab is currently displayed.

This fact has an important consequence for keyboard shortcuts:

Quick Access Toolbar keyboard shortcuts are shorter than Ribbon keyboard shortcuts .

As explained above, most Ribbon keyboard shortcuts are at least 3 keys long, with the following basic form:

  • Key #1: Alt or F10.
  • Key #2: The key corresponding to the relevant Ribbon tab.
  • Key #3: The key for the relevant command.

In several cases, the shortcuts are longer and include more than 3 keys. For example, applying cell borders (as in the example I show above), requires you to press 4 keys (Alt, H, B and A).

Quick Access Toolbar shortcuts can be as short as 2 keys , although they can also be longer. The exact length of a Quick Access Toolbar shortcut varies, as explained below, depending on the following 2 factors:

  • Factor #1: The amount of buttons your Quick Access Toolbar has.
  • Factor #2: The location (within the Quick Access Toolbar) of the relevant command.

In order to understand how Quick Access Toolbar shortcuts work in practice, let's take a look at what happens when I turn on key tips by pressing Alt or F10:

Keyboard shortcuts for Quick Access Toolbar

Notice how Quick Access Toolbar keyboard shortcuts have the following structure , which is simpler than that of Ribbon keyboard shortcuts:

Alt + Number

The Quick Access Toolbar in the screenshot displayed above only has 3 commands. If your Quick Access Toolbar has more than 9 commands, the keyboard shortcuts for the commands after the ninth have the following form:

Alt + Number + Number/Letter

In any case, the organization of Quick Access Toolbar keyboard shortcuts is quite intuitive. More precisely, the number and letter (where applicable) of a keyboard shortcut is determined by the position of the relevant button in the Quick Access Toolbar .

The exact number combination that is assigned by Excel to the first 45 buttons in the Quick Access Toolbar is as follows:

  • The first 9 commands of the Quick Access Toolbar are numbered between 1 and 9. Notice how, in the screenshot displayed above, the 3 commands (Save, Undo and Redo) are numbered 1, 2 and 3. The complete keyboard shortcuts for these buttons are “Alt + 1” through “Alt + 9” .
  • Commands 10 to 18 of the Quick Access Toolbar are assigned a 2-digit number, starting with 09 and going down to 01. In other words, these keyboard shortcuts are “Alt + 0 + 9” through “Alt + 0 + 1” .
  • Commands 19 to 45 have keyboard shortcuts where the second key is always a 0 and the third key is a letter ranging from A to Z. In these cases, keyboard shortcuts range from “Alt + 0 + A” to “Alt + 0 + Z” .

If you want to get an idea of how a Quick Access Toolbar with many commands looks like when the Key Tips are turned on, the following screenshot shows this situation:

Keyboard shortcuts for big Quick Access Toolbar

Notice how the keyboard shortcuts follow the general rules described above. The keyboard shortcut for the first command on the left is “Alt + 1”. The keyboard shortcut for the last command on the right is “Alt + 0 + Q”.

Now that you understand how Quick Access Toolbar keyboard shortcuts work, and their advantages, let's take a look at how you can create your own custom keyboard shortcuts by using the Quick Access Toolbar:

How To Create Custom Keyboard Shortcuts For Excel Using The Quick Access Toolbar

From a broad perspective, you only need to do 1 thing to create a custom keyboard shortcut using the Quick Access Toolbar :

Add the relevant command to the Quick Access Toolbar in the position that matches the keyboard shortcut you want that command to have . Excel assigns the keyboard shortcut following the rules explained above.

There are several different methods you can use to add commands to the Quick Access Toolbar. Since this blog post isn't about the Quick Access Toolbar itself, I only explain 1 of these methods below.

I may explain the other methods you can use to add commands to the Quick Access Toolbar in future Excel tutorials. If you want to receive an email whenever I publish new content in Power Spreadsheets, please make sure to subscribe to our Newsletter by entering your email address below:

To add a command to the Quick Access Toolbar, follow these 5 simple steps :

Step #1: Access The Quick Access Toolbar Tab Of The Excel Options Dialog

You can access the Quick Access Toolbar tab of the Excel Options dialog in several ways. I explain the most common below:

  • Method #4: Use either of the following keyboard shortcuts: Keyboard Shortcut #1: Alt + T + O + Q. Keyboard Shortcut #2: Alt + F + T + Q.

Step #2: Choose The Group Of Commands You Want To Work With

You can choose from more than 1,000 commands to add to the Quick Access Toolbar.

That's quite an overwhelming amount. Fortunately, Excel allows you to choose from several different subsets of commands to browse.

To do this, follow these 2 steps:

Choose commands from in Excel Options dialog

Step #3: Add A Command To The Quick Access Toolbar

Once you've chosen the subset of commands you want to work with, the actual commands you can choose to add to the Quick Access Toolbar are displayed in the Choose commands from list box. This appears on the left side of the pane, just below the Choose commands from drop-down list.

Choose commands from list box in Excel Options dialog

You can add a commands to the Quick Access Toolbar in the following 2 steps:

Add Center command to Quick Access Toolbar

Step #4: Organize The Commands In The Order You Desire

Once you've added a command to the Quick Access Toolbar, the command (usually) appears at the bottom of the Customize Quick Access Toolbar list box on the right side of the Excel Options dialog.

Location of command added to Quick Access Toolbar

As I explain above, the exact keyboard shortcut assigned to a particular command depends on its location in the Quick Access Toolbar. Therefore, you can determine (within a certain limited scope) what is the keyboard shortcut for the commands you add to the Quick Access Toolbar by placing them in the appropriate position .

To change the position of commands within the Quick Access Toolbar, use the Move Up or Move Down arrows that appear on the right side of the Excel Options dialog. Click on the appropriate arrow as many times as required in order to place the command in the position you desire.

Organize commands in Quick Access Toolbar

Step #5: Press The OK Button

Once you're done with the 4 steps above, simply click on the OK button on the lower right corner of the Excel Options dialog box to complete the process.

Confirm customization of Quick Access Toolbar

The resulting Quick Access Toolbar displays any new commands that you've added by following the process described above.

Excel Quick Access Toolbar with additional commands

If you've completed the 5 steps above, you've created a custom Excel keyboard shortcut. Let's take a look at a practical example.

How To Create A Custom Keyboard Shortcut In Excel: An Example

The following image shows the full process of creating a keyboard shortcut for the Paste Values command using the 5-step process described above.

In this particular case, I place the command in the first position of the Quick Access Toolbar. Therefore, the keyboard shortcut assigned to Paste Values is “Alt + 1”.

The following image shows how the Quick Access Toolbar looks like after the process above has been completed. Notice how the newly added command appears at the beginning of the Quick Access Toolbar and is paired with the number 1.

Screenshot of customized Quick Access Toolbar

Dialog Box Keyboard Shortcuts For Excel: Hotkeys

The information within the previous sections in this Excel tutorial allows you to execute a huge amount of commands. However, you'll soon notice that, in several cases, a particular keyboard shortcut leads you to a dialog box .

This section explains how you can use keyboard shortcuts to interact with most dialog boxes within Excel.

As a general matter, you can use Hotkeys to interact with dialog boxes within Excel . I introduce Hotkeys at the beginning of this Excel tutorial.

You can generally recognize Hotkeys within a particular dialog box because Excel underlines the corresponding letter .

Working with Hotkeys is quite straightforward and, to a certain extent, is similar to working with the Key Tips that help you with Ribbon and Quick Access Toolbar keyboard shortcuts.

Let's start by taking a look at a dialog box and its Hotkeys. The following screenshot shows the Format Cells dialog box, with the Alignment tab selected. The arrows point to some of the underlined letters within this dialog box.

Example of Excel Hotkeys in dialog box

When you're working with a dialog box and face such a situation, you just need to remember the following:

To select a command using keyboard shortcuts, press the relevant Hotkey. In some cases, you have to press the relevant Hotkey plus the Alt key . In other words, the keyboard shortcut may be of the form “Alt + Hotkey”.

Some of the cases in which you won't need to press the Alt key at the same time as the Hotkey are the following :

  • If (i) the dialog box you're working with doesn't have tabs and (ii) the current selected option isn't a drop-down list or a spinner control. The Paste Special dialog box that I show at the beginning of this blog post doesn't have tabs, drop-down lists or spinner controls.
  • If the dialog box you're working with has tabs, but (i) the current selected option is within the tab in which the relevant Hotkey appears and (ii) that current selected option isn't a box or a spinner control. In other words, it isn't enough that the displayed tab is the one you want to work with. The current selected option must be one of the commands within the tab.

Even though it isn't required in these situations, you can anyway press the Alt key at the same time as the relevant Hotkey . The result generally doesn't change and Excel usually takes you to the same option as if you hadn't press the Alt key.

You are, however, generally required to press the Alt key at the same time as the Hotkey if the conditions above aren't met . In other words, you generally press the Alt key when:

  • The current selected option within a dialog box is a list box or spinner control.
  • The dialog box has tabs and the current selected option isn't within the tab in which the relevant Hotkey appears.

Knowing which is the current selected option may be a little bit tricky in some cases. In most cases, particularly if the current selection option is within the displayed tab, Excel shows a dotted square around the current selected option .

For example, in the following screenshot, “Merge cells” is the current selected option:

Example of selected command in Excel dialog

In such a case, if you wanted to wrap the text, you just need to press W (the Hotkey for Wrap text).

Keyboard shortcut with Hotkeys example

Compare the images above with the one below, where there are no dotted lines to be found. In this case, even though the Alignment tab is displayed, the current selected option isn't within the Alignment tab. The selected option is, actually, the Alignment tab itself.

In this case, the Hotkeys continue to be the same. However, you must press the Alt key at the same time as the relevant Hotkey. In other words, to wrap the text, you press “Alt + W”.

Hotkey screenshot in Excel dialog box

You also need to press the Alt key at the same time as the relevant Hotkey (making the keyboard combination “Alt + W”) when the current selected option is any of the drop-down lists (Horizontal, Vertical and Text direction) or spinner controls (Indent and Degrees) within the Alignment tab of the Format Cells dialog box. The following image highlights some of these lists and spinners:

excel hotkeys list spinner

When working within dialog boxes, you generally have to confirm your choice by pressing the Enter key .

As I explain below, Enter is the equivalent of clicking on the default button of the dialog box. Usually, the default button of a dialog box is OK.

Dialog Box Keyboard Shortcuts: What Happens When There Are No Underlined Letters

Most dialog boxes within Excel have clearly marked Hotkeys for almost all of their commands.

However, you may eventually notice not all options within a Dialog Box have an underlined letter . In fact, several of the dialog boxes I show within this Excel tutorial have at least 1 such option, where no letter is underlined.

The lack of an underlined letter doesn't necessarily mean that you can't interact with such an option using your keyboard.

To see how this works, let's go back to the Excel Options dialog that I introduce above when explaining how to create custom keyboard shortcuts by customizing the Quick Access Toolbar:

In order to customize the Quick Access Toolbar using the method I describe in this Excel tutorial, you access the Quick Access Toolbar tab of the Excel Options dialog. I explain several ways of doing this, including the following 2 keyboard shortcuts:

  • Alt + T + O + Q.
  • Alt + F + T + Q.

The first 3 components of each of these keyboard shortcuts open the Excel Options dialog box. In other words, “Alt + T + O” and “Alt + F + T” make Excel display the Excel Options dialog.

Once you're within the Excel Options dialog box, the letter Q takes you to the Quick Access Toolbar tab. Notice, however, that the labels on the left-side pane of the Excel Options dialog box (including “Quick Access Toolbar”) don't have any underlined letters:

Options dialog with no Hotkeys

Despite this lack of guidance, you can reach most of the tabs within the Excel Options dialog by using the first letter of their respective label . In other words, just as the letter Q takes you to the Quick Access Toolbar table:

  • The letter G takes you to the General tab, making the full keyboard shortcut “Alt + T + O + G” or “Alt + F + T + G”.
  • The letter F displays the Formulas tab. Therefore, the full keyboard shortcut is “Alt + T + O + F” or “Alt + F + T + F”.
  • … and so on.

This same rules applies in other dialog boxes. Let's go back to the Alignment tab of the Format Cells dialog box that I use in the examples above. Notice that the tab labels don't have underlined letters:

Tabs of dialog box with no Hotkeys

In such cases, as long as the current selected option isn't within the displayed tab, you can go to a particular tab by pressing the first letter of the label. In other words:

  • N displays the Number tab. A full keyboard shortcut for going to the Number tab is, therefore, “(Ctrl + 1) + N”. As a side note, “Ctrl + 1” is a basic shortcut for opening the Format Cells dialog box.
  • F takes you to the Font tab. In this case, a full keyboard shortcut is “(Ctrl + 1) + F”.

This is already quite specific, so you may be surprised that this still doesn't cover absolutely all of the possible options. You may eventually encounter the following situation:

Dialog Box Keyboard Shortcuts: What Happens When There Are No Underlined Letters And 2 Options Begin With The Same Letter

The rule for using keyboard shortcuts within a dialog box when there are no underlined letters (as explained in the previous section) is relatively simple:

To choose an option, press the first letter of its label .

However, if 2 labels begin with the same letter, this rule doesn't work for all of those options. After all, the same key shouldn't lead to 2 different consequences.

To see how this can happen, let's go back (once more) to the left-side pane of the Excel Options dialog. Notice that 2 labels (Advanced and Add-ins) begin with the same letter (A):

Tab label example in Excel Options dialog

In this case, the letter A works as a keyboard shortcut for the first tab (Advanced). In other words, the keyboard shortcuts “Alt + T + O + A” or “Alt + F + T + A” take you to the Advanced tab of the Excel Options dialog.

To go to the Add-ins tab, you actually need to press the first key of the label twice . In other words, the following 2 are full keyboard shortcuts to go to the Add-ins tab of the Excel Options dialog:

  • Alt + T + O + A + A.
  • Alt + F + T + A + A.

Other Ways Of Navigating The Excel Interface Using The Keyboard

The previous sections of this Excel tutorial cover keyboard shortcuts that allow you to work with a huge range of commands, options and features.

There are, however, some cases where you may prefer (or need) to navigate through Excel's interface using other (more common) navigation keys, such as the arrow keys.

The following is a list of some general rules and examples of how you can navigate through Excel's interface using the keyboard .

Example of keyboard shortcut to expand Excel list

  • Arrow Keys: You can use the arrow keys for navigation purposes in several situations. For example: (i) within the Ribbon, arrow keys allow you to move to the next (right) or previous (left) option or tab or (ii) within a drop-down list (such as that displayed above for Font Face), the arrow keys help you move between the different items.
  • “Ctrl + Tab” and “Ctrl + Shift + Tab”: When working with a dialog box that has more than 1 tab, “Ctrl + Tab” takes you to the next tab. “Ctrl + Shift + Tab” takes you in the opposite direction, to the previous tab.
  • Enter: Within a dialog box, Enter generally performs the action that corresponds to the default command button of the respective dialog box. For example, in the Excel Options and Format Cells dialog boxes used as examples further above, Enter is the equivalent of pressing the OK button.

Excel Ribbon Home tab shortcuts

  • Home and End: When a menu or sub-menu is visible, you can use the Home or End key to select the first or last command within the menu or sub-menu.
  • Spacebar: When working with a dialog box, you can use the Spacebar to perform the action that corresponds to the selected button, or select/clear a particular checkbox. When working with the Ribbon, the Spacebar activates the current selected command or, if the current selection is a menu or gallery, expands the selected menu or gallery.
  • Tab and “Shift + Tab”: Tab has a similar effect regardless of whether you're working with the Ribbon or a dialog box. In both cases, it allows you to move the next option or option group. “Shift + Tab” allow you to move in the opposite direction, to the previous option or option group.

Macro Keyboard Shortcuts For Excel

Macros are sequences of instructions that you want Excel to follow in order to achieve a purpose. Macros allow you to automate a particular aspect of Excel.

I cover the topic of macros in several posts throughout Power Spreadsheets.

In this particular tutorial, I explain how you can execute a macro by assigning a keyboard shortcut to a macro that already exists . If you're using the macro recorder, you can also assign a keyboard shortcut to a macro upon creation .

Let's take a look at the 3 easy steps you can follow to assign or edit the keyboard shortcut of any macro:

Step #1: Open The Macro Dialog Box

You can open the macro dialog box using either of the following 2 methods:

Open Macro dialog box within Developer tab

  • Method #2: Use the “Alt + F8” keyboard shortcut.

Step #2: Select The Macro You Want To Edit

The Macro dialog box displays most (although not all) of the Sub procedures that are available . More precisely, the Macro dialog only displays public procedures.

Once Excel displays the Macro dialog box, select the VBA Sub procedure you want to assign a keyboard shortcut to and click on the Options… button on the right side of the screen. The following screenshot shows how this looks like. I explain (in detail) the Sub procedures displayed in the Macro name list box (whose purpose is to delete blank rows) in this blog post .

Screenshot of Macro dialog box to modify Options

Step #3: Assign A Keyboard Shortcut

Once you've gone through step #2 above, Excel displays the Macro Options dialog. You can use this dialog to assign a shortcut key and (if you want to) a description to the relevant macro.

Assign keyboard shortcut to macro in Macro Options dialog

You can assign keyboard shortcuts that use either of the following forms :

  • Ctrl + Letter.
  • Ctrl + Shift + Letter.

As a general rule, I suggest that you :

  • Stick to keyboard shortcuts of the form “Ctrl + Shift + Letter”; and
  • Make sure that the keyboard shortcut you want to use isn't assigned by default.

Any keyboard shortcuts that you assign to a macro override Excel's pre-existing keyboard shortcuts . As a consequence of this, if you assign a keyboard shortcut that is exactly the same as a built-in one, you'll be disabling the built-in shortcut.

For example, “Ctrl + X” is the built-in keyboard shortcut for the Cut command. If you assign the keyboard shortcut “Ctrl + X” to a macro, you can no longer use that keyboard shortcut to cut.

Once you've assigned the keyboard shortcut to the macro, click the OK button on the lower right corner of the Macro Options dialog box to complete the process of creating the macro keyboard shortcut.

confirm macro keyboard shortcut

Next time you press the relevant keyboard combination (“Ctrl + Shift + M” in the example above), the relevant macro is executed.

In this Excel tutorial , you've read about different matters surrounding the topic of keyboard shortcuts in Excel . Among other things, you've seen:

  • The main (different) types of keyboard shortcuts you can use when working with Excel.
  • Some methods you can use to create custom keyboard shortcuts.
  • Several different lists and cheat sheets with keyboard shortcuts for Excel.

If you start implementing this knowledge, considering the approach I suggested at the beginning of this blog post, you're like to dramatically reduce your reliance in the mouse while working with Excel . This, in turn, will improve your speed and productivity in a consistent manner.

So go ahead and start (or continue) the process of becoming an Excel keyboard ninja and saving time.

Books And Resources Referenced In This Excel Tutorial

  • Harvey, Greg (2016). Excel 2016 All-in-One for Dummies. Hoboken, NJ: John Wiley & Sons Inc.
  • Jelen, Bill (2015). Excel 2016 in Depth. United States of America: Pearson Education Inc.
  • Walkenbach, John (2015). Excel 2016 Bible. Indianapolis, IN: John Wiley & Sons Inc.

guest

I publish a lot of Tutorials and Training Resources about Microsoft Excel and VBA . Here are some of my most popular Excel Training Resources:

  • Free Excel VBA Email Course
  • Excel Macro Tutorial for Beginners
  • Excel Power Query (Get and Transform) Tutorial for Beginners
  • Excel Keyboard Shortcut Cheat Sheet
  • Excel Resources

assign keyboard shortcut to macro excel

Assign a macro to a Form or a Control button

You can use a Form control button or a command button (an ActiveX control) to run a macro that performs an action when a user clicks it. Both these buttons are also known as a push button, which can be set up to automate the printing of a worksheet, filtering data, or calculating numbers. In general, a Form control button and an ActiveX control command button are similar in appearance and function. However, they do have a few differences, which are explained in the following sections.

In the sections below, learn how to add a macro to a button in Excel—for Windows or the Mac.

Note:  ActiveX controls are not supported on the Mac.

Macros and VBA tools can be found on the Developer tab, which is hidden by default.

The first step is to enable it. For more information, see the article:  Show the Developer tab .

Developer tab on the ribbon

Add a button (Form control)

Click the worksheet location where you want the upper-left corner of the button to appear. The Assign Macro popup window appears.

Assign a macro to the button, and then click OK .

To specify the control properties of the button, right-click the button, and then click Format Control .

Add a command button (ActiveX control)

Click the worksheet location at which you want the upper-left corner of the command button to appear.

In the Controls group, click View Code . This launches the Visual Basic Editor. Ensure that Click is chose in the drop-down list on the right. The sub procedure CommandButton1_Click (see the figure below) runs these two macros when the button is clicked: SelectC15 and HelloMessage .

A subprocedure in the Visual Basic Editor

In the subprocedure for the command button, do either of the following:

Enter the name of an existing macro in the workbook. You can find macros by clicking Macros in the Code group. You can run multiple macros from a button by entering the macro names on separate lines inside the subprocedure.

As necessary, add your own VBA code.

To run the VBA code that is now part of the button, click the ActiveX command button that you just created.

To edit the ActiveX control, make sure that you are in design mode. On the Developer tab, in the Controls group, turn on Design Mode .

Note:  Before you click Properties , make sure that the object for which you want to examine or change properties is already selected.

The Properties box appears. For detailed information about each property, select the property, and then press F1 to display a Visual Basic Help topic. You can also type the property name in the Visual Basic Help Search box. The following table summarizes the properties that are available.

Macros and VBA tools can be found on the Developer tab, which is hidden by default, so the first step is to enable it

Go to Excel > Preferences… > Ribbon & Toolbar .

Select the checkbox next to Developer to add it to the ribbon

Follow these steps:

Note:  If you have already inserted a button, you can right-click on it, and select Assign Macro .

Assign a macro to the button and click OK .

To specify the control properties of the button, right-click it, and then select Format Control... .  

Add a command button (Visual Basic control)

Click the worksheet location where you want the upper-left corner of the command button to appear.

In the Assign Macro dialog box, select New , which will open the Visual Basic Editor ( VBE ) to a pane with the following code:

In the subprocedure for the command button, between the Sub and End Sub lines, do either of the following:

Enter the name of an existing macro in the workbook. You can run multiple macros from a button by typing the macro names on separate lines inside the sub procedure

Add your own VBA code.

To edit the button, right-click it and choose Visual Basic .

To specify the control properties of the button, right-click it, and then select Format Control... .

Need more help?

You can always ask an expert in the Excel Tech Community  or get support in  Communities .

Add or edit a macro for a control on a worksheet

Enable or disable macros in Office files

Enable or disable ActiveX settings in Office files

Facebook

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

assign keyboard shortcut to macro excel

Microsoft 365 subscription benefits

assign keyboard shortcut to macro excel

Microsoft 365 training

assign keyboard shortcut to macro excel

Microsoft security

assign keyboard shortcut to macro excel

Accessibility center

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

assign keyboard shortcut to macro excel

Ask the Microsoft Community

assign keyboard shortcut to macro excel

Microsoft Tech Community

assign keyboard shortcut to macro excel

Windows Insiders

Microsoft 365 Insiders

Was this information helpful?

Thank you for your feedback.

How to Create Custom Keyboard Shortcuts in Microsoft Excel

Looking to make Excel your own? Here are the best ways to create custom keyboard shortcuts.

Full proficiency in Microsoft Office demands the use of keyboard shortcuts. Although there are plenty of built-in keyboard shortcuts for Microsoft Excel, for the best workflow you usually need to edit or customize those shortcuts to better fit your needs.

Luckily, Excel supports some customizable keyboard shortcuts, and we're here to show you how to create your own.

A Note on Default Excel Keyboard Shortcuts

Unfortunately, it's not all perfect in the Excel custom shortcut realm. Excel doesn't offer the functionality to override standard shortcuts, so you can't adjust the shortcut key for something that already exists.

Thus, there are three main types of keyboard shortcuts:

  • Standard shortcuts, like Ctrl + I for italics, which you cannot change in Excel.
  • Alt shortcuts, where you press the Alt key to activate shortcuts for Ribbon items. For example, pressing Alt > N > T will select the Insert tab on the Ribbon, followed by the Table option.
  • Macro shortcuts, which are completely customized. We'll discuss these in a bit.

So while you can't tweak any of the default shortcuts, you can still access quite a bit of functionality on the Ribbon or create macros. Thus, we'll use those options to make custom shortcuts.

1. Custom Quick Access Toolbar Commands

The Quick Access Toolbar (QAT) is a helpful strip of commands that always stays at the top of your screen. By default, it only includes a few options, such as save , undo , and redo , which you might want to remove, since they already have easy keyboard shortcuts. But you can add many more commands to the QAT.

We mentioned that Alt codes let you access anything on the Ribbon. If you press Alt , you should notice some popup shortcut keys that also appear near the QAT, giving you a one-step shortcut to any Excel command you wish.

To set up the Quick Access Toolbar in Excel as you like, click the dropdown arrow to the far right of the current icons. You can check a few common options here, but for a full list, you'll want to select More commands .

Excel Customize Quick Access Toolbar

This opens the customization window, where you'll see a list of available commands in the left box and your current QAT commands on the right.

Customizing the Quick Access Toolbar

Click any command in the right box and hit the Remove button to erase it or use the arrow buttons to the right of the box to re-order the current items. Once you've got that sorted, check out the left box to find new commands you want to add.

By default, the dropdown box shows Popular Commands , but you can change it to Commands Not in the Ribbon if you want to avoid duplicating what's already there. You can also show All Commands , but be warned that there's quite a long list.

Excel QAT Add Remove Options

Have a look through the list and select any functions you'd like instant access to. The QAT allows for lots of commands, so don't be afraid to pick several of your favorites or even find some new features to try.

If you want to create groupings, there's an option called <Separator> that lets you add a divider between icons. And to preserve your setup for the future, use the Import/Export button in this window to export your customizations.

Once you've got everything in order, just click OK to return to Excel with your new and improved QAT. When you want to access one of its functions, press Alt , followed by the number of the command, to execute it right away.

Excel Alt Toolbar Shortcuts

This is a lot faster than hunting through menus to find the right command.

2. Create Your Own Excel Macros

Macros are an extremely useful (yet often overlooked) feature of Office that allow you to record a series of actions and automatically play them back. If you have trouble remembering complex formulas, for example, you could create a macro to automatically apply the formulas to specific cells.

Macros are really just Visual Basic code running in the background to make Excel perform actions, but you don't have to know how to program to take advantage of macros.

To get started with using them, enable the Developer Ribbon tab by going to File > Options and choose Customize Ribbon on the left pane. On the right side, make sure Developer is checked, then press OK to return.

Excel Enable Developer Ribbon

Recording New Macros

Now you can record your first macro. Head to the Developer tab on the Ribbon and choose Record Macro in the Code section. Give it a name to remember it by (it can't have spaces) and assign it a key that works for you.

The shortcut box shows Ctrl + another key, but you can also add Shift to this by holding it while you select a key. Bear in mind that you can override standard keyboard shortcuts here, so if you make your macro run with Ctrl + Z , you won't be able to use that shortcut to undo anymore.

Under Store macro in , select Personal Macro Workbook. This is a file that allows you to share macros between Excel workbooks, which is perfect for defining shortcuts. If you want to run macros on only one workbook, change this setting to This Workbook instead. Give your macro a brief description if you like, then click OK .

Excel Record New Macro

From here, everything you do is recorded. Any cell you select, font changes you apply, or characters you type become your macro. So you could, for example, make a macro that selects cell B3, types "test," and formats it as bold. Once you've performed exactly the action you want to record, select Stop Recording on the Developer tab to end the macro.

Using and Editing Macros

After this, you can access your macro by choosing Macros from the same Code section of the Developer tab. This shows a list of your macros; press Edit to view the Visual Basic code for the macro, if you like. This is a good way to make sure you didn't record any extra steps, even if you don't fully understand the code.

Excel Macro Code Example

Once you're satisfied, you can press the key combo you assigned at any time to run your macro. To change the key combination later, select it in the list of Macros and choose Options .

Here's an advanced tip: you can even combine macros and the QAT. Once you've saved a macro, open up the QAT menu again and change the Choose commands from box to Macros . Select a macro you've created from the list, and you can add it to the QAT just like any other action.

Thus, if you have trouble remembering macro shortcut combos, you can instead run them from the QAT with Alt and a number.

What you do with macros is up to you, but there are tons of possibilities. Think of anything you do in Excel on a regular basis that you'd want to automate, and try making a macro for it. Automating these tedious tasks can save you hours of time.

Have a look at our guide to making Excel macros for more ideas and help.

Easily Create Custom Excel Shortcuts

Now you don't have to waste time performing repetitive tasks or hunting through Excel menus to find a particular feature. Shortcuts take a bit to set up and everyone will want different ones, but they're vital for Excel efficiency.

Macros aren't scary once you've done a few, and the QAT is handy for everyone. But those aren't the only timesavers Excel is hiding.

Image Credit: GooDween123/ Shutterstock

Excel Dashboards

Excel Tutorial: How To Assign Macro In Excel

Introduction.

When it comes to streamlining your workflow in Excel, macros can be an invaluable tool. By assigning macros, you can automate repetitive tasks, saving you time and effort in the long run. In this tutorial, we will guide you through the process of assigning macros in Excel so that you can take full advantage of this powerful feature.

Key Takeaways

  • Assigning macros in Excel can streamline your workflow and save time.
  • Macros in Excel automate repetitive tasks, increasing efficiency.
  • Recording a macro involves opening the developer tab, naming the macro, performing the actions, and stopping the recording.
  • Buttons and keyboard shortcuts can be assigned to macros for quick access.
  • Best practices include using clear names, organizing macros, and thorough testing.

Understanding Macros in Excel

A. Define what macros are in Excel

A macro in Excel is a set of instructions that can be recorded and saved to automate repetitive tasks. It allows users to perform complex operations with just a single click, saving time and effort.

B. Explain the benefits of using macros in Excel

  • Efficiency: Macros can help automate repetitive tasks, reducing the time and effort required to perform them manually.
  • Accuracy: By automating tasks with macros, the chances of human error are minimized, leading to more accurate results.
  • Consistency: Using macros ensures that the same steps are followed each time a task is performed, leading to consistent outcomes.
  • Productivity: With the time saved from using macros, users can focus on other important tasks, increasing overall productivity.
  • Customization: Macros can be customized to suit specific needs, allowing users to tailor them to their individual requirements.

How to Record a Macro in Excel

If you want to automate repetitive tasks in Excel, recording a macro can save you time and effort. Follow these steps to record a macro in Excel:

A. Open the developer tab in Excel

To begin recording a macro, you need to ensure that the developer tab is visible in Excel. If it's not already visible, you can enable it by going to the "File" tab, selecting "Options," choosing "Customize Ribbon," and then checking the "Developer" option.

B. Click on "Record Macro" and give the macro a name

Once the developer tab is open, click on "Record Macro" to start the recording process. You will be prompted to give the macro a name, which should be descriptive and easy to remember.

C. Perform the actions you want to automate

After naming the macro, you can start performing the actions in Excel that you want to automate. This can include formatting cells, entering data, or any other task that you want to be able to repeat with the macro.

D. Click on "Stop Recording" when done

Once you have completed the actions you want to automate, click on "Stop Recording" in the developer tab. This will finalize the recording of the macro, and it will be ready for use.

How to Assign a Macro to a Button in Excel

Assigning a macro to a button in Excel can help automate repetitive tasks, saving you time and effort. Follow these simple steps to assign a macro to a button in your Excel worksheet.

To begin, you'll need to insert a button in your Excel worksheet. You can do this by going to the "Developer" tab and clicking on "Insert" in the Controls group. From the drop-down menu, select "Button" and then click and drag to draw the button on your worksheet.

Once the button is inserted, right-click on it to display the context menu. From the menu, select "Assign Macro" to open the "Assign Macro" dialog box.

In the "Assign Macro" dialog box, you will see a list of available macros in your workbook. Select the macro that you want to assign to the button and click "OK".

After assigning the macro to the button, you can test it to ensure that it runs correctly. Simply click on the button to execute the assigned macro and observe the results. If the macro performs as expected, you have successfully assigned a macro to the button in Excel.

How to Assign a Macro to a Keyboard Shortcut in Excel

To assign a macro to a keyboard shortcut in Excel, follow these steps:

First, open the Excel workbook in which you want to assign a macro to a keyboard shortcut. Then, click on the "Developer" tab in the Excel ribbon. If you do not see the Developer tab, you may need to enable it in the Excel options.

B. Click on "Macros" and select the macro you want to assign

Once the Developer tab is open, click on the "Macros" button. This will open a window displaying all the available macros in the workbook. Select the macro to which you want to assign a keyboard shortcut.

C. Click on "Options" and choose a letter to use as a shortcut

After selecting the macro, click on the "Options" button. In the "Options" window, you can assign a letter to use as a keyboard shortcut for the selected macro. Choose a letter that is not already assigned to another function in Excel, as this could cause conflicts.

D. Test the shortcut to ensure the macro runs as expected

Once you have assigned the keyboard shortcut, close the "Options" window and return to your Excel workbook. Test the shortcut by pressing the letter you assigned. If the macro runs as expected, then you have successfully assigned a keyboard shortcut to the macro.

Best Practices for Assigning Macros in Excel

When it comes to assigning macros in Excel, there are a few best practices to keep in mind to ensure that your macros are easy to use and maintain. Let's take a look at some of these best practices:

A. Use clear and descriptive names for your macros

  • Use meaningful names: When creating macros, it's important to use names that clearly describe the action or function of the macro. This will make it easier for you and others to understand and use the macros in the future.
  • Avoid generic names: Avoid using generic names such as "Macro1" or "Button2_Click". These names are not descriptive and can be confusing, especially when you have multiple macros in your workbook.

B. Organize your macros in a separate module in Excel

  • Create a separate module: Instead of storing your macros directly in a worksheet or workbook, create a separate module in Excel to organize and store your macros. This will make it easier to manage and maintain your macros, and allows you to easily share them with others.
  • Group similar macros: Organize your macros by grouping similar functions together in the same module. This will help you find and manage your macros more efficiently.

C. Test your macros thoroughly before assigning them to buttons or shortcuts

  • Test for errors: Before assigning a macro to a button or shortcut, thoroughly test it to ensure that it works as intended. This will help you catch any errors or bugs before they cause any issues in your workbook.
  • Consider user inputs: If your macro requires user inputs, make sure to test it with various input scenarios to ensure that it handles different situations correctly.

Assigning macros in Excel is crucial for automating tasks and increasing efficiency. By creating custom macros, users can streamline their workflow and save time on repetitive actions. We encourage all Excel users to practice and experiment with assigning macros to become more proficient in using this powerful tool. The more you familiarize yourself with macros, the better equipped you will be to tackle complex tasks and improve your productivity in Excel.

Excel Dashboard

Immediate Download

MAC & PC Compatible

Free Email Support

Related aticles

Excel Tutorial: What Does #### Mean In Excel

Excel Tutorial: What Does #### Mean In Excel

Understanding Mathematical Functions: How To Call A Function In Vba

Understanding Mathematical Functions: How To Call A Function In Vba

Understanding Mathematical Functions: How To Add Function In Google Sheets

Understanding Mathematical Functions: How To Add Function In Google Sheets

Understanding Mathematical Functions: How To Fill In A Table Using A Function Rule

Understanding Mathematical Functions: How To Fill In A Table Using A Function Rule

Understanding Mathematical Functions: What Are The Basic Functions Of A Cell

Understanding Mathematical Functions: What Are The Basic Functions Of A Cell

Making Write 15 Minutes On A Timesheet

Making Write 15 Minutes On A Timesheet

Making Identify Sheet Sizes

Making Identify Sheet Sizes

Mastering Formulas In Excel: What Is The Formula For Standard Deviation

Mastering Formulas In Excel: What Is The Formula For Standard Deviation

Mastering Formulas In Excel: What Is The Formula Of Force

Mastering Formulas In Excel: What Is The Formula Of Force

Mastering Formulas In Excel: What Is Net Present Value Formula

Mastering Formulas In Excel: What Is Net Present Value Formula

Mastering Formulas In Excel: How To Write Formula In Google Docs

Mastering Formulas In Excel: How To Write Formula In Google Docs

Mastering Formulas In Excel: How To Do A Formula In Google Sheets

Mastering Formulas In Excel: How To Do A Formula In Google Sheets

  • Choosing a selection results in a full page refresh.

Android Police

Microsoft Excel:How to create, record, and run macros

M ost people rely on formulas in Excel, not the automation benefits of macros. Macros allow you to create complex data manipulations, streamline report generation, and more. Whether you're in finance, marketing, or another data-driven field, learning to write Excel macros is a game-changer. This guide demystifies Excel macros, helping you understand and use them. Macros work on PCs and Macs. If you have an affordable Chromebook , use Google Sheets and create similar programs using Google Apps Script.

What are Excel macros?

Macros in Excel are sequences of instructions that automate repetitive tasks. They are written in Visual Basic for Applications (VBA), a programming language developed by Microsoft. When you record a macro, you instruct Excel to remember a series of actions you perform. After being recorded, these actions can be executed automatically with a single command.

This is useful for repeating the same task across multiple datasets, streamlining data entry, and formatting processes. Excel macros can also generate standardized reports with consistent formatting and calculations.

Macros save time and minimize the risk of human errors during repetitive data processing. They handle large volumes of data efficiently, making Excel a robust tool for personal and professional use.

How to create Excel macros

There are two ways to create macros in Microsoft Excel. The first is by recording it. The other involves writing it using VBA language. If you're new to macros, it's easier to record it than it is to write it in VBA.

Macros work on Mac and PC. However, macOS doesn't support ActiveX Controls, which are often found in some macros.

Activate the Developer tab

Before creating a macro, activate the Developer tab in Microsoft Excel. This works on PC and Mac, but the steps to activate the Developer tab differ slightly from one operating system to the other.

Here's how to activate the Developer tab on a PC:

  • Open Microsoft Excel .
  • Click the File tab , located in the upper-right corner.
  • Click Options in the lower-left corner.
  • Click the Customize Ribbon tab, located to the left of the window.
  • Tick the Developer checkbox .

Here's how to activate the Developer tab on a Mac:

  • Open the Excel preferences by pressing the Cmd + Comma ( , ) keys simultaneously on your keyboard. Alternatively, click Excel on your Mac's top menu and select Preferences .
  • Click Ribbon & Toolbar .
  • Scroll down in the list to the right, and tick the checkbox next to Developer.

After completing these steps, the Developer tab appears in the Excel ribbon, regardless of whether you're on a Mac or PC. You'll need it to use macros.

How to use Microsoft 365 (Office) on a Chromebook

Record an excel macro.

The easiest way to create a new Excel macro is by recording it. When the recording is on, all the tasks you perform are recorded. After you save the tasks, Excel automatically reproduces them for you.

Before you begin, list the tasks you want to perform to avoid mistakes. If you make an error, edit the macro's code or delete it and start over.

  • Open a new or existing Microsoft Excel spreadsheet.
  • Under the Developer tab , click the Record macro button .
  • Give your macro a name.
  • Choose where to save it. In addition to the current workbook, you can create a new one.
  • Select Personal Macro Workbook to use it in any Excel spreadsheet you open.
  • Optionally, assign it a keyboard shortcut and give it a description.
  • Click OK to start recording the macro.
  • Perform the tasks and actions for the process you want to automate. All clicks, data input, formulas, and formatting are recorded.
  • Click Stop Recording to finish. The button is under the Developer tab. It replaces the Record macro button.
  • Your macro is saved.

Write an Excel macro

You can write your macro from scratch if you're familiar with VBA. To do this, create a new macro:

  • Under the Developer tab , click Visual Basic .
  • Write your macro in VBA using the parameters you want.

How to run Excel macros

Now that you've created your Excel macros, let's see how to run them. There are two ways to do it. The first is picking the macro you want to use from the menu, which is a bit complex. If you use it often, assign a macro to a button, making it easier to run.

Access your Excel macros

To access the Excel macros you created and run them using the menus, follow the steps below:

  • Under the Developer tab , click Macros .
  • Select the macro you want to run.
  • Click Run .

Assign a button to an Excel macro

If you plan to use a specific macro often, assign it to a button. Then, place the button anywhere you want.

  • Under the Developer tab , click Button .
  • Choose the spreadsheet where you want to put the button.
  • Select the macro it needs to run.
  • To change how the button looks, right click and select Customize controls .

Do more with Microsoft Office

Microsoft Office offers productivity tools that make your day at work easier. OneNote makes note-taking a breeze and helps you keep track of them across devices. PowerPoint offers templates and themes to make presentations consistent and good-looking. If you work with spreadsheets, Excel helps you manage CSV files .

Microsoft Excel:How to create, record, and run macros

IMAGES

  1. 2 Ways to Assign Keyboard Shortcuts to Macros

    assign keyboard shortcut to macro excel

  2. 2 Ways to Assign Keyboard Shortcuts to Macros

    assign keyboard shortcut to macro excel

  3. 2 Ways to Assign Keyboard Shortcuts to Macros

    assign keyboard shortcut to macro excel

  4. How To Assign Keyboard Shortcuts To Macros In Excel

    assign keyboard shortcut to macro excel

  5. Assign a Macro to a Shortcut Key

    assign keyboard shortcut to macro excel

  6. 2 Ways to Assign Keyboard Shortcuts to Macros

    assign keyboard shortcut to macro excel

VIDEO

  1. Assign Keyboard Shortcuts to Macros in Excel

  2. How to Create Custom Keyboard Shortcut to Run Any VBA In Excel

  3. 3 methods to assign keyboard shortcut key to a macro

  4. How to Make a Macro Button in Excel

  5. 8 Awesome Keyboard Shortcuts For The Vba Editor In Excel

  6. How To Add A Macro To A Button In Excel 2016

COMMENTS

  1. 2 Ways to Assign Keyboard Shortcuts to Macros

    How To Assign Keyboard Shortcuts To Macros In Excel Watch on Download the Excel File You can download the example Excel file I use in the video here: Assign-Keyboard-Shortcut-to-Macro.zip Download Create Your Own Keyboard Shortcuts to Run Macros Assigning keyboard shortcuts to simple or complex macros can help you work faster in Excel.

  2. How to Run a Macro in Microsoft Excel by Assigning a Shortcut Key

    The Macro Options window is a simple and convenient way to assign a shortcut key to a macro. You can access it from the Developer tab, the View tab, or the keyboard shortcut Alt+F8. Here are the steps to create a macro shortcut key using this method: 1. Go to the Developer tab and click on the Macros button.

  3. How to add a keyboard shortcut for a macro in excel

    2 Answers Sorted by: 0 Press Alt + F8 to open macro dialog box. Select the macro for which you want to assign shortcut key and click Options. There you can assign a shortcut to the macro. More detailed answer you can find here Share Improve this answer Follow

  4. excel

    9 Answers Sorted by: 39 According to Microsoft's documentation On the Tools menu, point to Macro, and then click Macros. In the Macro name box, enter the name of the macro you want to assign to a keyboard shortcut key. Click Options. If you want to run the macro by pressing a keyboard shortcut key, enter a letter in the Shortcut key box.

  5. Excel Macro Shortcut Key (2 Suitable Ways)

    If you enter an uppercase letter, then the shortcut key combination will be CTRL + SHIFT + your assigned key. It is demonstrated in the following picture. Subsequently, click on the drop-down icon under the Store macro in option. As a result, you will see 3 options in the drop-down menu.

  6. How to run macro in Excel and create a macro button

    Tip. If the Developer tab is not added to your Excel ribbon, press Alt + F8 to open the Macro dialog. Run a macro with custom keyboard shortcut If you execute a certain macro on a regular basis, you can assign a shortcut key to it. A shortcut can be added while recording a new macro and to an existing one. For this, carry out these steps:

  7. Customize keyboard shortcuts

    Select Remove. Use just the keyboard to assign or remove a keyboard shortcut Press ALT+F, T to open the Word Options dialog box. Press DOWN ARROW to select Customize Ribbon. Press the TAB key repeatedly until Customize is selected at the bottom of the dialog box, and then press ENTER.

  8. How To Assign Keyboard Shortcuts To Macros In Excel

    Sign up for our Excel webinar, times added weekly: https://www.excelcampus.com/blueprint-registration/In this video I explain 2 ways to create keyboard short...

  9. microsoft excel

    1 Answer Sorted by: 2 The way to view and change the mapping of keyboard shortcuts to macros in Excel is completely different than in Word. Maybe that's what's tripping you up. In Excel: In the Ribbon, click View → Macros dropdown → View Macros. That will display the "Macro" dialog.

  10. How to Run a Macro in Excel

    To run a macro in the VB Editor: Open the VB Editor (Developer tab > Visual Basic, or ALT+F11) In the Project window, double-click the module containing the macro you want to test. In the module's code window, place the cursor anywhere on the macro's code between "Sub" and "End Sub". Click the Run button on the toolbar, or press ...

  11. How can I assign Excel keyboard shortcuts for macros that use keys

    There are 3 different kinds of keyboard shortcuts I know of: Ones like Ctrl-C for Copy ; Ones like Alt-E-S-V for Edit > Paste; Special > Values A shortcut key combo you assign to your own macro. . . 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.

  12. Assign a Macro to a Shortcut Key

    1. Press Alt+F8 to display a list of macros. 2. Click on the desired macro name. 3. Click the options button. In the Options dialog, type a shortcut key. Note to assign a macro to Ctrl+Shift+S, you simply type Shift+S into the box. Click OK. Additional Details: You can assign a macro to the unshifted shortcut keys.

  13. Create or run a macro

    To run a macro, click the button on the Quick Access Toolbar, press the keyboard shortcut, or you can run the macro from the Macros list. Click View > Macros > View Macros . In the list under Macro name , click the macro you want to run.

  14. In Microsoft Excel, how do I assign a shortcut key to a macro?

    Assigning a shortcut to an existing macro. In Excel 2010 and 2007, from the View tab, click Macros, and then select View Macros . In Excel 2003 and earlier, from the Tools menu, select Macro, and then Macros.... A dialog box containing all macros in all open workbooks will appear. Click the macro for which you would like to create a key ...

  15. How To Assign A Macro To A Keyboard Combination In Excel

    Click on the 'Insert' option and select 'Module' to create a new module. Type the Macro code that you want to assign to a keyboard shortcut. Switch back to Excel and press Alt + F8 to open the Macro dialog box. Select the Macro that you want to assign to a keyboard shortcut and click on 'Options'.

  16. 2 Ways to Assign Keyboard Shortcuts to Macros

    Assigning keyboard shortcuts to simple or complex macros can help you my faster in Excel. These is especially true if you have to executing the identical actions repeatedly. In this post we look at two popular ways to create the shortcut keys. These include an Micro Options window and VBA code for the Application.OnKey method.

  17. Assign Macros to Keyboard Combos in Excel

    Keyboard shortcuts can be assigned via floating buttons, quick access toolbar, or directly through the keyboard. Creating keyboard shortcuts for macros can increase productivity and efficiency. Understanding Macros in Excel Macros are a series of recorded keystrokes and commands that you can use to automate repetitive tasks in Excel.

  18. Excel Keyboard Shortcuts: Easily Get Or Create Any Keyboard Shortcut

    Step #1: Turn Key Tips On Step #2: Press The Key (s) That Corresponds To The Ribbon Section You Want To Go To Step #3: Continue Pressing The Key (s) That Corresponds To What You Want To Activate How To Use Ribbon Keyboard Shortcuts For Excel: An Example Old Alt + Menu Keyboard Shortcuts

  19. Microsoft Excel: How Do I Assign a Keyboard Shortcut to an ...

    Assign a Keyboard Shortcut to Existing Macro. Open Excel file containing the macro (FYI, I'm using the same file I used in the earlier post).

  20. Assign a macro to a Form or a Control button

    Add a button (Form control) On the Developer tab, in the Controls group, click Insert, and then under Form Controls, click Button . Click the worksheet location where you want the upper-left corner of the button to appear. The Assign Macro popup window appears. Assign a macro to the button, and then click OK.

  21. How to Create Custom Keyboard Shortcuts in Microsoft Excel

    For example, pressing Alt > N > T will select the Insert tab on the Ribbon, followed by the Table option. Macro shortcuts, which are completely customized. We'll discuss these in a bit. So while you can't tweak any of the default shortcuts, you can still access quite a bit of functionality on the Ribbon or create macros.

  22. Excel Tutorial: How To Assign Macro In Excel

    From the drop-down menu, select "Button" and then click and drag to draw the button on your worksheet. B. Right-click on the button and select "Assign Macro". Once the button is inserted, right-click on it to display the context menu. From the menu, select "Assign Macro" to open the "Assign Macro" dialog box.

  23. excel

    7. Since Outlook doesn't have the OnKey event, the easiest way is to assign a toolbar button to the macro and put an ampersand in its name somewhere. This only works if your shortcut letter doesn't conflict with an existing shortcut. You may also have luck with setting a global hotkey, but it's usually more pain then it's worth:

  24. Microsoft Excel:How to create, record, and run macros

    Optionally, assign it a keyboard shortcut and give it a description. ... Assign a button to an Excel macro. If you plan to use a specific macro often, assign it to a button. Then, place the button ...