Table of Contents
To create a frequency distribution in Excel, follow these steps:
1. Arrange your data in a column or row.
2. Click on the “Data” tab and select “Data Analysis” from the “Analyze” group.
3. In the “Data Analysis” dialog box, select “Histogram” and click “OK”.
4. In the “Input Range” field, select the data you want to use for the frequency distribution.
5. In the “Bin Range” field, enter the range of values you want to use for the frequency intervals.
6. Check the “Chart Output” box if you want to create a chart alongside the frequency table.
7. Click “OK” to generate the frequency distribution table and chart.
This will provide a visual representation of the frequency of each value in your data set, making it easier to analyze and interpret.
Create a Frequency Distribution in Excel
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.