XL: How to Use the Histogram Add-in

ID: Q141684

The information in this article applies to:

SUMMARY

The Analysis ToolPak that ships with Microsoft Excel includes a Histogram tool. This article describes how to create a histogram with a chart from a sample set of data.


MORE INFORMATION

Installing the Analysis ToolPak

Before using the Histogram tool, you need to make sure the Analysis ToolPak Add-in is loaded. To verify whether the Analysis ToolPak is loaded, follow these steps:

  1. On the Tools menu, click Add-Ins.


  2. In the Add-Ins dialog box, make sure the Analysis ToolPak check box under Add-Ins Available is selected. Click OK.


NOTE: In order for the Analysis ToolPak to be shown in the Add-Ins dialog box, it must be installed on your computer. If you do not see Analysis ToolPak in the Add-Ins dialog box, run Microsoft Excel Setup, and add this component to the list of installed items.

Creating a Histogram

  1. Enter the following in a new worksheet: 

    
    A1:  87      B1: 20
    A2:  27      B2: 40
    A3:  45      B3: 60
    A4:  62      B4: 80
    A5:   3      B5:
    A6:  52      B6:
    A7:  20      B7:
    A8:  43      B8:
    A9:  74      B9:
   A10:  61     B10:


  2. On the Tools menu, click Data Analysis. In the Data Analysis dialog box, click Histogram, and then click OK.


  3. In the Input Range box, type A1:A10.


  4. In the Bin Range box, type B1:B4.


  5. Under Output Options, click New Workbook and click to select the Chart Output check box, and then click OK.


A new workbook, with a Histogram table and an embedded chart, is generated. Based on the sample data from step 1, the Histogram table will look like the following 


   A1: Bin     B1: Frequency
   A2:  20     B2:        2
   A3:  40     B3:        1
   A4:  60     B4:        3
   A5:  80     B5:        3
   A6: More    B6:        1


and your chart will be a column chart reflecting the data in this Histogram table.

Microsoft Excel counts the number of data points in each data bin. A data point is included in a particular data bin if the number is greater than the lowest bound and equal to or less than the greater bound for the data bin. In the example here, the bin that corresponds to data values from 0 to 20 contains two data points, 3 and 20.

If you omit the bin range, Microsoft Excel creates a set of evenly distributed bins between the data's minimum and maximum values.

NOTE: You will not be able to create the Histogram chart if you specify the options (Output range or New worksheet ply) that create the Histogram table in the same workbook as your data.

For additional information, please see the following article in the Microsoft Knowledge Base:

Q178243 : XL97: Regression Err: "Cannot Add Chart to a Shared Workbook"


REFERENCES

For more information about Creating a Histogram in Microsoft Excel version 7.0, click Answer Wizard on the Help menu and type: 


   tell me how to make a histogram


For more information about Creating a Histogram in Microsoft Excel version 5.0, click the Search button in Help and type:
Histogram

Additional query words: 5.00a 5.00c 8.00 97 ATP Tool Pack Toolpack mac histo 


Keywords          : kbtool kbdta xladdins 
Version           : WINDOWS:5.0,5.0c,7.0,97; MACINTOSH:5.0,5.0a
Platform          : MACINTOSH WINDOWS 
Issue type        : kbhowto

Last Reviewed: April 15, 1999