How to Make a Histogram in Excel Using the Frequency Function

The Importance of Histograms and Descriptive Statistics

The word “statistics” comes from the German word “stadt”. Stadt means “city” but in the 18th century each city was a state in its own right. And the people who ran those states needed to have the facts. What is the population? What is the food production? How many men are available for the military? What is the birth rate? Information about the state came to be known as Statistics. In today’s complex world people do data mining, gather big data, and perform tests of statistical hypotheses. It is easy to forget that one of the primary tasks of statistics is purely descriptive.

Calculating descriptive statistics such as mean, median, and variance is easy, since Excel has functions for this purpose. Let’s look at something a little more complicated but a necessary tool in the statistician’s toolbox, the frequency distribution and its graphical comrade, the histogram.

Creating a Histogram using Microsoft Excel

We’ll stick with the centuries-old tradition and gather some information about cities, starting with a table of income information about cities in the United States from the year 2009. We would like to observe the frequency distribution of median family incomes. The Excel data analysis toolpak has a nice dialog box for doing this automatically, but we’re going to take charge and do it ourselves using the FREQUENCY function.

We must first define the bins, which is to say the value ranges into which our data will be sorted. Generally this is done in a worksheet column, and Excel’s auto-fill feature makes this easy. In this example we will create bins from 10,000 to 170,0000 at increments of 5000.

Creating Histogram Bins
Entering the first two values and then grabbing, dragging, and dropping the square in the lower right-hand corner, automatically creates a sequence in your worksheet.

Now we are ready to tabulate our frequency distribution. We highlight a second column next to our column of bin values. In the formula bar, we enter =FREQUENCY(. The first argument is the range containing our data. If you enter this range by highlighting it on the worksheet, be careful not to include the column heading. Only the data is highlighted. The second argument to FREQUENCY is the range containing the bin values. After entering the range in the formula bar close the parenthesis, but

Do not hit enter!
Highlight the range where the frequency results will appear and enter the frequency function.

Entering the Frequency Function
Entering the FREQUENCY FUNCTION. Note that the entire destination array is selected!

Enter both the data ranges and the frequency bin range.

Frequency01
The FREQUENCY function requires two ranges. The first defines the range that contains the data; the second defines the range that contains the boundary values for our histogram bins.
Selecting The Ranges
As is always the case, Excel lets you indicate range arguments in a function by highlighting the worksheet range with your mouse.

 

You’re almost ready but
Do not hit enter yet!

FREQUENCY if one of the many valuable Excel array functions. Mr. Excel calls them “CSE” functions, and this name is well chosen. The CSE acronym reminds us that you enter array functions not by hitting enter but by hitting <ctrl><shift><enter>. When you hit control-shift-enter, the formua will be entered in the highlighted cells. Note that in the formula bar, the function is now within curly braces. You cannot just type in these curly braces yourself. You must let Excel enter them after you type <ctrl><shift><enter>.

Now that we have our frequency distribution, it is impossible to resist creating a chart. The frequency range is already highlighted; we have only to click on “column chart” from Excel’s Insert ribbon tab. The chart looks OK, but we see that we will have to edit the horizontal axis label to reflect our frequency bins. We can right-click on the chart and choose “Select Data…” . Click the “Edit” button on the right-hand side, the side for the horizontal axis labels. Then enter the range containing the frequency bins.

The Insert Chart button
Not surprisingly, Insert Chart is on the Insert tab of the Microsoft Excel ribbon.

 

The horizontal axis is indexed because we have not specified the frequency bins.
Excel does not yet know how to label the horizontal axis of our chart.

Right-click the chart and choose “Select Data…” to edit the axis labels.

Using the context menu to access the chart edit tool.
The “Select Data…” choice on the chart context menu allows us to edit the axis labels.

Right-click the chart and choose “Select Data…” to edit the axis labels.

The Select Data Source dialog box.
The Select Data Source dialog box allows us to edit the values used as horizontal axis labels.
The Axis Labels dialog box.
The range used for the horizontal axis labels is the same one that contains the bins for the FREQUENCY function.

After entering a title and choosing a style, we have our finished frequency histogram!

The finished histogram.

 

I hope you enjoyed my first Excel post! Stay tuned for my next one where we cover Standard Deviations.

Type to search blog.learningtree.com

Do you mean "" ?

Sorry, no results were found for your query.

Please check your spelling and try your search again.