Linear Regression in Microsoft Excel: Part I – How to Load the Excel Analysis Toolpak

Part I: Linear Regression in Microsoft Excel: How to Load the Excel Analysis Toolpak Add-In

Most readers will be familiar with loading an Excel add-in. For those who don’t, or might be rusty, here’s a quick precis of what you need to do to perform easily linear regressions in Excel. The rest of you can move directly to Part II.

The installation process for Microsoft Excel installs the Data Analysis Toolpak to your hard drive, but by default it will not be automatically loaded when Excel starts up. To ask Excel to start the Toolpak for us, we must first go to the File menu and click on Options. The dialog box that appears permits the management of many of Excel’s configuration settings. We, of course, are interested in Add-Ins.

Add-In Dialog
Excel Add-In Dialog

The user interface seems to suggest that we can click on Analysis Toolpak and click OK, but this is not the case. When we identify the add-in of interest in the list, we note at the right whether the Add-In is an Excel Add-In or a COM Addd-In. We see here that he Analkysis Toolpak is an Excel Add-In, which means that it was either developed within Excel itself and saved as a macro-only workbook, or, in this case, a compiled xll file. Once we know the type of add-in, we can click on the appropriate choise on the “Manage” drop-down and then click “Go…”.

Add-InType List
Types of Excel Add-Ins

The Add-Ins selector that appears will have a checkbox for every installed add-in and the checkboxes clearly indicate those that have been selected for loading into Excel. We check “Analysis Toolpak”.

Selecting an Excel Add-In
Selecting an Add-In

There is sometimes confusion about whether the Analysis ToolPak – VBA is required. The answer is no, it is not required if you wish to perform your analyses from the Excel graphical user interface. The Analysis ToolPak – VBA add-in provides an interface between the actual toolpak and the Visual Basic for Applications macro development environment. If you want to create macros to run linear regression (or other analyses) automatically then this second add-oin would be required.

Once you have clicked OK, you should be good-to-go. You can confirm a successful load of he Analysis ToolPak by clicking on the Data tab on the Excel ribbon. Towards the right-side of the data ribbon you should see a section titled “Analysis” containing a “Data Analysis” button. Clicking this button brings up the Analysis Tools list, from which you select the type of statistical tool you want.

Once you have chosen to load the Analysis ToolPak it will load every time you start Excel, not just for the workbook you had open when you first loaded it. This is fine, but keep an eye on your add-ins. After time you may have chosen many, Excel’s load time can begin to drag and Excel’s memory use can grow unecessarily. It is wise to sometimes prune the list of add-ins down to the set you use regularly.

Stay tuned for my next post where we will look at how to perform the linear regression analysis in Excel.


PS – To further your development, have a look at our complete curriculum of Microsoft Excel courses including 3 new 1-day Boot Camp Courses.

Type to search

Do you mean "" ?

Sorry, no results were found for your query.

Please check your spelling and try your search again.