Creating a ...
Frequency Distribution Table and a Histogram in Excel 2010

Overview

A histogram graphically summarizes the distribution of a single variable data set. In tandem with a frequency distribution table, histograms are descriptive and used to assess:

  • The center and spread of the data;
  • Whether there are outliners present in the data;
  • Whether the data is symmetrical or skewed; and
  • Whether multiple modes exist in the data.

Like the average and range, histograms help determine the type of analysis needed in order to make inferences about the population as a whole.

There are three ways to create a frequency distribution table and a histogram chart in Excel 2010—via the frequency function, a PivotTable and Chart, or the Histogram tool within the Data Analysis toolpak.

Download the finished examples here:
Check out the Youtube video here:

Frequency Function

  1. Start by entering your sample data in Excel. I entered 4000 columns of data ranging in values from 134 to 284.
  2. Next, determine the bin limits.
  3. Think of these bins as intervals. For example, if I wanted to assess the spread of grades in a calculus class, I would use intervals of 10 to match the letter grades. 100 to 90 (A) 89 to 80 (B), 79 to 70 (C), and so forth.
  4. Going back to the example, take the range of the data set, which is 150, and divide it by 10 to find the bin intervals. The first bin is equal to the minimum value in the data set + 1 interval—that is, 134 plus15 which equals 149. That's the first limit. From there, add 34 to each number until the maximum value in the data set is hit. In essence, you’re entering the upper level of each interval.
  5. Create the frequency distribution table.
  6. Select the range of cells to the right of the bin limits, type =FREQUENCY in the formula bar, hit the TAB key, select the sample data, insert a comma, and select the bin limits.
  7. Then hold down both the CRTL and SHIFT keys and press ENTER to treat this formula as an array.
  8. Create the histogram chart.
  9. Highlight the range F2 to G12, click the Insert tab and select Column Chart, Clustered 2-D Column. Format the chart, by right-clicking within the chart and then clicking Select Data. Click the Edit button beneath the Horizontal Axis label and select the data under the bins header back on the Excel worksheet in order to use that range as the basis for the X axis. Click OK and then remove the Bins label beneath the Legend Entries and click OK one last time.

Pivot Table

  1. As in the previous method, enter your sample data.
  2. Create the Frequency Distribution table and histogram chart.
  3. Select the sample data including the header and insert a PivotTable and a PivotChart by clicking the Insert tab and selecting the PivotChart option from the PivotTable drop down. Select the Existing Worksheet option, click a blank cell within your current worksheet away from the current data, and then click OK. Drag the header for your sample data down to the Row Labels area and do it once again to the Values area.
  4. On the PivotTable, beneath the Row Labels, right-click any cell and select Group. This is where the bin labels (limits) are setup. For more info on this, see the Frequency Function example. Change the values to 150, 284, and 15, respectively, and click OK. Right click any cell beneath the Sum of Data, hover the cursor over "Summarize Values By", and then click Count.

Data Analysis Toolpak

  1. Install the ToolPak if needed. (File > Options > Add-Ins > Analysis ToolPak > Click GO > Check the Analysis ToolPak > Click OK)
  2. Again, enter your sample data and define the bin limits.
  3. Create the histogram chart.
  4. Select the Data tab, click Data Analysis on the far right of the Ribbon, select Histogram, and then click OK.
  5. The Input Range is the sample data range and the Bin Range is the pre-specified bin limits. Make sure to include the headers when you select the range and check the Labels option.
  6. Select the Output Range as a cell within the current worksheet, make sure the Chart Output option is checked, and then click OK.

Finally, the lattermethod’s resulting data table and chart are static. Meaning: you will have to re-run the Data Analysis toolpak every time there is a change in the sample data and/or bin limit. On the other hand, the former two methods create dynamic output, so if the sample data is changed or additional data is added, the frequency table and histogram will update automatically (you have to "refresh" the PivotTable and Chart, though).