Customize Excel Ribbon and make your macros default commands

Posted: September 14, 2010 in Power of Microsoft Office
Tags: ,

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.

clip_image002 clip_image004

(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.

clip_image006

(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.

clip_image008

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).

clip_image010 clip_image012

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.

clip_image014

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.

clip_image016

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).

clip_image018

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).

clip_image020

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)

clip_image022

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.

clip_image024

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.

Comments
  1. lubna says:

    Brilliant!
    Thanks

  2. ibrahim says:

    Good work

  3. Jim Palmer says:

    Thanks

    I had done a lot of customization to my Excel menu in version 2003 and when I recently switched to Excel 2010 I really missed it.

    All of the macros on my custom menu are in my personal macro file. Whenever I attempted to select macros from the “choose commands from” drop down I kept getting the dreaded message “Microsoft Excel has encountered a problem and needs to shut down”.

    I discovered that I had to first unhide the personal macro workbook and then I could proceed to add my macros to the ribbon.

  4. Wade says:

    Do you know if there is a way to change to macro linked to a button after you have already created it? I can’t figure it out and keep having to create a whole new button in order to link it to a different macro.

    Great post and thank you in advance for any help you can provide on this.

    • Hi Wade,

      Thanks for visiting my blog.

      If I understand you correctly you have an old macro that is linked to a button under a certain tab in your Excel Ribbon, and you have created a new macro and you need to replace the old macro with the new macro under the same button.

      In this case you just need to go to File (Backstage), Option, Customize Ribbon, from the “Customize the Ribbon” menu select your tab and the old macro under it, then click the Remove button. Then select Macros from the “Choose Commands from” menu on left and select the new macro then add it to the same tab by clicking the Add button.

      By this you should see the new macro placed under the same button on your Ribbon.

      Hope this helps.

  5. sandeep says:

    I have implemented similar customization for my application. The Customize ribbon is appearing for every xl sheet opened on my machine. But when i am sharing it through N/W and putting it in LAN for access of others..the same customize ribbon is not available. Is there any solution to make this customize ribbon available for the others over the Network.

    Thanks in Advance

    • Yes. You need to export the customized ribbon from your own machine for other people to be able to import it again into their machines.

      Under Options/Customize Ribbon, export your customized ribbon by hitting the Import/Export button (Export all customizations). You need, then, to share this ribbon file to others and have each one import it the same way (Import customization file).

      The other thing that you may need to do is to save the macro as Add-in in their Excel App as explained in the blog.

  6. Arivarasan says:

    Hi Mohammed Barakat,

    Really super post ….. i have one question , is that import functionality will import the macro too when we try this in other machine ? expecting your reply…………..Thanks in advance

    • Hi Arivarasan,

      Unfortunately, you need to create the Add-in again on the other machine and attach it to the Excel application even if you import the Ribbon into the new machine.

      I personally see this step defeating the purpose of transferring the ribbon to other machines, but at least you can preserve the structure of the customized ribbon on all machines.

  7. Arivarasan says:

    Hi Mohammed Barakat ,

    Ok, i got your point and thanks for your quick response !! :)…. is there any other way to carry out the macro with ribbon other than creating codes for the ribbons ???

    thanks , Arivarasan

  8. Arivarasan says:

    Ok thank you very much :):)

  9. Steve Fabischek says:

    Hi Mohammed,

    The reason I stumbled upon your post was I was looking for a way to put my macro buttons into the ribbon and off of my spreadsheet due to the constant and necessary scrolling associated with my excel spreadsheet. Your instructions are perfect for what I needed it for, except that I only need it for my one excel spreadsheet template and NOT every excel sheet I open. Is there a way to create the custom Tab with macro commands for only 1 specific spreadsheet? Or will I have to simply Hide or not show the custom Tab in all subsequent spreadsheets.

    Thanks, Steve

    • Hi Steve,

      I don’t think there’s a way to assign a tab on the Ribbon associated with a single spreadsheet in a workbook. However, there are two workarounds for this to happen:

      First: assign the macro to a button, instead of a tab on the Ribbon, on the first row of the spreadsheet and freeze it so that the button is always visible when scrolling down the sheet.

      Second: using the VBA (coding) to trigger the ribbon visibility on/off when the required spreadsheet is loaded/unloaded.

      Hope this helps.

  10. Gert says:

    Great, works very fine! Thank you.

  11. tuffley says:

    is this possible to do in excel 2007?
    Need some help please- I have a user form and need to attach to a ribbon

  12. MDAL says:

    Mohammed. I have added seevral of my macros to my personal ribbon in Excel2010. Since the macros have rather abstract names I have not only edited the icon of the macro but also the name. So, e.g. the macro ‘Rounding_Format_Listing’ to ‘Finish Report’. Now several months later I want to edit the macro that is kicked of by the ribbon item ‘Finish Report’ but I do not recall anymore to which macro the item points. In Excel2003 the link was always visible. How would I see it in Excel2010?

    • Simply, you go to the Customize Ribbon menu item under File/Options. On the right side of the window find your Custom tab and group that hold your macros. You’ll see your customized macros listed. If you hover the mouse at any macro name, a tip will appear with the full path and name of your original macro. Besides, you can see a list of all available macros in your Excel if you filter the list on left to ‘Macros’.

      Hope this helps.

  13. vicsar says:

    Thanks. This is brilliant!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s