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.
- 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).
- 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.
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.
More 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.
- On the Tools menu, click Data Analysis.
- In the Analysis Tools box, click Histogram, and then click OK.
- Under Input in the Input Range box, enter the cell reference for the range of data you want to analyze.
- 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.
- If the first row or cell in the column of your input range contains labels, select the Labels check box.
- 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.
- 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.