Using Add-Ins

 

Introduction.  Some of the most widely used aspects of Excel are not automatically implemented in the typical setup.  For example, one of the classic instances of this occurs in teaching quantitatively oriented courses.  The Data Analysis tools must be added in before use.  This is an example of what is called an Add-In.  There are many others that provide everything from sophisticated forecasting to special functions.

As a developer there are many reasons why it can be advantageous to develop add-ins.

  • Restrict access to specially developed code - this can help both by protecting it from illegal copying and inadvertent modification.
  • It can help simplify access for users.  Add-Ins appear in the Add-Ins dialog with a representative name and description.
  • They can be automatically loaded when Excel is loaded.
  • Add-Ins tend to execute somewhat faster than normal files.

There are many other reasons, but these should give you some basic motivation for this webpage.  Typically, workbooks that benefit the most from being converted to Add-Ins are those that are mostly constituted by general purpose sub/function/macro procedures.

Now we will walk you through adding in the Analysis ToolPak, which will add a new item to the Utilities menu called Data Analysis.

Using an Add-In.  You should work through the following steps to add in the Analysis ToolPak.

  1. Click on the Tools menu.
  2. Select the Add-Ins item.
  3. Look at the dialogue form and click on the Analysis ToolPak.

You should see a dialogue form like the following.  Make sure to scroll through it some to see some of the other add-ins that are available.

 

 

The following table describes a few of the add-ins that are available without purchasing anything from a third party source.

 

Add-In Name

Description
Access Links This allows you to use Microsoft Access tables and forms in Excel.
Analysis ToolPak Provides functions and interfaces for financial and statistical data analysis.
Analysis ToolPak VBA Analysis functions that can be used in VisualBasic.
Euro Currency Tools Conversion and formatting for the Euro currency.
LookUp Wizard Helps create formulas to find data in lists/arrays.
MS Query Works with Microsoft Query in macros.
ODBC Open DataBase Connectivity standard allows fairly general accessibility of external data sources.
Report Manager Helps create reports by combining spreadsheet views with data scenarios.
Solver A fairly elaborate tool for optimization and equation solving.

 

  1. Now click on OK.
  2. Click on the Tools menu.
  3. Select the Data Analysis item.

The following dialogue form should appear.  You should scroll through it to see the large variety of tools that are available.  Since this is not a course in quantitative methods or statistics we are not going to make much use of these, but this gives you some idea of the power available through add-ins.