Excel Frequency Distribution

Excel Frequency Distribution (Table of Contents)

• Frequency Distribution in Excel
• Frequency Formula in Excel
• How to Make Frequency Distribution in Excel?

Frequency Distribution in Excel

Frequency Distribution in Excel is used to give an impression of how the data is spread out. This can be done using a Histogram which gives the proper vision of how the data is being distributed. To create Frequency Distribution in Excel, we must have Data Analysis Toolpak, which we can activate from the Add-Ins option available in the Developer menu tab. Once it is activated, select the Histogram from Data Analysis, and select the data we want to project.

Frequency Formula in Excel

Below is the Frequency Formula in Excel :

The Frequency Function has two arguments are as below:

• Data array: A set of array values where it is used to count the frequencies. If the data array values are zero (i.e. Null values), then the frequency function in excel returns an array of zero values.
• Bins array: A set of array values which is used to group the values in the data array. If the bin array values are zero (i.e. Null values), it will return the array elements from the data array.

How to Make Frequency Distribution in Excel?

Frequency Distribution in excel is very simple and easy to use. Let’s understand the working of Excel Frequency Distribution by some example.

In excel, we can find the “frequency function” in the Formulas menu, which comes under the statistical category, by following the below steps as follows.

• Go to Formula menu.

• Click on More Function.

• Under the Statistical category, choose Frequency Function as shown in the below screenshot.

• We will get the Frequency Function Dialogue box as shown below.

Where data array is an array or set of values where we want to count frequencies, and Bins_array is an array or set of values where we want to group the values in the data array.

Example #1

In this example, we are going to see how to find the frequency with the available student database.

Let’s consider the below example, which shows students score which is shown below.

Now in order to calculate frequency, we have to group the data with students marks, as shown below.

Now using the frequency function, we will group the data by following the below steps.

• Create a new column named Frequency.
• Use the frequency formulation on the G column by selecting G3 to G9.

• Here we need to select the entire frequency column then only the frequency function will work properly, or else we will get an error value.
• As shown in the above screenshot, we have selected column as data array and Bin array as Student marks =FREQUENCY (F3:F9, C3:C22) and go for CTRL+SHIFT+ENTER.
• So that we will get the values in all the column.
• Once we hit the CTRL+SHIFT+ENTER, we can see the open and closing parenthesis as shown below.

Now using the Excel Frequency Distribution, we have grouped the student’s marks with mark wise which shows students has scored marks with 0-10 we have 1 student, 20-25 we have 1 student, 50-55 we have 1 student, and 95-100 we have 1 student as shown below.

Example #2

Excel Frequency Distribution Using Pivot Table

This example shows how to make excel frequency distribution using graphical data with the available sales database.

One of the easiest ways to make excel frequency distribution is using the pivot table so that we can create graphical data.

Consider the below sales data which has a year-wise sale. Now we will see how to use this using a pivot table with the following steps.

• Create a Pivot Table for the above sales data. For creating a pivot table, we have to go to the insert menu and select pivot table.

• Drag down the Sales in Row Labels. Drag down the same sales in Values.

• Make sure that we have selected the pivot field setting to count to get the sales count numbers shown below.

• Click on the row label sales number and right click then Choose Group option.

• So that we will get the grouping dialogue box as shown below:

• Edit the grouping numbers starting at 5000 and ending at 18000 and it Group By 1000 and then click ok.

•  After that, we will get the below following result where sales data has been grouped by 1000 as shown below:

We can see that Sales data has been grouped by 1000 with Minimum to Maximum values, which can be shown more professionally by displaying in graphical format.

• Go to the Insert menu and select the Column chart.

• So the output will be as follows:

Example #3

Excel Frequency Distribution Using Histogram

By using the pivot table, we have grouped the sales data; now, we will see how to make historical sales data by Frequency Distribution in excel.

Consider the below sales data for creating a histogram which has Sales Person Name with corresponding sales values. CP is nothing but Consumer Pack and Tins are range values, i.e. how much tins have been sold out for specific salespersons.

We can find a histogram in the data analysis group under the data menu, which is nothing but add-ins. We will see how to apply histogram by following the below steps.

• Go to Data Menu on the right top; we can find the data analysis. Click on the data analysis, which is highlighted as shown below.

• So that we will get the below dialogue box, choose the Histogram option and Click ok.

• We will get the below histogram dialogue box.

• Give the Input Range and Bin Range as shown below.

• Make sure that we have a checkmark all option like label option, Cumulative Percentage, Chart Output and then Click OK.

• In the below chart, we got the output which shows the cumulative percentage along with frequency.

We can display the above histogram more professionally by editing the sales data as follows.

• Right-click on the histogram chart and click on Select Data.

• We will get the dialogue box to change the Ranges. Click on edit.

• So that we can edit the ranges, we need to give. Edit the Bins value what exactly we need to specify the range so that we will get the appropriate result and then click ok.

•  So the result will be as below.

Things to Remember about Excel Frequency Distribution

• In excel Frequency distribution, while grouping, we might lose some of the data; hence make sure that we are grouping in a proper manner.
• While using excel frequency distribution, make sure that classes should be in equal size with an upper limit and lower limit values.

Recommended Articles

This has been a guide to Frequency Distribution in Excel. Here we discuss the Frequency Formula in excel and how to make Frequency Distribution in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. FREQUENCY Excel Function
2. Excel GETPIVOTDATA Function
3. Pivot Table In Excel
4. Examples of Excel Macros