How to Create a Frequency Distribution in Excel Formula

To create a frequency distribution in Excel, you can use the FREQUENCY function, which has the following syntax:

=FREQUENCY(data_array, bins_array)

where:

• data_array is the range of cells that contains the numerical data that you want to analyze.
• bins_array is the range of cells that contains the upper limits of the bins that you want to use.

The FREQUENCY function returns an array of values that represent the frequency of each bin. The array has one more element than the bins_array, which is the frequency of the values that are greater than the highest bin. For example, if your bins_array has four elements, such as 10, 20, 30, and 40, the FREQUENCY function will return an array of five elements, such as 6, 7, 5, 2, and 3, which are the frequencies of the bins 0-10, 11-20, 21-30, 31-40, and 41+.

To enter the FREQUENCY function as an array formula, you need to follow these steps:

1. Select a range of cells that has the same number of rows as the bins_array plus one, and the same number of columns as the data_array.
2. Type the FREQUENCY function in the formula bar, and make sure to use absolute references for the data_array and the bins_array by adding dollar signs before the column and row references, such as $B$2:$B$16 and $E$2:$E$6.
3. Press Ctrl+Shift+Enter to enter the formula as an array formula. Excel will automatically add curly braces around the formula to indicate that it is an array formula. You should see the frequency distribution in the selected range of cells.

Example of Creating a Frequency Distribution in Excel Formula

To illustrate how to create a frequency distribution in Excel formula, let’s use the following example dataset of 18 numbers between 0 and 10:

We want to create a frequency distribution that shows how many numbers fall within the following bins:

To do this, we follow these steps:

1. Select a range of cells that has six rows and one column, such as G2:G7.
2. Type the following formula in the formula bar: =FREQUENCY($B$2:$B$19,$E$2:$E$6)
3. Press Ctrl+Shift+Enter to enter the formula as an array formula. Excel will automatically add curly braces around the formula and display the frequency distribution in the selected range of cells, as shown below:

The results show that:

• 2 numbers in the dataset are within the range of 0-2.
• 4 numbers in the dataset are within the range of 2-4.
• 5 numbers in the dataset are within the range of 4-6.
• 4 numbers in the dataset are within the range of 6-8.
• 2 numbers in the dataset are within the range of 8-10.
• 1 number in the dataset is greater than 10.

How to Visualize a Frequency Distribution in Excel

To visualize a frequency distribution in Excel, you can use a histogram chart, which is a type of column chart that shows the frequency of each bin. A histogram chart can help you see the shape and spread of your data, as well as identify any gaps, outliers, or clusters.

To create a histogram chart in Excel, you need to have the Analysis ToolPak add-in installed and activated. You can check if you have the Analysis ToolPak add-in by clicking the Data tab and looking for the Data Analysis command in the Analysis group. If you don’t see the Data Analysis command, you can install the Analysis ToolPak add-in by following these steps:

1. Click the File tab and then click Options.
2. In the Excel Options dialog box, click Add-Ins in the left pane.
3. In the Manage drop-down list, select Excel Add-ins and then click Go.
4. In the Add-Ins dialog box, check the box next to Analysis ToolPak and then click OK.
5. Excel will install the Analysis ToolPak add-in and display the Data Analysis command in the Data tab.

Once you have the Analysis ToolPak add-in installed and activated, you can create a histogram chart by following these steps:

1. Click the Data tab and then click Data Analysis in the Analysis group.
2. In the Data Analysis dialog box, select Histogram and then click OK.
3. In the Histogram dialog box, do the following:
   • In the Input Range box, enter the range of cells that contains the data_array, such as B2:B19.
   • In the Bin Range box, enter the range of cells that contains the bins_array, such as E2:E6.
   • Check the box next to Chart Output to create a histogram chart.
   • Optionally, you can also check the box next to Output Range and enter a cell reference where you want to display the frequency distribution table, such as G2.
   • Click OK.
4. Excel will create a histogram chart and display it in a new worksheet, as shown below:

The histogram chart shows the frequency of each bin as a column. The height of each column represents the frequency of the bin, and the width of each column represents the bin size. You can modify the chart title, axes labels, and column widths to make the chart more informative and appealing.