Add Macro to Quick Access Toolbar and Ribbon

In the previous blogs, we had learned how one can start automation in excel by recording a macro and then running it. We had also learned four ways to run a macro in Excel. Among these four ways, we saw how to insert a shape or a button in excel and then assign macro to it. This enabled us to instantly run our macro at a click of a mouse. Now, in continuation of our previous blogs, as we go ahead, we would unlock the technique to add the macro button on the ‘Quick Access Toolbar’ and the ‘Ribbon Tabs’.

Before starting this, let us get a brief idea about what a ‘Quick Access Toolbar’ and ‘Ribbon Tabs’ are, and what are the differences.

Quick Access Toolbar vs Ribbon in Excel

Quick Access Toolbar, as the name defines, is a toolbar in Excel which helps you to access the most used excel functions quickly. This toolbar is available on the Title Bar of the Excel workbook.

The highlighted portion in the below image is the ‘Quick Access Toolbar’. 

Quick Access Toolbar

When you install the Microsoft Excel Utility on your computer system, by default, the Quick Access Toolbar would have three buttons – Save, Undo and Redo (Refer to the image above).

However, you can customize the ‘Quick Access Toolbar’ according to your choice.

A Ribbon in Excel is the area that holds all the functions that you can perform in excel. The ribbon options are organized under three – Tabs, Groups, and Functions. 

All the functions in excel are placed into logical groups and these groups are placed under various excel ribbon tabs.

For Example, the function Text to Columnis under the group called ‘Data Tools’. The ‘Data Tools’ group is logically placed under the tab named ‘Data’.

In the below image, you can see how the entire ribbon looks and its hierarchical structure of Tabs > Groups > Functions

Understanding Tabs, Ribbons and Groups

Now, when you have the understanding of the Quick Access Toolbar and the Ribbon, we are good to start with learning to add the macro button to it.

Add Macro to Quick Access Toolbar or Ribbon

Add Macro Button to Quick Access Toolbar

Before, adding the macro button to the quick access toolbar, we need to have at least one macro inserted into our excel workbook. To insert a new quickly go through our blog on ‘Start Automation – Record A Macro in Excel’.

I have created a macro that writes the text “Excel Unlocked” in cell range A1:A5 and then changes the font formatting of the cells to Bold.

Now, when you have a macro recorded in your workbook, follow the undermentioned steps:

Firstly, place your mouse cursor on the quick access toolbar options, and right-click to open the list of options.

From the list that comes up, click on the ‘Customize Quick Access Toolbar’ option.

Customize Quick Access Toolbar

The ‘Excel Options’ dialog box would come up on your screen.

Excel Options - Quick Access Toolbar

Now, click on the option ‘Macro’ from the drop-down list that appears under the section ‘Choose Command From’.

Choosing Macros Command

As a result, you would notice that in the box below it the recorded macro is listed (In my case it is ‘WriteName’. 

If in case you have more than one macros recorded in the workbook, you would notice all of them over here.

Selecting Recorded Macro

Select the macro and click on the ‘Add’ button. As a result, this would insert the macro in the right pane (below Redo function).

Adding Recorded Macro to QAT

Now, select the macro, click on the button ‘Modify’ and choose the button of your choice.

Also, you can change the default display name, by entering some logical name to your macro button. Refer to the screenshot below:

Change Display Name of Macro - QAT

Click on OK to exit. Consequently, the excel inserts the selected macro button on quick access toolbar.

Macro Added To QAT

Now just click on it and you would notice that excel runs the macro.

Macro Run Using QAT Macro Button

Add Macro Button on Ribbon

You can even add the macro button on the ribbon bar in the:

  1. Existing Tab and New Group ; or
  2. New Tab

Note that you cannot add any custom option in the existing group under the existing Microsoft provided tabs.

Let us now learn this technique. In this section, we would create a new tab and add the macro button over there. 

Follow the undermentioned steps:

Firstly, hover your mouse cursor on the ribbon tab and right-click to get the option.

Click on the option ‘Customize the Ribbon’.

Customize the Ribbon Navigation

In the ‘Excel Options’ dialog box that appears, select on any of the tab names on the right pane and click on the ‘New Tab’ button.

Excel Options - Add New Tab

The excel inserts a new tab, having a default name as ‘New Tab (Custom)’. To change the position of this new custom tab, use the up and down buttons.

Moving New Tab Up and Down

Rename this new tab using the ‘Rename’ button. Or you can right-click on the tab and click on the ‘Rename’ option. I have renamed it as ‘Excel Unlocked’.

Rename New Excel Tab

Similarly, click on the ‘New Group (Custom)’ option under this new tab and rename it as ‘Favorite Macros’.

Rename New Excel Group

The new tab and the new group is ready now.

Now, firstly click on the group ‘Favorite Macros’ on the right pane. Then select the option ‘Macro’ from the drop-down list on the left pane, and select your macro.

Finally, click on the ‘Add’ button to insert the macro to this new tab under the selected group.

Adding Macro to New Tab

You can also rename and change the macro icon by selecting the macro, then clicking on the ‘Rename’ button.

Rename Macro

As a result, excel adds the new tab to the ribbon.

New Tab, Group and Macro Added to Ribbon

Just click on this new button and the excel would execute the assigned macro.

This brings us to the end of this blog.

Leave a Comment