Create a histogram

Important notice for users of Office 2003    To continue receiving security updates for Office, make sure you're running Office 2003 Service Pack 3 (SP3). The support for Office 2003 ends April 8, 2014. If you’re running Office 2003 after support ends, to receive all important security updates for Office, you need to upgrade to a later version such as Office 365 or Office 2013. For more information, see Support is ending for Office 2003.

You can analyze your data and display it in a histogram with the Histogram tool of the Analysis ToolPak. This data analysis add-in is available in Excel, but may not be automatically loaded.

 Note   If you don't see Data Analysis on the Tools menu, you will need to load the add-in. For more information, see Load or unload add-in programs.

  1. Enter the data you want to use as input for the Histogram tool in one column. If you want, you can add a label in the first cell of this column.

 Note   You must enter quantitative numeric data (such as item amounts or test scores) in each cell of the input column—the Histogram tool doesn't work with qualitative numeric data (such as identification numbers).

  1. In another column, enter the bin numbers you want to use for the analysis. The bin numbers need to be entered in ascending order. To quickly generate a set of evenly distributed bin numbers, you can use the Fill Series option.

ShowHow?

  1. Enter two or three evenly spaced bin numbers, and then select all of them.
  2. Drag the fill handle (fill handle: The small black square in the lower-right corner of the selection. When you point to the fill handle, the pointer changes to a black cross.) fill handle image across the cells you want to fill, and then release the mouse button.
  3. If needed, click Auto Fill Options AutoFill button, and then click Fill Series.

 Note   If you don't enter bin numbers, the Histogram tool automatically creates evenly distributed bin intervals using the minimum and maximum values in the input range as beginning and end points. However, these bins may not be useful—creating your own bin numbers is recommended.

ShowMore about bin numbers

Bin numbers represent the intervals you want the Histogram tool to use for measuring the input data in the data analysis. The Histogram tool counts the number of data points between the current bin number and the adjoining higher bin, if any. A number is counted in a particular bin if it is equal to or less than the bin number down to the last bin. All values below the first bin value are counted together, as are the values above the last bin value.

  1. On the Tools menu, click Data Analysis.
  2. In the Analysis Tools box, click Histogram, and then click OK.
  3. Under Input in the Input Range box, enter the cell reference for the range of data you want to analyze.
  4. Under Input in the Bin Range box, enter the cell reference to a range that contains an optional set of boundary values that define bin ranges.

 Note   If you omit the bin range, the Histogram tool creates a set of evenly distributed bins between the data's minimum and maximum values. However, because these bin intervals may have little relation to your data, or the type of analysis you are looking for, it is recommended that you create your own bins.

  1. If the first row or cell in the column of your input range contains labels, select the Labels check box.
  2. Under Output options, do one of the following:
  • To paste the output table on the same sheet, click Output Range, and then enter the reference of the upper-left cell of the output table.

 Note   The Histogram tool automatically determines the size of the output area and displays a message if the output table will replace existing data.

  • To insert a new worksheet in the current workbook and paste the output table starting at cell A1 of the new worksheet, click New Worksheet Ply.

You can name the worksheet by typing a name in the box.

  • To create a new workbook and paste the output table on a new worksheet in the new workbook, click New Workbook.
  1. Under Output options, do any or all of the following:
  • To present data in the output table in descending order of frequency, select the Pareto (sorted histogram) check box.
  • To generate an output table column for cumulative percentages and to include a cumulative percentage line in the histogram chart, select the Cumulative Percentage check box.
  • To generate an embedded histogram chart with the output table, select the Chart Output check box.

 Note   After the bin and frequency table is generated, you can select any of the text and change the default labels. To change the display of the histogram, use the options on the Chart toolbar, which appears when you click the histogram.

 
 
Applies to:
Excel 2003