This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Customizing the Ribbon
Problem: I want to customize the ribbon.
Strategy: Ribbon customization is not in Excel 2007. The customizations in Excel 2010 are weak compared with the customization capabilities in Excel 2003. However, they are better than the inability to customize in Excel 2007.
You might feel like the Pivot Table command belongs on the Data tab rather than on the Insert tab. You can add a new group to the Data tab to hold the pivot table icons.
First, look at the ribbon and decide where you want the new group to appear. Perhaps a good location would be between the Sort & Filter group and the Data Tools group.
Figure 11 Right-click the ribbon to access this menu.
Figure 12 Choose where the new group should go.
The Customize dialog contains two large list boxes. You will first be working with the list box on the right side of the screen.
Expand the plus sign next to the Data entry to see the groups on the Data tab. If you want a new group to appear after the Sort & Filter group, click Sort & Filter, and then click the New Group button below the list box.
Excel adds a new group with the name of New Group (Custom). Click the Rename button below the list box.
Figure 13 Choose to rename the group.
Type a new name in the Rename dialog. Also, choose an icon. This icon will appear only when the Excel window gets small enough to force the group into a drop-down, as shown later in Figure 17.
Two PivotChart icons are available. Hover over each icon to see that the first one is the PivotChart icon available on the PivotTable Tools Options tab. You will also see that the second icon is the one on the Insert tab. The first PivotChart icon will be grayed out unless you are in a pivot table. The second PivotChart icon is the one that is used to create a new pivot chart from a data set.
This figure shows the resulting group on the Data tab.
Figure 16 The custom group is added to the ribbon.
If you are wondering why you had to choose an icon back in Figure 14, it is for people who have the Excel window resized to a narrower width. If you make your Excel window narrower, the custom group will eventually get squished down to a single drop-down. Your icon will appear on that drop-down, as shown here.
Note back in Figure 10 that the Sort icon appears as a large icon with a caption and that the AZ and ZA icons appear as small icons without a caption. How can you specify that the pivot table icon should be large and the pivot chart and wizard icons should be small? You can’t. At least not with the Excel interface.
If you want to start writing some XML and VBA, you can gain control over the size and images used in the ribbon. For an excellent book on this daunting task, look for RibbonX: Customizing the Office 2007 Ribbon by Robert Martin, Ken Puls and Teresa Hennig.
I find that I spend most of my time on either the Home or the Data tab. If I could combine the left side of the Home tab with the right side of the Data tab, plus pivot tables, I would probably be able to spend all my time on one tab.
This figure shows a new MrExcel tab that reuses groups from other ribbon tabs to build a new tab.
4. Use the Up and Down buttons at the right side of the dialog to move the new tab into the proper location.
5. From the left drop-down, choose Main Tabs.
6. In the left drop-down, expand an existing tab and find an existing group that you want to add to your new tab. Click that group and click Add.
7. Repeat step 6 to add additional groups.
8. You can reuse a custom group that you created previously. In the left drop-down, choose Custom Tabs and Groups. You can move the Pivot Table (Custom) tab created earlier in this chapter onto your new ribbon tab.