How to Create a Histogram in Excel

Excel 2013 has several chart options for displaying data graphically. While a histogram shows you the number of items in a set of data that fall within a certain range using whole numbers, a relative frequency histogram shows the same information using percentages. You can use Excel to create histograms and relative frequency histograms by using the Data Analysis add-in and bar graphs.

...
Display your data as a histogram.
credit: Courtesy of Microsoft

Create a Histogram in Excel 2013

Step

Open Excel and click Options. You need to load a data analysis add-in in Excel in order to use histograms.

...
Click Options.
credit: Courtesy of Microsoft
...
Add the Analysis ToolPak.
credit: Courtesy of Microsoft

Step

Select Add-Ins from the left menu, choose the Analysis ToolPak and click Go.

...
You can also install other tools from this window, too.
credit: Courtesy of Microsoft

Step

Check the box for Analysis ToolPak and click "OK" to install the add-in. This ToolPak includes histograms.

...
Prepare your data.
credit: Courtesy of Microsoft

Step

Open a spreadsheet that includes the data you want to display as a histogram, or open a new spreadsheet and enter your data. In this example, the Grade Categories values are the bin numbers for the histogram. (In histograms, the bin numbers are the ranges that your data is sorted into.) The Students' Final Grades values are the input values for the histogram, that is, the data that you want to sort and analyze according to where it falls along the range of bin numbers. The Grade Key reminds you that a histogram sorts values into bin categories based on if they are greater than the next smallest bin number and less than or equal to the current bin number. For example, all of the grades that are between zero and 60 are placed under the bin number 60, while all of the grades that are greater than 60 but less than or equal to 70 are placed under the bin number 70.

...
Open the Histogram menu.
credit: Courtesy of Microsoft

Step

Click the Data tab and choose Data Analysis. Select the Histogram option and click OK.

...
Enter values for the histogram.
credit: Courtesy of Microsoft

Step

Enter the values you want to analyze in the Input Range box. You can type the cells or click on the top cell of the column and drag to the bottom cell. In the same manner, enter the range of values for the histogram in the Bin Range box. Choose a location to output the histogram; in this example, the histogram appears in a new worksheet. Choose the type of output you desire; in this example, the histogram is output as a chart. Click OK.

...
View the histogram.
credit: Courtesy of Microsoft

Step

Check the histogram. Excel generates a list of frequency values that tells you how many of your input values fell into each bin number range; it displays the data graphically in the histogram chart.

Create a Relative Frequency Histogram

Step

Calculate the relative frequency values of your data. For each bin number, divide the number of input values that fall into that bin value's range by the total number of input values.

...
Calculate relative frequencies for the data.
credit: Courtesy of Microsoft

Step

For example, in this data, there is one input value out of 13 that falls below or equal to 60, the first bin number; so to calculate the relative frequency, you would type =(1/13) into cell D2, in the Relative Frequency column. There are two, three, two, and five values falling into the other bin ranges, so you would type =(2/13), =(3/13), =(2/13) and =(5/13) into cells D3 through D6 respectively. Excel automatically does the division and displays the answer as a decimal number. The sum of all of your frequencies should be one.

...
Create a bar chart with the percentage data.
credit: Courtesy of Microsoft

Step

Create a bar chart to use as your relative frequency histogram. You cannot use the histogram option in Excel to create a relative frequency histogram since the percentage values are smaller than the bin numbers for almost every set of data; this results in an incorrect histogram where every percentage is considered to be in the first bin range (between zero and the first number). Instead, select the percentages and go to the Insert tab.

...
Add the bar chart.
credit: Courtesy of Microsoft

Step

Select a Clustered Column from the Insert Column Chart drop-down menu.

...
Select the horizontal axis.
credit: Courtesy of Microsoft

Step

Right-click on the horizontal axis values of the bar chart that appears and choose Select Data from the drop-down menu.

...
Edit the bin numbers.
credit: Courtesy of Microsoft

Step

Click Edit for the Horizontal (Category) Axis Labels so that you can enter the correct bin numbers for your data instead of the default numbers for the bar chart.

...
Enter your bin numbers for the horizontal axis.
credit: Courtesy of Microsoft

Step

Click in the Axis Label Range field and select your bin numbers. Click OK.

...
Click OK.
credit: Courtesy of Microsoft

Step

Note that your values have been added to the horizontal axis, and click OK.

...
Check your histogram.
credit: Courtesy of Microsoft

Step

Check your relative frequency histogram. Note that the frequency percentage values make up the vertical axis, and the bin values make up the horizontal axis.