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...
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.
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).
Step 4
Select the new tab and then you can either right click and choose Rename, or click on the Rename button.
Enter a new name for the tab and click OK. I'm calling mine PGT.
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.
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.
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.
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.
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.
Step 10
Once you have renamed all your macros you should have something like this.
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.
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.
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?
Philip Treacy
Hi Stefano,
Yes it’s possible to do this. You’re the 2nd person today to ask about custom Ribbon UI!
Microsoft – Customize the ribbon
I’ll have to write a post about it.
Regards
Phil
Bill Staggs
Mynda & Philip,
Your Blogs are always so easy to follow.
Thanks for your generosity
Philip Treacy
You’re welcome Bill, glad to help.
Regards
Phil
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.
Philip Treacy
Good point Bob
MF
Do you really have the macros that can “Fix everything” or “Increase my salary”??? heeheee… Pls share if it exists. 🙂
Philip Treacy
🙂
I’ll email them right over
Arvind
How we can add location in our excel file Ribbon.
Philip Treacy
Hi Arvind,
I’m not sure what you mean by adding a location – can you explain a bit more please?
Regards
Phil
Joyce
Really wish we had Excel 2010 at work. At least I can use this tip at home. Thank you!
Philip Treacy
You’re welcome
Jef
Great. Works fine. I had done a lot of customization to my Excel menu. Thanks for sharing.
Philip Treacy
Thanks Jef.