How to Create a Histogram in Excel

Techwalla may earn compensation through affiliate links in this story.
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.
Image Credit: Hero Images/Hero Images/GettyImages

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.

Advertisement

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.

Advertisement

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.

Advertisement

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.

Advertisement

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.

Advertisement

references