My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

You are here: Home / Excel VBA / Customize the Ribbon in Excel
Customize the Ribbon in Excel
by

You can customize the Ribbon in Excel so that you add your own tabs and icons to run commonly used tasks and tools, or run your macros.

NOTE : This applies to Excel 2010 and 2013 only. Modifying the Ribbon in Excel 2007 is a different process which I will cover in another post.

Just follow the steps below, it should only take about 5 minutes.

You can also modify the Quick Access Toolbar to show your own icons for macros and commands.

Step 1

Right click your mouse anywhere on the QAT or the Ribbon, and from the menu that appears, click on Customize the Ribbon...

Right Click Menu to Customize the Ribbon

Step 2

The next window that appears has a list of commands on the left had side of the main pane, from this you can choose commands to add to the Ribbon.

Excel Options for the Ribbon

You can uncheck the default tabs listed on the right hand side, but you can't remove them. You can open up the tabs and remove items in them though. For example, if you double click on Home to open it, you see the groups in the Home tab like Clipboard, Font, Alignment etc. These items can be removed.

Step 3

Click the New Tab button to create a new tab. This will be added to the right hand side of the pane with the default name New tab (Custom), and a new group inside it named New Group (Custom).

Create a new tab

Step 4

Select the new tab and then you can either right click and choose Rename, or click on the Rename button.

Rename the new tab

Enter a new name for the tab and click OK. I'm calling mine PGT.

Rename the new tab

Step 5

Select the new group and rename it as we just did with the tab. When you click on Rename you'll see this window.

Renaming the new group

Enter the new group name in the Display name box. Choosing an icon has no effect. My new group is called PGT's Macros.

Step 6

OK so I've renamed my tab and group, now I need to add some commands or macros to it.

If you leave the Choose commands from drop-down as Popular Commands then you can add regular Excel commands, but I want to add some awesomely cool macros, so I'm choosing Macros from the drop-down.

This is what you want to choose if you want to add macros from your Personal.xlsb.

Select macros as source

Step 7

From the list of macros that are now displayed, click on the macro you want to add, then click on the Add>> button. I've added two macros.

Choose the macros you want to add

Step 8

As with the new tab and group, we now need to rename our macros to something more meaningful. Right click the macro and choose Rename.

Rename the macros

Step 9

You should now be looking at a large list of icons. Choose the icon you want for your macro. I'm going with a bright green square (I like green).

In the Display name box, enter a name for the macro, this also becomes the tool tip you see when you hover your mouse over the icon.

Repeat this process for each macro you added.

Choose new icons and names for the macros

Step 10

Once you have renamed all your macros you should have something like this.

Changes to macros finished

Step 11

You can adjust the position of the tab, group(s) and items in each group by first selecting the tab, group or item, and clicking the Up/Down arrows.

how to reorder items

Step 10

When you are finished with your changes, click OK to close this window and you'll see that you now have a new tab on the Ribbon with your new group and icons for the macros.

The Customized Ribbon

Customize the Ribbon in Excel

More Excel VBA Posts

automating and emailing pivot table reports
Automate the creation and distribution of pivot table reports with some VBA. Send reports to multiple email recipients using Outlook.
Checking values in range objects with vba
Use built in tools or your own code to examine the values contained within Range objects in VBA. Sample code to download.
Static variables in VBA
Variables normally cease to exist once your Sub or Function has ended. But Static Variables allow you to preserve values after your code has finished.
save chart as image
List all the charts in your workbook then select the ones you want to save as either PNG or JPG. Sample workbook and code to download
Clearing Downstream Dependent Data Validation Lists
Change one of your data validation lists and clear the values in the other data validation lists dependent on your first choice.
Excel Status Bar
Use the Excel Status Bar to send messages to your users and to show a progress bar for your VBA code
Progress Bar for Excel VBA
Create your own progress bar for VBA in Excel. Use it to show that your code is still running, and how long before it finishes.
error handling in vba
Understand how Excel VBA generates errors, how to control what Excel does when an error occurs, and how to write your own error handling routines.
Finding File Metadata Using FileSystemObject
Find file meta data like the creation date, last modified date and file size using Windows FileSystemObject in Excel VBA
Automatically Add Items to Data Validation List
Automatically Add Items to Data Validation List by typing in the new data. Then sort the source list for bonus points

More Excel VBA Posts

Display All Matches from Search in Userform ListBox
Search a range for all partial and full matches of a string, and display matching records (entire rows) in a userform listbox. Sample code and userform.
animating excel charts
Use animation correctly to enhance the story your data is telling. Don't animate your chart just for some eye candy. Sample code and workbook to download.
dynamic data validation lists in userforms
Data validation lists using the same source that are dynamically modified to prevent the same choice being made in each list.
show report filter pages for power pivot pivottables
PivotTables created from Power Pivot can't use the 'Show Report Filter Pages' option. But this piece of VBA allows you to do just that.
charting real time data in excel
Receive data in real time and chart the data as it arrives. Can be used to chart things like stock prices or sensor readings. Sample code and workbook
select multiple items from drop down data validation list
Choose multiple items from a data validation (drop down) list and store them all in the same cell. Sample workbook with working VBA.
Excel Calendar (Date Picker) to Use in Worksheets and Userforms
Easy to use, highly customizable and multi-language. This date picker is implemented as a userform that is simple to integrate into your workbook.
automating and emailing pivot table reports
Automate the creation and distribution of pivot table reports with some VBA. Send reports to multiple email recipients using Outlook.
search for data with userform
Search a list of records (like a table) using a user form, and then populate the fields of the search form when the record is found.
Checking values in range objects with vba
Use built in tools or your own code to examine the values contained within Range objects in VBA. Sample code to download.
Category: Tag:

Reader Interactions

Comments

  1. Stefano Gatto

    Hello – thank you for explaining here how to customize the ribbon to add access to new functions which are implemented in personal macros. But what happens if those macros are not open in Excel? Back with Excel 2000/2003, I made a macro which was adding a menu in Excel whenever you opened it, and was removing it whenever you were closing it. Of course it worked under the form of an addin too. The advantage was that when you uninstalled the addin those extra menuitems disappeared. So in conclusion it would be nice to have the manual steps you demoed here, be programmed in the AutoOpen of my workbook implementing the extra functionality (addin). Is this possible?

    Reply

  3. Bob

    Customization of your ribbon can be a significant productivity boost, but there is a downside.

    For example: I had put many icons on the quick access tool bar to improve productivity on my workstation. That was all well and good until the day I went to take an MOS certification test. I found myself in front of a tiny 15″ CRT monitor, and standard Excel ribbon. Hmmmm. I spent a good deal of test time searching the ribbon for icons that I had become accustomed to just grabbing from the quick access tool bar. It was funny afterward, but not in the moment.

    My only point is – if you’re prepping for an MOS test you might want to reset your workstation to the standard ribbon and turn off, or temporarily remove, your quick access tool bar icons.

    Reply

  4. MF

    Do you really have the macros that can “Fix everything” or “Increase my salary”??? heeheee… Pls share if it exists. 🙂

    Reply

    • Philip Treacy

      Hi Arvind,

      I’m not sure what you mean by adding a location – can you explain a bit more please?

      Regards

      Phil

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Current ye@r *