Computing a Frequency Distribution in Excel

Fundamentals of Social Statistics by Adam J. McKee

Frequency distributions are a foundational element in data analysis, helping researchers and analysts identify patterns and trends within a dataset. Microsoft Excel, one of the most widely-used spreadsheet applications, offers a built-in function specifically designed for this purpose: the FREQUENCY function.

Unlike many other Excel functions that return a single value, the FREQUENCY function is unique in that it returns an array. This means it can produce multiple outputs or values from a single formula. These arrays can show the distribution of values across various data intervals, which can be extremely insightful when dissecting large datasets or when understanding how data points are grouped within specified ranges.

The way the FREQUENCY function works in Excel is by counting the number of values that fall within specified intervals or “bins.” For instance, if an analyst is looking at the distribution of student grades, they can set up bins for grade ranges (e.g., 90-100, 80-89, and so on). When the FREQUENCY function is applied, it will return the count of scores within each of these intervals. This kind of data presentation makes it easier to gauge how scores are dispersed and to identify any predominant trends, such as a large number of students scoring within a particular grade range.

However, there’s a caveat to bear in mind when working with the FREQUENCY function in Excel: because it generates an array, the typical method of pressing ‘Enter’ after typing your formula won’t suffice. Instead, you’ll need to use a special key combination (Control + Shift + Enter) to execute the function properly. This tells Excel that the formula should be treated as an array formula. Once done correctly, Excel will then populate multiple cells with the output values, giving a clear picture of the frequency distribution across the defined bins or intervals.

Arrays In Excel
For some reason, you cannot just click “OK” to exit the Insert Function dialog box.  You must do so by pressing CTRL+SHIFT+ENTER at the same time.
Figure 8: Frequency Distributions in Excel.
Figure 8: Frequency Distributions in Excel.

[ Back | Contents | Next ]

Last Modified:  09/25/2023

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.