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
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:
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:
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:
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:
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.
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.
The QAT looks like this when shown below the ribbon.
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!
- Excel Tips & Tricks #479 – Advanced Filter redux
- Excel Tips & Tricks #478 - Introduction to Power Automate
- Excel Tips & Tricks #477 - Introduction to creating map charts in Excel
- Excel Tips & Tricks #476 - Cross join in Excel using a formula
- Excel Tips & Tricks #475 - Using ‘Show Changes’: keeping track of cell edits and the power of cloud storage
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.