DetailPage-MSS-KB

Microsoft small business knowledge base

Article ID: 214269 - Last Review: July 12, 2013 - Revision: 11.1

This article was previously published under Q214269
For a Microsoft Excel 98 version of this article, see 189519  (http://support.microsoft.com/kb/189519/ ) .

On This Page

Summary

You can analyze your data and display it in a histogram (a column chart that displays frequency data) by using the Histogram tool of the Analysis ToolPak in Microsoft Office Excel. To create a histogram, you must organize the data in two columns on the worksheet. These columns must contain input data and bin numbers. Input data is the data that you want to analyze by using the Histogram tool. Bin numbers are the numbers that represent the intervals that you want the Histogram tool to use for measuring the input data in the data analysis.

When you use the Histogram tool, 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 largest bound for the data bin. If you omit the bin range, Excel creates a set of evenly distributed bins between the minimum and maximum values of the input data.

The output of the histogram analysis is displayed on a new worksheet (or in a new workbook) and shows a histogram table and a column chart that reflects the data in the histogram table.

Before you can use the Histogram tool, you have to make sure that the Analysis ToolPak Add-in is installed.

Make sure that the Analysis ToolPak Add-in is installed

To make sure that the Analysis ToolPak Add-in is installed, follow these steps:


Excel 2010
  1. On the File menu, click Options.

    Excel 2013:

    Collapse this imageExpand this image


    Excel 2010:

    Collapse this imageExpand this image
  2. Click Add-Ins in the navigation pane.

    Collapse this imageExpand this image

  3. In the Manage list, select Excel Add-ins, and then click Go.
  4. In the Add-Ins dialog box, make sure that the Analysis ToolPak check box under Add-Ins available is selected, and then click OK.

    Collapse this imageExpand this image


    Note For the Analysis ToolPak Add-in to be displayed 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.
Excel 2007
  1. Click the Microsoft Office Button, and then click Excel Options.

    Collapse this imageExpand this image
  2. Click Add-Ins in the navigation pane.

    Collapse this imageExpand this image
  3. In the Manage list, select Excel Add-ins, and then click Go.
  4. In the Add-Ins dialog box, make sure that the Analysis ToolPak check box under Add-Ins available is selected, and then click OK.

    Collapse this imageExpand this image
Excel 2003 and earlier versions of Excel
  1. Click Add-Ins on the Tools menu.
  2. In the Add-Ins dialog box, make sure that the Analysis ToolPak check box under Add-Ins available is selected, and then click OK.

    Collapse this imageExpand this image


    Note For the Analysis ToolPak Add-in to be displayed 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.

Create a histogram table

To create a histogram table, follow these steps:
  1. Copy and paste the following data into a new worksheet. 
    Collapse this tableExpand this table
    8720
    2740
    4560
    6280
    3
    52
    20
    43
    74
    61
  2. Do one of the following:
    • In Excel 2013, Excel 2010 and Excel 2007, on the Data tab, click Data Analysis in the Analysis group.

      Collapse this imageExpand this image

    • For Excel 2003 and earlier versions of Excel, click Data Analysis on the Tools menu.
  3. In the Data Analysis dialog box, click Histogram, and then click OK.

    Collapse this imageExpand this image

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

    Collapse this imageExpand this image

  5. In the Bin Range box, type B1:B4.
  6. Under Output Options, click New Workbook, select the Chart Output check box, and then click OK.
Excel generates a new workbook with a histogram table and an embedded chart.

Based on the sample data from step 1, the histogram table will resemble the following table:
   A1: Bin     B1: Frequency
   A2:  20     B2:        2
   A3:  40     B3:        1
   A4:  60     B4:        3
   A5:  80     B5:        3
   A6: More    B6:        1
				
Your chart will be a column chart that reflects the data in this histogram table. In this example, the bin that corresponds to data values from 0 to 20 contains two data points: 3 and 20. 

For more information, click the following article number to view the article in the Microsoft Knowledge Base:
214029  (http://support.microsoft.com/kb/214029/ ) Using Data Analysis tools on grouped sheets

Applies to
  • Microsoft Excel 2013
  • Microsoft Excel 2010
  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2003
Keywords: 
kbfunctions kbfuncstat kbaddin kbhowtomaster kbsmbportal KB214269
Share
Additional support options
Ask The Microsoft Small Business Support Community
Contact Microsoft Small Business Support
Find Microsoft Small Business Support Certified Partner
Find a Microsoft Store For In-Person Small Business Support