How to make a frequency table in Excel

Excel Frequency Distribution (Table of Contents)Frequency Distribution inExcelFrequency Formula in ExcelHow to Make Frequency Distribution in Excel?Frequency Distribution inExcelFr

How to make a frequency table in Excel
Frequency Distribution inExcel

Excel Frequency Distribution (Table of Contents)

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

Frequency Distribution inExcel

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.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

Frequency Formula in Excel

Below is the Frequency Formula in Excel :

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. Lets understand the working of Excel Frequency Distribution by some example.You can download this Frequency Distribution Excel Template here Frequency Distribution Excel Template

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.
Formula menu
  • Click on More Function.
More Function
  • Under the Statistical category, choose Frequency Function as shown in the below screenshot.
Choose Frequency Function
  • We will get the Frequency Function Dialogue box as shown below.
Dialogue box

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.

Lets consider the below example, which shows students score which is shown below.Popular Course in this category

Sale

Excel Advanced Training (16 Courses, 23+ Projects)16 Online Courses | 23 Hands-on Projects | 140+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.8 (11,332 ratings)Course Price

View Course


Related CoursesExcel for Marketing Training (8 Courses, 13+ Projects)

Example 1

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

Frequency Distribution Example 1-2

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.
Frequency Distribution Example 1-3
  • 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.
Frequency Distribution Example 1-4

Now using the Excel Frequency Distribution, we have grouped the students 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.

Result of Example 1

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.

Example 2
  • 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.
Frequency Distribution Example 2-2
  • Drag down the Sales in Row Labels. Drag down the same sales in Values.
Frequency Distribution Example 2-3
  • Make sure that we have selected the pivot field setting to count to get the sales count numbers shown below.
Frequency Distribution Example 2-4
  • Click on the row label sales number and right click then Choose Group option.
Frequency Distribution Example 2-5
  • So that we will get the grouping dialogue box as shown below:
Frequency Distribution Example 2-6
  • Edit the grouping numbers starting at 5000 and ending at 18000 and it Group By 1000 and then click ok.
Frequency Distribution Example 2-7
  • After that, we will get the below following result where sales data has been grouped by 1000 as shown below:
Frequency Distribution Example 2-8

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.
Frequency Distribution Example 2-9
  • So the output will be as follows:
Result of Example 2

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.

 Example 3

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.
Frequency Distribution Example 3-2
  • So that we will get the below dialogue box, choose the Histogram option and Click ok.
Frequency Distribution Example 3-3
  • We will get the below histogram dialogue box.
Frequency Distribution Example 3-4
  • Give the Input Range and Bin Range as shown below.
Frequency Distribution Example 3-5
  • Make sure that we have a checkmark all option like label option, Cumulative Percentage, Chart Output and then Click OK.
Frequency Distribution Example 3-6
  • In the below chart, we got the output which shows the cumulative percentage along with frequency.
Frequency Distribution Example 3-7

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.
Frequency Distribution Example 3-8
  • We will get the dialogue box to change the Ranges. Click on edit.
Frequency Distribution Example 3-9
  • 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.
Frequency Distribution Example 3-10
  • So the result will be as below.
Result of Example 3

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.

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 alsogo through our other suggested articles

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

All in One Excel VBA Bundle (120+ Courses, 30+ Projects)

120+ Online Courses

30+ Projects

500+ Hours

Verifiable Certificates

Lifetime Access

Learn More2 SharesShareTweetShare

Video liên quan