MrExcel.com - Your One Stop for Excel Tips & Solutions

Tips for Customizing Excel Ribbon

August 30, 2018 - by

Tips for Customizing Excel Ribbon

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.

None of the icons get small by following Ronnie'e rules
None of the icons get small by following Ronnie'e rules

Here are the steps to creating a new Ribbon tab.

  • Right-click the Ribbon and choose Customize the Ribbon.

    Customize the Ribbon
    Customize the Ribbon

  • Look in the right box. Find and select the Home tab. Beneath the box, choose New Tab.

    Add a new tab while the Home tab is selected.
    Add a new tab while the Home tab is selected.

  • Excel adds a New Tab and a New Group just below the Home tab. By default, New Group is selected.

    The New Tab and New Group appear below Home
    The New Tab and New Group appear below Home

  • Select New Tab and press the Up Arrow to the right of the list box to move New Tab above the Home tab.

    Move the New Tab above Home
    Move the New Tab above Home

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

    Name the tab
    Name the tab

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

    Find the command on the left and add it to the right
    Find the command on the left and add it to the right

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

    Adding the Tabular Layout to the custom tab
    Adding the Tabular Layout to the custom tab

  • Keep adding more icons, then more groups and icons. Here is the first half of my custom MrExcel tab.

    Eight icons in two groups
    Eight icons in two groups

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

    Break a group into two groups if you have more than two icons.
    Break a group into two groups if you have more than five icons.

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

    Export your customizations
    Export your customizations

  • Your co-workers would use the same button to import the customizations from the file.

Watch Video

Video Transcript

Learn Excel from MrExcel Podcast, Episode 2240: Tips for Customizing the Ribbon.

If you like what you see in this video, please subscribe and ring that bell.

Hey, welcome back to MrExcel netcast, I'm Bill Jelen. Last week, I was doing a webinar about pivot tables and, you know, one of the things I always complain about in pivot tables is how I hate this compact layout, and I always switch to tabular form. Of course if you have office 365 you can set your pivot table to False now, and make that be the default. But in the chat panel, Ronnie W, from Texas, said, "Hey, you can just add this to the quick access toolbar." And, sure enough, right-click, I had a quick access toolbar like that. I never realized that was one you could add to the quick access toolbar, but now with one click-- BAM! It's back in a tabular form, which is the form that I like.

And so, you know, I said, "Hey, Ronnie, so let me know and I'll send you an Excel Guru patch for that idea. And then Ronnie sends me two new ideas, even better than the one he just gave me, right? He says, "Yeah, you know the best thing to do in Excel is to customize the ribbon," and sent me a copy of Ronnie's ribbon setup and gave me two tips that I had never heard before.

Tip Number 1: I'm always frustrated when I customize the ribbon because I can't seem to have control over whether the icon is large or small. Alright, see it? You know, you get these small icons and, unless you go into the ribbon XML, you're not going to have a chance to control this, and Ronnie broke the code and said, "Look, always make sure that you just use lots of groups, and each group has five icons or less, and that'll keep all the icons really large."

So, great tip there, and then Ronnie says, "Make sure that whatever tab you create, you put that tab above the Home tab and that it'll be the default tab-- would be the tab that you have all the time." Alright, so let's try this. We're going to right-click, Customize the ribbon. Okay, so I'm sitting around the Home tab, I click New Tab, and I get two things: both New Tab and New Group. Alright, so you want to go to New Tab and rename that-- I'll call it the MrExcel tab-- click OK, and then with that selected, move it up so it is before the Home tab. Alright, then you have your New Group here, and we're actually going to have a lot of groups. So this first group, What are the most important things to me? It has to be related to pivot tables. So I'll rename that to be Pivot, click OK. And then we have to find commands to add to that. Currently, Pivot is on the Insert tab, so if I Insert, Tables, choose PivotTable, and then Add that. And then what else would I like? if I find, go to Design and then Report Layout, Show in Tabular Form, click Add. Alright, so now I've built a group with two items there and then maybe I want a new group. That is, my formatting group. So I'll rename, click OK, and back to the All Tabs, Home, Font, Font size, I'm always changing that so I'll add Font Size, Bold, all of that, underlined-- that's alright. So, there you go. So we have two groups-- in this case, Group one has two icons, Group 2 has three icons. Take some time, you know, 10 or 15 minutes, to create the perfect set of icons for you. If you need to move it to another machine you can export all customizations and then import on the other machine.

Let's see how it looks-- click OK. Alright, so we have our new tab-- MrExcel, PivotTable, Show in Tabular Form, the Font Size. Alright and just keep going straight across. Alright so, now, let me close Excel and reopen Excel and see if MrExcel becomes the default. Yeah, there we are-- beautiful. So as soon as I open Excel, it is the default-- MrExcel. And if I need to go find something else, I could go to the Home tab. Yeah. Show the Start screen when this application starts-- so, File, Options, General-- I have this turned off so that way I just go straight to a blank workbook instead of starting at that new Home screen that we talked about yesterday.

Alright, hey check out my new book, "Microsoft Excel 2019 Inside Out." Click that "I" on the top right hand corner for more information about that.

Today, wrap-up: Build a new tab in the ribbon with your favorite commands, thanks to Ronnie W. for this idea-- lots of groups with less than or equal to 5 icons each, make sure that the icons stay nice and large like this and then move your new tab above the Home tab to make sure that it is the default. To download the workbook from today's video is the URL in the YouTube description.

I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.

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