How to Create a Histogram in Excel

By Zicheng Ren

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.

Create a Histogram in Excel 2013

Step 1

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

Step 2

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

Step 3

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

Step 4

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.

Step 5

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

Step 6

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.**

Step 7

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 1

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.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.

Step 2

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.

Step 3

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

Step 4

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

Step 5

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.

Step 6

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

Step 7

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

Step 8

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.