Table of Contents
To create a Frequency Distribution in Excel, first you need to enter your data into a spreadsheet. Then, select the data and click the “Data” tab on the ribbon and select “Data Analysis”. Choose “Frequency” in the list of options and click “OK”. In the dialog box that appears, select the range of data, choose the output range, and click “OK”. This will generate a frequency distribution table which will show the number of occurrences of each value in your data set.
A frequency distribution describes how often different values occur in a dataset. It’s a useful way to understand how data values are distributed in a dataset.
Fortunately it’s easy to create and visualize a frequency distribution in Excel by using the following function:
=FREQUENCY(data_array, bins_array)
where:
- data_array: array of raw data values
- bins_array: array of upper limits for bins
The following example illustrates how to use this function in practice.
Example: Frequency Distribution in Excel
Suppose we have the following dataset of 20 values in Excel:
First, we will tell Excel what upper limits we’d like to use on the bins of our frequency distribution. For this example we’ll choose 10, 20, and 30. That is, we’ll find the frequencies for the following bins:
- 0 to 10
- 11 to 20
- 21 to 30
- 30+
Next, we’ll use the following =FREQUENCY() function to calculate the frequencies for each bin:
=FREQUENCY(A2:A21, C2:C4)
Here are the results:
The results show that:
- 6 values in the dataset are within the range of 0-10.
- 7 values in the dataset are within the range of 11-20.
- 5 values in the dataset are within the range of 21-30.
- 2 values in the dataset are greater than 30.
We can then use the following steps to visualize this frequency distribution:
- Highlight the frequency counts in the range D2:D5.
- Click on the Insert tab, then click on the chart titled 2-D Column in the Charts group.
The following chart will appear that displays the frequencies for each bin:
Feel free to modify the axes labels and bar widths to make the chart more aesthetically pleasing:
You can find more Excel tutorials here.