Microsoft Excel — Productivity with Add-In’s and the Quick Access Toolbar
Installing an Add-In and Adding Items to Your Quick Access Toolbar (“QAT”)
**Updated 2/27/17 to fix link — see below**
Excel users likely use add-in’s every day and many don’t realize it. Add-in’s typically add capabilities to Excel via an “add-in” file that enables the user to 1) save time on frequent Excel tasks, or 2) allow you to do things you cannot do without an add-in.
“An Excel Add-In is a file (usually with an .xlam or .xll extension) that Excel can load when it starts up. The file contains code (VBA in the case of an .xlam Add-In) that adds additional functionality to Excel, usually in the form of new functions.” — http://www.fontstuff.com/vba/vbatut03.htm
In this post, I want to remove some of the mystery around Excel add-ins. Using a custom add-in that I have developed — for my own use, but also with clients — I will walk through installing the add-in and including add-in items on your Quick Access Toolbar. This makes it very easy to access the add-in functions and immediately improves your productivity!
A list of the functions / capabilities this add-in provides is included at the bottom of this post. If you would like a copy of this add-in, see the “Getting Add-In” section at end of post.
Installing an Add-In
“Installing” the add-in simply means
- obtain the file (download, create, etc.),
- put the file where Excel “sees” it, and
- tell Excel to activate it when you open the program.
Here we go…
Obtain the file
In this case, the file is stored in Box.com cloud storage account. Click “Download” (ignore file name in images — it has changed!)
“Save” the file to your computer (in this case, I chose the Desktop)
Put the File where Excel “sees” it
Next, move the file to the File Directory where Excel needs it (this is how it is “seen” by Excel)
The path refers to a specific user setup, so the name [DRTomoff] should be YOUR specific user name.
Windows 7 Operating System
C:\Users\DRTomoff\AppData\Roaming\Microsoft\AddIns
Tell Excel to activate it when you open the program
Once copied into the proper folder, you open up Excel and “activate” the add-in. The steps are outlined below:
Click on the Excel “Office” button and select “Excel Options”
From “Excel Options”, click on “Add-Ins”, then select “Excel Add-Ins” under Manage, and click “Go”.
From the Add-Ins Available list, make sure that the “InvenioMakeItSimpleAddIn” (remember, image name below has changed) box is checked. Then click “OK” to close the dialog box.
At this point, you will notice nothing different about your Excel worksheet, but the macros included within the Add-In are now available to you whenever you are working in Excel.
Including Add-In Items on the Quick Access Toolbar
Now that Excel has loaded the add-in, we need to make it easy to invoke the shortcuts / commands that we want. As shown in the image below, this can be accomplished through a custom Ribbon or by adding the items to your Quick Access Toolbar.
I will walk through adding them to the Quick Access Toolbar
Default Quick Access Toolbar
By default, the Quick Access Toolbar will have very few items located on it. In the image below, it is located below the menu ribbon. You may see it located at the above the ribbon also.
Yours may look different if you have already customized it for your personal use.
Select Macros to Add to the Quick Access Toolbar
Now to add the macros you want (can be a few or all…) from the “InvenioMakeItEasyAddin” Add-In:
Click on the “Office” button and select “Excel Options” (same as 1. above). Then select “Customize”. From the “Choose commands from:” dropdown, choose “Macros”.
From the list, choose each of the items and “Add>>” them to the Quick Access Toolbar (for a list of all macro functions, see the image at the end of this post).
When you are completed, the right side of your screen will appear as follows:
Rearrange Commands and Add Icons
Every user has different preferences for order and what image they want to assign to each command. Here is how you do it.
To rearrange items, select the item and move it using the directional arrows on the right.
A completed, recommended sequence, is shown below. Like items are grouped together and arranged based on my anticipated frequency of use (again, arrange however you want).
To assign icons, select the item and click “Modify…” at the bottom of the dialog box.
The following dialog box will appear:
Select each macro item and modify the image to your preference!
A completed view is shown below.
The final Quick Access Toolbar appears as follows:
If you forget what an item on the Quick Access Toolbar represents, simply hover your mouse pointer over it and the name will appear.
Add-In List of Functions / Getting Add-In
These are the available shortcuts / commands in the add-in I used.
If you would like these instructions in a PDF format, including a copy of the add-in for your use in Excel, you can request it here (and a download link will be provided).
Thanks for reading — I hope you found this useful!
About Don
Don Tomoff is a “recovering CPA”, who is passionate about helping professionals and organizations keep up and adapt to the changing business world that we operate in.
One lesson learned over the years is that all of us, regardless of organization type or size, struggle with similar issues — primarily information management and presentation, and effective use of our time. Let’s change that…one person at a time!
Connect with Don!
LinkedIn, Flipboard, Twitter, Snapchat
