Plotting the frequency of data falling within numeric ranges illustrates the diversity of your data. As an example, a teacher might wish to calculate and display her students’ grades by tabulating the number of scores corresponding to each letter grade. Microsoft Excel’s Frequency function simplifies this process. This function displays the number of data points falling within given ranges and sets the stage for charting these frequencies. Using a scatter chart automatically plots the ranges against their frequencies, but using a column graph creates a familiar histogram type chart.
Open Microsoft Excel and list your data in column A. As an example, you might list your 30 students’ grades from cells A1 through A30.
List the data ranges in which the frequencies should be tabulated in column B. Each successively higher number corresponds to the highest number for the given range. In the example, say grades F, D, C, B and A corresponded to ranges 0 to 59, 60 to 69, 70 to 79, 80 to 89 and 90 to 100, respectively. In this case, you would enter 60, 70, 80, 90 and 100 in cells B1 through B5.
Enter “=FREQUENCY(data_range,frequency_range)” without quotes in cell C1. Replace “data_range” with the data range from column A. Replace “frequency_range” with the ranges you specified in column B. In the example, enter “=FREQUENCY(A1:A3,B1:B5)” in cell C1.
Click and drag you mouse from cell C1 to the cell that corresponds to the last range in column B. In the example, drag from C1 to C5.
Press “F2.” Hold the “Ctrl” and “Shift” keys and press “Enter” to copy the formula as an array. The frequencies of each range will display.
Drag your mouse across all the ranges and frequencies. In the example, drag from cell B1 to C5 to select all those cells.
Click the “Insert” tab. Click “Scatter” from the Charts group and select “Scatter With Straight Lines,” which is the last option in the Scatter drop-down menu. The Chart appears on the same worksheet and plots the range versus the frequency.
Click the “Design” tab. Click “Change Chart Type” in the Type group. Click the first option in the “Column” section and click “OK” to make a column histogram chart.