A histogram can be used to plot frequency distribution in Excel, allowing you to visualize the number of times each member of a data set occurs in the set. For example, if you have a list of test scores for a class of 20 students, you can use a histogram to show how many students received each score within a certain range. Like other statistical charts in Excel, you can automatically create a histogram from a list of data values and then customize it for your specific needs.
Creating a Histogram in Excel
In Excel 2016 and newer versions, you create a histogram by first selecting the cells in the spreadsheet containing the data you want to plot. The data should be in the form of a list of values. Next, go to the Insert tab. In the Charts section, pick Histogram from the Insert Statistic Chart menu. You can also find Histogram in the Recommended Charts panel on the All Charts tab.
After you create the histogram, you can change some of its visual features, such as the chart title, by clicking on them. Customize other aspects of the chart using options on the Chart Design tab, which shows when the histogram is selected. You can change the colors used in the chart or modify its overall style, choosing from templates with different background colors for the chart and solid or patterned chart bars. The Chart Design tab also has options for redefining the data used for the chart and for switching it to another type of chart.
Configuring Bins in Excel
When you plot a continuous set of data values, such as one where every value from 0 to 100 is represented, you probably don't want a separate bar in the chart for each value. Histograms use intervals, known as bins, to group the values. For example, bins representing 10 test scores might be used to show the distribution of scores. One bin might be the scores from 10 to 20, the second bin the scores from 20 to 30, and so on.
For each bin, Excel finds the values that are greater than the lower bound of the bin and less than or equal to the upper bound. When creating a histogram chart in Excel, it's important to choose a bin interval that's neither too large nor small to get the best visualization of the frequency distribution.
Excel automatically decides on the bin width for a histogram when the chart is created, using an algorithm known as Scott's normal reference rule. If you want to use a different width, you can customize the bins in your histograms by pressing the right mouse button on the chart's horizontal axis. In the Bins section under Axis Options, specify a constant number for width or define the number of bins you want. The width of each bin is calculated from the total distribution.
Interpreting Excel Bin Labels
Excel uses standard statistical notation on labels for the bin ranges on the horizontal axis. Numbers that are preceded or followed by a parenthesis are excluded from the bin, while those adjacent to a bracket are included. For example, the label "[10, 20]" denotes that both 10 and 20 are within the bin range. The label "(20, 40]" shows that 20 is not within the bin range but 40 is.
Creating Histograms in Earlier Excel Versions
In Excel 2013 and earlier versions, histogram charts aren't available unless an add-in called the Data Analysis ToolPak is installed. You can do this from the File tab. Select Options and choose Add-Ins. Select Excel Add-Ins from the Manage box and choose Go. In the Add-In box, check the box for Analysis ToolPak and click OK.