EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Excel Excel Resources Statistical Functions in Excel Excel Frequency Distribution
 

Excel Frequency Distribution

Jeevan A Y
Article byJeevan A Y
Madhuri Thakur
Reviewed byMadhuri Thakur

Frequency Distribution in Excel

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 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 :

FREQUENCY Formula in Excel

The Frequency Function has two arguments 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 that are 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 examples.

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 the 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 will see how to find the frequency with the available student database.

Let’s consider the following example, which displays the scores of students:

Example 1

We must group the data with students’ marks to calculate frequency, as shown below.

Frequency Distribution Example 1-2

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

  • 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 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 columns.
  • 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 student’s marks with mark wise which shows students have 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. To create a pivot table, we must 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

  • Ensure 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 the 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 enhance the professional display of the grouped Sales data by presenting it in a graphical format, showcasing the Minimum to Maximum values grouped by 1000.

  • 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

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 sales data below for creating a histogram with Sales Person Name with corresponding sales values. CP is nothing, but Consumer Pack and Tins are range values, i.e., how many 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 histograms by following the below steps.

  • Go to Data Menu on the right top to 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

  • Ensure we have a checkmark for all options like label option, Cumulative Percentage, and Chart Output, and then Click OK.

Frequency Distribution Example 3-6

  • In the below chart, we got the output showing the cumulative percentage and 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 to 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 we are grouping correctly.
  • While using Excel frequency distribution, ensure that classes are equal in size with 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, how to make Frequency Distribution in Excel, and practical examples and downloadable Excel templates. 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

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

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

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download Frequency Distribution Excel Template

EDUCBA Login

Forgot Password?

EDUCBA

Download Frequency Distribution Excel Template

EDUCBA

डाउनलोड Frequency Distribution Excel Template

🚀 Limited Time Offer! - 🎁 ENROLL NOW