Tips for Customizing Excel Ribbon
August 30, 2018 - by Bill Jelen
I was doing a webinar for Excel4Apps on Modern Pivot Tables and I mentioned how I hated the default Compact Layout. Ronnie Wilson was watching the webinar and sent a note through the Q&A panel that you can add the Show in Tabular Form icon in the Quick Access Toolbar.
I told Ronnie to send me an e-mail and I would mail him one of the Excel Guru patches that I hand out in my live Power Excel seminars whenever I learn something new. Ronnie sent me an e-mail with two more great tips about customing the Ribbon in Excel.
Ronnie's three tips for customizing the Ribbon
- Use the white background for maximum visibility
- Never put more than five icons in one group. This will ensure that 100% of the icons stay large. If you would put 25 icons in one group, some icons will be large and others will be small. You don't have control over which is large and which is small. By using Ronnie's technique of five or less icons in each group, they will always stay large.
- Move your new tab above the Home tab and it will become the tab visible most of the time.
Here is part of Ronnie's custom tab.
Here are the steps to creating a new Ribbon tab.
Right-click the Ribbon and choose Customize the Ribbon.
Look in the right box. Find and select the Home tab. Beneath the box, choose New Tab.
Excel adds a New Tab and a New Group just below the Home tab. By default, New Group is selected.
Select New Tab and press the Up Arrow to the right of the list box to move New Tab above the Home tab.
Below the list box is a Rename button. With New Tab selected, click the Rename button. You can give your tab a name such as your name. I've used MrExcel.
Click on New Group and rename that group. My first group in the MrExcel ribbon is going to be some pivot table features, so I called my group Pivot.
Use the left list box to find your desired command. I am using the Excel 2003 Pivot Table Wizard, which I found in the All Commands category. Select the icon in the left box and then click the Add>> button in the center of the dialog.
If the command that you want to add to your custom ribbon tab is already in another tab, it might be easier to find it by looking through Main Tabs or Tool Tabs.
Keep adding more icons, then more groups and icons. Here is the first half of my custom MrExcel tab.
Never put more than five icons in a group. I had seven icons that should be in my Data group, but I made a Data group and a Data two group.
If you want to share your ribbon tab with others, you can export a Excel Customizations.exportedUI file. Use the Import/Export button below the right list box.
Your co-workers would use the same button to import the customizations from the file.
Download Excel File
To download the excel file: tips-for-customizing-excel-ribbon.xlsx
Thanks to Ronnie for those great tips about customizing the ribbon.
Excel Thought Of the Day
I've asked my Excel Master friends for their advice about Excel. Today's thought to ponder:
"Next time M code exasperates you, remember this: M code was written by your ex. And your ex hates you."
Title Photo: Jon Tyson on Unsplash