Excel Ribbon Archives - Excel Campus

Archive

Category Archives for "Excel Ribbon"
11

How to Install an Excel Add-in – Guide

What’s an Excel Add-in File?

An add-in file is an Excel file that contains macros (code) and has the extension “.xla” or “.xlam” (2007 and later). When opened, the worksheets in the file are hidden and the macros are typically accessed through buttons that are added to the toolbar or ribbon. Once installed, an add-in will automatically open every time Excel is opened.

Installation

Here is a video that walks through how to install the add-in.

Please see my video below on how to add the add-in’s folder location to the Trusted Locations list.  This is now a critical step due to a July 2016 Office Security update.

Installing the add-in is pretty easy, and should only take a few minutes.

  1. Save the add-in file to your computer.
    – Pick any folder on your hard drive that is easy for you to find.
    – My suggestion is to create a folder named “Excel Campus” and place it in your Documents folder.
  2. Open Excel and go to the Options Menu.
    – This varies depending on your version of excel.
    – 2007 – Office Button > Excel Options
    – 2010 & 2013 – File > OptionsExcel Options Menus 2007 2010 2013 2016
  3. Click the “Add-Ins” option on the left-side menu, and then click the “Go…” button.
    – You may have to wait a few seconds for the window to appear.Excel Options Add-Ins Menu
  4. Click the “Browse…” button on the Add-Ins Menu.Browse for Excel Add-ins
  5. Locate the add-in file you saved in Step 1, and click OK.Select Excel Add-in File
  6. The Add-in will appear in the list and should be enabled, click OK.Enable Excel Add-in
  7. Installation Complete (almost) – The add-in is now installed.  However, there is now an additional step due to an Office Security Update in July 2016. Please see the section below for instructions.

Trust the File Location

Important Note: There is one additional step to this installation due to an Office Security Update released in July 2016.  Here is a video that explains it in more detail.

The folder that the add-in file is saved in needs to be added as a Trusted Location in Excel.  The instructions on how to trust the folder location are below.  I also have an article and video that describes this issue in more detail.

  1. Open the Excel Options menu
    1. File > Options
      Excel Options Menus 2007 2010 2013 2016
  2. Open the Trust Center menu and Add a new location
    1. 1. Trust Center > 2. Trust Center Settings… > 3. Trusted Locations > 4. Add new location.
      Add New Trusted Location Folder in Excel
  3. Browse for the folder that you saved the add-in file in.
    Add Trusted Location Folder
  4. Press OK, the folder should now appear in the Trusted Locations list.
    Trusted Location Added for Documents Folder
  5. Press OK on the Trust Center and Excel Options menu to close the menus.
  6. The add-in is now in a trusted location and the XL Campus tab will appear every time you open Excel.

Unblock the File

Some users still have issues with the add-in’s ribbon disappearing after trusting the folder location.  If this happens to you, you will need to Unblock the file by changing a file property.

  1. Locate the Add-in file (.xla, .xlam) in Windows Explorer.
  2. Right-click the file and select Properties.
  3. At the bottom of the General tab you should see a Security section.  Check the box that says Unblock.
    Security Unblock Property for Excel Add-in - Ribbon Disappears
  4. Press the OK button.
  5. Close Excel completely and re-open it.  The add-in should now load and any custom ribbons will appear.

You will only need to do this unblock one time.  However, if you download an updated version of the file then you will have to repeat the steps above to unblock it.

Hopefully these additional security steps will be fixed in a future update to Office.  If you completed all the steps above then you should see the add-ins ribbon tab load every time you open Excel.

Download PDF

Installing An Excel Add-in - Guide (672.2 KB)

Add-ins

Paste Buddy – Create custom keyboard shortcuts for the paste special commands like paste values.

Formatting Shortcuts Add-in – Keyboard shortcuts to change font & fill color and cell styles.

More Add-ins

9

Excel Quick Access Toolbar – Keyboard Shortcuts

The Quick Access Toolbar (QAT) in Excel 2007 is extremely useful for creating keyboard shortcuts for items that don’t have a defined keyboard shortcut.  For example, there is no defined keyboard shortcut to Paste Values but you can easily set it up on the QAT.

The QAT is accessed through the keyboard by pressing the Alt key.  Press and release the Alt key and you will see numbers appear next to each icon on the QAT.

Alt Number Key for Keyboard Shortcuts for the Quick Access Toolbar

 

So Alt + [the icon number] is the keyboard shortcut for each item.  In this example, the keyboard shortcut for paste values is Alt+1.  The keyboard shortcut for the format painter is Alt+2.

There are two ways to use the keyboard shortcut.

  • Press and release Alt to view the shortcut numbers next to each icon.  Then press the number of the QAT item you want to use.
  • Better Option: Press and hold Alt then press the number of the QAT item.  This is much faster once you have your QAT shortcut numbers memorized.  You will notice that the numbers don’t appear when you press and hold Alt, so you have to memorize them.

I tend to move the icons around based on the task I’m working on in Excel.  If I’m going to be using the format painter a lot, I will move it to the “1” position (farthest left) while I’m using it.  This is mainly because it is easier to Press Alt+1, Alt+2, and Alt+3 on the keyboard by placing your right thumb on Alt and right index finger on the number.  The other numbers can be a bit of a stretch, depending on your keyboard.

See my post on how to setup the QAT for more details.

5

Setup the Excel Quick Access Toolbar (QAT)

The Quick Access Toolbar (QAT) in Excel 2007 is a great option for shortcuts to your most used commands.  It’s faster to click on an icon in the QAT (one click) versus clicking on the ribbon tab and then the command (two clicks + mouse navigation).  And you can use keyboard shortcuts for the QAT to make things even easier.

The Setup

Setting up the QAT is simple and consists of two steps:  Adding commands and arranging the icons.

The easiest way to add commands to the toolbar is to:

  1. Navigate to the command you want to add in the ribbon.
  2. Right click on the icon and select Add to Quick Access Toolbar from the drop down menu.

QAT Add Item

The command icon will be added to the end of the QAT.

To arrange the icons:

  1. Right click anywhere on the QAT and select Customize the Quick Access Toolbar… from the drop down menu.Customize QAT drop down
  2. The Excel Options menu opens and you can move your icons to the left or right by selecting the icon in the right box and pressing the up or down arrows.
QAT Options

Click Image to Zoom

The order of the icons becomes important when you are using keyboard shortcuts to access the commands.  And I highly recommend this as a much faster way to execute commands in Excel, versus using the mouse.  You can also use the Options window (above) to add and remove commands from the toolbar.

So what icons should I add to the Quick Access Toolbar?

This will depend on what you use Excel for and which commands you use most often.  If you are doing lots of data entry and manipulation you might want to have the “Paste Values” and “Format Painter” commands at the top of your list.  Reporting and analyzing, you may want some charting and page layout commands.  The key is to have the commands you use most often located closest to the left because the keyboard shortcuts are easier to press with your thumb and index finger.

Move the QAT

The toolbar can be place above or below the ribbon by clicking the down arrow to the right of the QAT and selecting Show Below the Ribbon or Show Above the Ribbon.

Move QAT

I prefer to have it above because it reduces the total vertical height of the top toolbars, which gives you more spreadsheet real estate.  But you may find that you have so many icons that it fits better below the ribbon.  If you are a heavy mouse user (vs. keyboard shortcuts) then it is also easier to navigate to the QAT if it is below the ribbon.

Checkout my recommendations on commonly used commands for the Quick Access Toolbar.

[print_link]