In the latest version of Excel it has become your responsibility (if you choose to) to create your own toolbar, and in our basic Excel series we show you how.
The Quick Access Toolbar at the top of your screen begins with just the Save, Undo and Redo buttons. If you require fast access to any other Excel commands you are able to add them yourself. You can add almost any functionality to the toolbar including those not found on the Ribbon.
As part of our basic Excel series, this post explores 7 incredible additions to the toolbar that are sure to improve the way you work.
1. The PivotTable and PivotChart Wizard
This wizard was how you created PivotTables in previous versions but then it was removed from Excel 2007. Without it you can create PivotTables quicker, but I am often asked if it is still around.
It can be found in the Commands Not in the Ribbon section. A fantastic use for it is for creating PivotTables from multiple consolidation ranges.
2. Paste Values and Number Formatting
How could you live without Paste Special in Excel? It would be difficult. We use it to copy and paste data validation rules, cell formatting only, to transpose data, copy column widths and so on.
Probably its most common use though is to copy and paste values and number formatting only. This removes the formulas for a finished spreadsheet. This can then be sent to a client without the formulas, or maybe continue being used but much quicker without the formulas slowing it down.
This button can also be found in the Commands Not in the Ribbon section.
3. Go To Special
The Go To Special button provides quick access to selecting cells that meet certain requirements. It can be found on the Home tab section. Two great uses of this feature are to;
- Select visible cells only to exclude hidden columns when copying data.
- Select blank cells within a range allowing you to delete blank rows or to enter data into empty cells.
4. Custom Views List
Custom Views are useful for saving different filter and print settings. Let’s say that you print a variety of reports from a spreadsheet. These different filter and print settings can be saved as custom views and then displayed and removed in the click of a button. Certainly beats unhiding column, removing filters and changing print settings like scaling manually each time.
Applying a custom view still takes three clicks though as standard, until Excel 2010 saw the introduction of the custom views list that can be added to the toolbar.
The list makes selecting different custom views easy. It is found under the All Commands section.
How to use Custom Views
5. Select Current Region
Another one from the Commands Not in the Ribbon section. This awesome button will select the entire range of cells around the cell(s) you currently have selected. This provides a one click method of selecting a large table of data.
6. Refresh All Data Source Connections
If you have many connections to external data sources and PivotTables this button could save you some time.
It will refresh all PivotTable and other data source connections. All tables refreshed in two clicks. You have to love that.
It also uses the Ctrl + Alt + F5 keyboard shortcut, but we are here to talk toolbar buttons so it can be found on the Data tab section.
Cannot find a button that does what you require? Want to create your own functionality?
You can record or write a macro and add that to the toolbar.