ICAEW.com works better with JavaScript enabled.
Exclusive

Excel tips & tricks

Excel Tips & Tricks #473 – Refreshing customising the ribbon

Author: Jonelle Johnson-Turner

Published: 30 May 2023

Exclusive content
Access to our exclusive resources is for specific groups of students, users, subscribers and members.

Hello all and welcome back to Excel Tips and Tricks! This week, we have a Creator level post in which we're dipping our toes back into the pool of customisation options available through Excel.

This was last covered back in Tip #312.

Customising your Ribbon

Screenshot of the Ribbon in Excel

The Ribbon replaced dropdown menus in Excel 2007, and since then has been a fixture of the Office suite. The default set of menus can be added to - you will see in the image above that I have the 'Developer' tab in my ribbon which is usually disabled by default, as well as a couple of others - and you can also decide what things to include on each.

This is all controlled from the Options menu:

Screenshot of the 'Customise Ribbon' dialogue box in Excel

On the left you can see the various buttons and icons you can add into the Ribbon menus - and these include a few options that don't appear anywhere on the default Ribbon! If you want to easily find items to build your custom Ribbon, the top left dropdown menu has several options to help you find something useful - including "Commands Not in the Ribbon" for finding those particularly hidden gems.

On the right are the current tabs that you have. By default, this is just the always-visible tabs, but with the right-hand dropdown you can also view and customise contextual tabs, like the PivotTable tools tab, that only display under certain conditions. If we open one of these up, we can see the structure beneath:

Screenshot showing how to create groups in the 'Customise Ribbon' dialogue box in Excel

These sub-layers are 'Groups'; you can see at the bottom where you can create new groups. These groups collect items with a similar purpose - such as charts in this example. While the default tabs can't be changed, when creating your own tabs or adding items to the existing ones you can set up your own groups and pick something that works for you.

As well as that, you can use the up/down arrows on the right to reorder any custom items. New groups and tabs start with default names, but you can right click on them to rename them.

Finally, the 'Reset' and 'Import/Export' buttons at the bottom do what you'd expect - they allow you to either rest to the default tabs, or transfer Ribbon customisations between users by exporting from one machine and importing to an another.

The Quick Access Toolbar

In case you didn't know, the Quick Access Toolbar is the name for the small selection of very common commands that appears right at the top left of the Excel window:

Screenshot of the default Quick Access Toolbar in Excel

The default QAT just has autosave, save, undo, and redo. But you can amend this - either by adding some other popular commands from the small dropdown menu you see at the right there, or from the Options menu again:

Screenshot of the drop down menu in the Quick Access Toolbar in Excel
Screenshot of the 'Customise Quick Access Toolbar' dialogue box in Excel

The menu and operation here are almost identical to the Ribbon customisation options above.

When combined with keyboard shortcuts changing the Ribbon and QAT can be even more worthwhile! With the default settings, Alt 2 works as a shortcut for the Save command, however many users are already familiar with using Ctrl S, so swapping it around for another command you use can be beneficial.

More on useful keyboard shortcuts for Excel has been covered in Tip #464

Screenshot of useful keyboard shortcuts in Excel

By default, the QAT sits above the ribbon as shown above. However, it is possible to move this below the ribbon which may make it easier to view and use.

Screenshot showing how to move the Quick Access Toolbar in Excel

The QAT looks like this when shown below the ribbon.

Screenshot showing the Quick Access Toolbar below the Ribbon in Excel

All in all, customising these options may not be for you - but if you have a particular set of tools or options you find yourself digging for frequently, then consider making yourself a new Ribbon tab or adding them to the Quick Access Toolbar!

Archive and Knowledge Base

This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.

}
}