Customize Excel Ribbon and make your macros default commands

I believe that efficient use of Microsoft Office Excel has a tremendous effect on office work efficiency. With this blog I start a series of powerful tips and tricks that I’m sure will change the way you perform some tasks and will make using Excel a sort of fun!

If you have created a macro to do a specific job regularly, have you ever wondered how you can make the macro available in any Excel file you open (even new ones)? Moreover, have you ever wished to have all of your own, customized macros appear in a new, separate tab in the Excel Ribbon? Then follow the below steps that walk you through an example to list a macro (Profitability macro in this example) in a new tab (My Macros tab) in the Excel 2010 Ribbon.

When the macro, so called Profitability, runs it performs some calculations (Profit and Grand Total) and reformats the range of data in terms of cell background color and number formatting as shown below.

(1) Before running Profitability macro (2) after running Profitability macro

The default Ribbon tabs found in Excel 2010 are the ones show in figure (3). In this example we are going to add a new customized tab named “My Macros”. The My Macros tab will include the Profitability macro that we need to be available by default in any Excel file we open afterwards.

(3) Ribbon in Excel 2010 before adding customized tabs

First, you have to make sure you save the original macro as Excel Add-in (figure 4) then you close and reopen Excel.

Figure (4) saving the macro as Excel Add-in file

Then you need to enable the add-in by going to the Backstage view (File menu)>Options>Add-Ins (figure 5). Select Excel Add-ins from the Manage dropdown list and hit Go. From the Add-Ins window select the new add-in you created for the macro and hit OK (figure 6).

Figure (5)                                                    Figure (6)

By this your macro is accessible by any Excel file you open but we need to make it appear as any other command in the Ribbon tabs. Go to the Backstage view and hit the Options button, then hit the Customize Ribbon button on left pane.

Select Macros option from the ‘Choose Commands From’ dropdown list. All macros available in the workbook will be listed as shown in figure (7).

The next step is to create a new tab to be displayed in the Ribbon. Hit the New Tab button in the bottom right side of the window. A new tab will be automatically added under the Main Tabs list as shown in the figure.

Figure (7)

Rename the new tab to any name you like, ‘My Macros’ for instance, by hitting the Rename button. Write the new name in the Display Name text box that appears (Figure 8). The new tab will be changed to ‘My Macros’ as soon as you hit the OK button.

Figure (8)

Any new tab would have to have at least one Group of commands. You can elect to change the name of the Group to another name that best describes the group of commands you wish to insert in the Group. Let’s select ‘Financial’ as a Group name (Figure 9).

Figure (9)

Now, it’s time to add your macro (Profitability) to the Financial Group under the new My Macros tab. Make sure the Financial Group is selected in the right pane, then select the macro you want to add from the left pane and hit the Add button in the middle of the window (figure 10).

Figure (10)

By this you will have a new tab with the hierarchy of My Macros>Financial>Profitability. You may want to change the symbol of the macro to another meaningful one, e.g. select the dollar sign ($) as a symbol for the Profitability macro (figure 11)

Figure (11)

By hitting the OK but to close the Excel Options window you will see your new tab shown in the Ribbon with the hierarchy you built as shown in figure 12.

Figure (12)

By this you have made your own automatic procedure (macro) available for any file you open with Excel and that is placed as a command item in your own Ribbon tab.