How to Permanently Add Macros in Excel Ribbon?

Having macros that automate custom tasks in Excel can be huge time savers. Like any tool, it would be ideal to have these macros easily accessible so we can run them any time we need them.

We often add buttons (like radio buttons) in specific worksheets to run our macros. However, finding the button to run the macro would be too burdensome if you have loads of worksheets in your workbook.

This is why setting up our macro button in the Excel Ribbon is a good idea. Whatever sheet we may be on, we can always run the macro with just a few clicks.

Also, if you have macros that you want to be available in other workbooks as well, you can do so when you add them to the Excel Ribbon.

Steps to Permanently Add Macros in Excel Ribbon

Step #1: Add the Macros to the Personal Macro Workbook

A Personal Macro Workbook is a special workbook that runs in the background whenever we open the Excel app.

We will use this workbook to store the macros that we have created. This way, the macros are still accessible even when we open a different Excel file.

To create a personal macro workbook:

1. Go to the Developer tab and click the Record Macro button.

2. The Record Macro menu will appear.

Click the “Store macro in” dropdown list. Select Personal Macro Workbook. After that, click OK.

Excel will then proceed with recording whatever you do in the file and create its corresponding macro.

I’m assuming that you already have your macros ready, so all you need to do next is stop the recording by clicking the Stop Record button (just below the first worksheet).

(However, if you need to record a macro, please proceed with performing the steps you wish to be automated in the workbook and then click on the stop button once done.)

3. Once you click the Stop Recording button, the Personal Macro Workbook should now be created.

To verify this, press ALT + F11 to open the VBA Editor.

On the list of projects, you should see PERSONAL.XLSB.

If you did some steps while the Record Macro is turned on, you should see the macro recorded on Module1 of this file.

4. Highlight the entire code in this module and delete it (if needed).

Then, add the code of your macros there.

That’s it! Your Personal Macro Workbook should now all be set up.

Step #2: Customize the Excel Ribbon

Now, it’s time to add the button(s) in the Excel Ribbon.

(Note: If you prefer adding a button in the Quick Access Toolbar, you can proceed to the next section of this article.)

1. Right-click anywhere on the Excel Ribbon.

2. A pop-up menu will appear. Select Customize the Ribbon.

3. In the Excel Options menu, click the New Tab button.

The new tab will then be inserted in the list of Main Tabs.

4. Right-click on the new tab and select Rename.

5. Enter your desired tab name.

In my example above, I’ve named it “Macros”.

6. Next, let’s rename the group under the current tab.

Right-click on New Group (Custom) and select Rename.

Type the appropriate name for the group.

In my example above, I’ve named the group “Worksheet” because the macros that I will add to it are worksheet-related functions.

If you have multiple macros to add, you can add more groups so you can group related macros.  

To do this, just click the New Group button at the bottom.

The new group will then be added to the current tab.

Don’t forget to give it a new name by right-clicking on it and selecting Rename.

7. After adding all the groups, it’s now time to add the macro buttons.

1. Click the first group you would like to add buttons to.
2. Click the “Choose commands from” dropdown list. From the list of options, select Macros.
3. You should see the macros added in the PERSONALXLSB file. Select the first macro you would like to add to the group.
4. Once selected, click the Add >> button.

Repeat these steps until you’ve added all macros to their appropriate group.

8. Next, let’s rename each of the macros to specify how they will appear on the buttons.

1. Right-click on the macro and select Rename.
2. Choose the symbol or the image that you’d like to appear on the button.
3. Edit the Display Name.
4. Once done, click OK.

Repeat these steps until you’ve renamed all your macros and added their corresponding symbols.

9. You can re-arrange the order of the macros, the groups, and the tabs.

You just need to select the object (whether it’s the macro, group, or tab) and click the up or down arrow on the right side.

10. If you’re happy with the names and the arrangement of the tab, click the OK button.

And that’s it! You have successfully added your macro(s) to the Excel ribbon.

You can click on these macro buttons to test them.

Also, try closing the current file. Then open a new one and see if you still have the macro buttons visible in the Excel Ribbon.

You can also add a custom autofill list after creating your customized Excel ribbon.

ADDITIONAL TIPS

• If you must update your macros and need to temporarily disable or hide the buttons from the Excel Ribbon, just uncheck the corresponding tab from the Customize Ribbon menu (as shown below).

• To permanently remove them from the Excel Ribbon, right-click on the corresponding tab and select Remove.

• To reset all the changes made to the Excel Ribbon, just click Reset >> Reset All Customizations.

Permanently Add Macros in the Quick Access Toolbar

Aside from the Excel Ribbon, you can also permanently add the macros in the Quick Access Toolbar (QAT).

By default, the QAT is located just above the Excel Ribbon.

With the QAT, you can add the macros as icons on top. Users can readily access them without browsing through the tabs. You won’t, however, see the names of each function unless you hover the mouse over each icon.

To add your macros to this toolbar, do the following:

1. Add the macros to the Personal Macro Workbook (same steps as above).

2. Click the Customize Quick Access Toolbar button (see the last icon in the QAT).

A pop-up menu will show up. Select More Commands.

3. You’ll then be redirected to the Excel Options for Quick Access Toolbar.

1. In the “Choose commands from” dropdown list, select Macros.
2. From the list of macros that appear, select the macro you want to add to the QAT.
3. Click the Add >> button.
4. You should now see your selected macro in the list of QAT functions.

4. Next, we’ll modify the macro to change its display name and icon.

1. Select the macro from the list of QAT commands.
2. Click the Modify button.
3. Select the icon that you’d like to appear in the QAT.
4. Edit the display name.
5. Once done, click OK.

5. You should now see the updated name and icon for your macro. If you’re happy with it, click OK.

6. That’s it! You should now see your macro added to the QAT.

Conclusion

If you want your macros to be accessible from any Excel workbook that you open, you need to add them to the Excel Ribbon (or the Quick Access Toolbar) as described in the steps above. Remember to add the macros to the Personal Macro Workbook – this is the only way the macros can run from any Excel file on your computer.