Is the Excel Data Analysis Add-in Even There? - dummies

Is the Excel Data Analysis Add-in Even There?

By Conrad Carlberg

Add-ins are not at the top of the food chain at Microsoft. The tasks that add-ins perform may be important enough to automate, but they’re not regarded as important enough to become a full-fledged part of the Excel application. (If add-ins did enjoy that degree of positive regard, there’d be a Data Analysis option on the Ribbon’s Data tab right out of the box, just like Sort or Filter.)

Developers other than Microsoft do offer lots of add-ins. A Google search in 2016 for the words Excel and add-in returned nearly 30 million hits; that’s compared to half a million hits in 2005. Lots of these pages offer add-ins for sale. If you know how to code using Visual Basic for Applications and have a copy of Excel, you can create an add-in and post it for sale on a website. If you’re looking for some sort of specialized capability that Excel doesn’t offer, but that it could, check out the net — but be prepared to get something less than what you’re looking for.

First, you have to get the add-in onto your computer. Then you have to get the add-in into Excel. The following sections describe how to do that for the Data Analysis add-in.

Do a quick check, first, by making sure that the Data Analysis add-in isn’t installed already. Start Excel and go to the Ribbon’s Data tab. Look in the Analyze group for an icon labeled Data Analysis. If you see it, you’re probably good to go. (With add-ins, you always run a certain risk that someone has installed something that isn’t the Data Analysis add-in at all, but that nevertheless puts the Data Analysis item in the Analyze group. Don’t worry about it. If people wanted to put ransomware on your computer, they’d choose a better way.)

If you don’t see Data Analysis in the Analyze group, you have a little added work to do. The add-in may still be on your computer, but no one told Excel. Take these steps:

  1. In Excel, click the File tab.
  2. Choose Options from the navbar at the left of the Excel window.
  3. Choose Add-Ins from the navbar at the left of the Excel Options window. Click OK.
  4. Make sure that the Manage drop-down near the bottom of the Excel Options window contains Excel Add-ins. Click Go.
  5. The Add-ins dialog box appears as in the following figure. Make sure that the check box next to Analysis ToolPak (sic) is checked, and click OK.
There’s an installation problem if you don’t see Analysis ToolPak in the list box.

If the list box shown doesn’t show an Analysis ToolPak item, your best bet is to get in touch with whoever installed Excel on your computer and complain bitterly.

As long as you’re here at the Add-ins dialog box, you might as well select the Solver check box if it isn’t already. Excel’s Solver is a powerful utility that’s absolutely indispensable when it comes to forecasting with the exponential smoothing methods.

If you think you may want to use some of the special functions in the Data Analysis add-in in your own VBA code, select both the Analysis ToolPak and the Analysis ToolPak – VBA check boxes. Otherwise, select just the Analysis ToolPak check box. This is one of the few locations in Excel that, in Excel 2016, still make reference to the Analysis ToolPak with that name.