Get my FREE Weekly Newsletter
Create Your Own Personalized Quick Access Toolbar
by Francis Hayes (The Excel Addict)
Customizing
your Quick Access Toolbar (QAT) is one of the best ways to
make working with Excel faster and easier, regardless of your
skill level.
EVERYONE can do this...it's not just for Excel geeks.
A customized QAT allows you to quickly access those commands you use most often, with just one click. No more multiple clicks, searching around the Ribbon and trying to remember where those commands are located.
Customizing the Quick Access Toolbar is really easy to do.
Option 1: Right-click, Add to Quick Access Toolbar
Not all commands are available using this method but it's quick and easy whenever you decide that a specific command on the Ribbon would work better for you on the QAT.
1) Locate a command on the Ribbon that you would like to have on your QAT;
2) Right-click the command and select Add to Quick Access Toolbar.
The command is added to the end of the QAT commands, so if you would like to reposition it, you will need to use Option 2.
Option 2: Customize Quick Access Toolbar
You would use this option when...
1) Right-click the small dropdown on the end of the Quick Access Toolbar and choose More Commands...
(or right-click on the Quick Access Toolbar and choose Customize Quick Access Toolbar.) The Excel Options dialog opens with the Quick Access Toolbar tab selected on the left;
2) Click the 'Choose commands from' dropdown arrow. If you are adding multiple commands from a particular tab (e.g. Insert Tab, Data Tab, etc...), select that group from the list, otherwise, select All Commands to get an alphabetical listing of ALL available commands.
3) Next, scroll down the All Commands list in the Excel Options dialog and select the command you want to add. Then click the Add>> button.
Note that if you want to position the command somewhere other than the end of the QAT, use the up and down buttons on the right side of the dialog. Alternatively, before you add a command, select the existing QAT command (on the right of the dialog) that is above the position where you want to add the new command.
Repeat this step for each command you want to add.
4) Click OK to close the Excel Options dialog.
The Quick Access Toolbar can be positioned above or below the Ribbon. Personally, I have my QAT below the Ribbon because I find that it requires less mouse movement and eye movement to get from the worksheet to the QAT commands. Plus, as you'll see, the icons blow the Ribbon are in full color whereas, when placed above the Ribbon, they are boring and difficult to see.
1) Right-click the small dropdown on the end of the Quick Access Toolbar and choose More Commands... (or right click the Ribbon and select Customize Quick Access Toolbar);
2) In the 'Choose commands from' field, click the dropdown arrow and select Macros. You should see the macros for all open workbooks listed;
3) Scroll down and select your macro from the list and click the Add>> button;
4) Click OK.
When you add a macro to your QAT, a default icon is used. If you would like to use a different icon, click the Modify... button at the bottom of the dialog. A Modify Button dialog showing various icons appears. Choose any icon that you would like to use for your macro. At the same time you can change the name of the 'tool tip' that displays when you hover you mouse pointer over the icon on the QAT.
To see what macro a QAT command is linked to, open the Excel Options (Quick Access Toolbar) and hover your mouse pointer over the command on the right side of the dialog.
When you're finished, click OK to close the Excel Options dialog.
All those seconds saved really add up over time.
Once you create YOUR OWN PERSONAL Quick Access Toolbar with YOUR most frequently used commands just one click away, you will wonder how you ever managed without it.
Why don't you give it a try and let me know what you think.
EVERYONE can do this...it's not just for Excel geeks.
A customized QAT allows you to quickly access those commands you use most often, with just one click. No more multiple clicks, searching around the Ribbon and trying to remember where those commands are located.
Customizing the Quick Access Toolbar is really easy to do.
Adding commands to your QAT
There are two ways you can add commands to your QAT.Option 1: Right-click, Add to Quick Access Toolbar
Not all commands are available using this method but it's quick and easy whenever you decide that a specific command on the Ribbon would work better for you on the QAT.
1) Locate a command on the Ribbon that you would like to have on your QAT;
2) Right-click the command and select Add to Quick Access Toolbar.
The command is added to the end of the QAT commands, so if you would like to reposition it, you will need to use Option 2.
Option 2: Customize Quick Access Toolbar
You would use this option when...
- you want to make 'wholesale changes' to your QAT;
- you want to browse all commands available for the QAT;
- when you want to add macros to your QAT;
- you want to rearrange the commands on the QAT.
1) Right-click the small dropdown on the end of the Quick Access Toolbar and choose More Commands...
(or right-click on the Quick Access Toolbar and choose Customize Quick Access Toolbar.) The Excel Options dialog opens with the Quick Access Toolbar tab selected on the left;
2) Click the 'Choose commands from' dropdown arrow. If you are adding multiple commands from a particular tab (e.g. Insert Tab, Data Tab, etc...), select that group from the list, otherwise, select All Commands to get an alphabetical listing of ALL available commands.
If you're not
sure of the name of a command in the list, go back to
the Ribbon and hover your mouse pointer over the
command and you'll see the name pop up in a small
'tool tip'. |
3) Next, scroll down the All Commands list in the Excel Options dialog and select the command you want to add. Then click the Add>> button.
Note that if you want to position the command somewhere other than the end of the QAT, use the up and down buttons on the right side of the dialog. Alternatively, before you add a command, select the existing QAT command (on the right of the dialog) that is above the position where you want to add the new command.
Repeat this step for each command you want to add.
4) Click OK to close the Excel Options dialog.
Quick Access Toolbar Above or Below the Ribbon?
The Quick Access Toolbar can be positioned above or below the Ribbon. Personally, I have my QAT below the Ribbon because I find that it requires less mouse movement and eye movement to get from the worksheet to the QAT commands. Plus, as you'll see, the icons blow the Ribbon are in full color whereas, when placed above the Ribbon, they are boring and difficult to see.
Move Quick Access Toolbar Below the Ribbon
To move your QAT below the Ribbon, right-click the small dropdown on the end of the Quick Access Toolbar and choose Show Below the Ribbon (or right click the Ribbon and select 'Show Quick Access Toolbar Below the Ribbon').Removing Commands From Your QAT
Removing commands from your QAT is really easy. You simply right-click the command on your QAT and select Remove from Quick Access Toolbar.Adding a Macro to Your QAT
If you have a macro that you would like to run from a button on your QAT, here's how you can do that.1) Right-click the small dropdown on the end of the Quick Access Toolbar and choose More Commands... (or right click the Ribbon and select Customize Quick Access Toolbar);
2) In the 'Choose commands from' field, click the dropdown arrow and select Macros. You should see the macros for all open workbooks listed;
3) Scroll down and select your macro from the list and click the Add>> button;
4) Click OK.
Changing the Icon and Display Name for a QAT Macro
When you add a macro to your QAT, a default icon is used. If you would like to use a different icon, click the Modify... button at the bottom of the dialog. A Modify Button dialog showing various icons appears. Choose any icon that you would like to use for your macro. At the same time you can change the name of the 'tool tip' that displays when you hover you mouse pointer over the icon on the QAT.
To see what macro a QAT command is linked to, open the Excel Options (Quick Access Toolbar) and hover your mouse pointer over the command on the right side of the dialog.
When you're finished, click OK to close the Excel Options dialog.
Organize Your QAT With Separators
When you have related commands (e.g. File commands, Data commands, etc...) on your QAT, it's good practice to group them together. The first item in the commands list in the Excel Options dialog is <Separator> which you can use to insert a vertical bar between two commands on the QAT.. To add a separator, select <Separator> and click Add>>.Add QAT Commands Only for Specific Workbooks
Note that you can also choose to add commands to the QAT that are available only when a specific workbook is open. Make sure the workbooks is open and in the Excel options dialog on the Quick Access Toolbar tab you'll see a dropdown on the top right showing For alll workbooks (default'. Here you can choose to have the commands shown on the QAT only for the current workbook.Conclusion
The Quick Access Toolbar is a very important part of my strategy for increasing my productivity in Excel. Having one-click and always-in-view access to my most frequently used commands, including macros, allows me to get my tasks done much faster than if I had to poke around the Ribbon to access the same commands.All those seconds saved really add up over time.
Once you create YOUR OWN PERSONAL Quick Access Toolbar with YOUR most frequently used commands just one click away, you will wonder how you ever managed without it.
Why don't you give it a try and let me know what you think.
To get more tips like this one every week...
Sign up for my FREE twice-weekly Newsletter'Spreadsheet Tips From An Excel Addict'
'Excel in Seconds' & 'Excel in Minutes'
And I'll give you my 'Excel in Seconds' E-book as a BONUS!
(Download it immediately after you sign up)
Copyright Francis Hayes © All Rights Reserved
8 Lexington Place, Conception Bay South, NL Canada A1X 6A2
Phone 709-834-4630
This site is not affiliated with Microsoft Corporation.