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 then select the data which 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 is zero (i.e. Null values) then 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 is zero (i.e. Null values) then it will return the number of 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 Formulas menu which comes under the statistical category by following the below steps as follows.
- Go to Formula menu.
- Click on More Function.
- Under 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.
4.8 (3,748 ratings)
View Course
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 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
In this example, we will see 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 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 so that we will get the sales count numbers which are 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 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. Where CP is nothing but Consumer Pack and Tins are range values i.e how much tins has been sold out for the specific salespersons.
We can find 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 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 check mark 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 –
