Using the Analysis Toolpak

Excel comes bundled with an add-in called the Analysis Toolpak (or ATP), accessible through the Data Analysis command on the Tools menu. The Analysis Toolpak provides many statistical functions not available directly through Excel. Depending on your installation, the Analysis Toolpak may or may not be installed.

Installing the Analysis Toolpak (from Microsoft Help Files)

If the Tools menu has a Data Analysis command, the Analysis Toolpak is installed and you can skip this section. Otherwise you must install the Analysis ToolPak.

First, on the Tools menu, click Add-Ins. If Analysis ToolPak is not listed in the Add-Ins dialog box, click Browse and locate the drive, folder name, and file name for the Analysis ToolPak add-in, Analys32.xll — usually located in the Microsoft Office\Office\Library\Analysis folder — or run the Setup program if it isn't installed.

Select the Analysis ToolPak check box.

     

How to Construct a Histogram in Excel

  1. In this example, I'll do Exercise 1.26.  The data is in the file ta01-007.txt.  Open an Excel file
    and import the data file (Data | Import External Data | Import Data).  If you imported the data to cell A1, then the Pasadena temperatures are in column B.  In column E, starting at E1, type 62, 63, 64, 65, 66, 67.  These are the bin separators. I want to construct a histogram with 6 bars, each corresponding to an interval of length one. The list resides at B1 to B50. The intervals are: [61,62], (62,63], (63,64], (64,65], (65,66], (66,67].
  2. In the Tools Menu, choose Data Analysis…. A window comes up, choose Histogram, then press OK.
  3. The first open box is listed Input Range. Select from B1 to B50.
  4. The second open box is listed as Bin Range. Click in the open box. Select  E1 to E6.  
  5. If you want the histogram on this same page, click Output Range and enter a cell number, such as H4. If you don’t want the chart on the same page, choose New Worksheet Ply.
  6. Click the box labeled Chart Output, then press OK.  
  7. It's not officially a histogram unless the bars are scrunched together, with no spaces in between them. To get the bars right next to each other, click on the finished graph. Double click on one of the vertical bars. Click on "options." Change gap width to 0. Press OK.
  8. Make changes as necessary: resize histogram shape, edit title, edit axis labels, etc.  

Reference: