How to Do a Frequency Distribution on Excel (3 Easy Methods)

A frequency distribution can help you figure out what proportion of distribution is made up of specific values. By grouping values together, a frequency distribution summarizes how often each value occurs. Today, In this article, we’ll get to learn how to do a frequency distribution on Excel effectively with appropriate illustrations.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Introduction to Frequency Distribution

By grouping values together, a Frequency Distribution summarizes how often each value occurs. A frequency distribution is a data set or graph designed to represent the frequencies of occurrence outcomes in the discipline of statistics. Each frequency distribution result is a recurrent occurrence that may be seen several times from the data collection.


3 Suitable Ways to Do a Frequency Distribution on Excel

Let’s say, we have a dataset that contains information about several Sales Representatives of XYZ Group. The name of the Sales Representatives, types of the products, and the Quantity of the products are given in column B, C, and D respectively. We will use the FREQUENCY function, COUTIFS function, and create a Pivot Table to do a frequency distribution on Excel. Here’s an overview of the dataset for our today’s task.

do a frequency distribution on excel


1. Use FREQUENCY Function to Do a Frequency Distribution

Here, we’ll learn how to calculate the frequency distribution by using the FREQUENCY function. Please follow the instructions below to learn!

Steps:

  • First of all, select cell G5.

Use FREQUENCY Function to Do a Frequency Distribution on Excel

  • After selecting cell G5, type the FREQUENCY Function in the Formula Bar. The FREQUENCY Function in the Formula Bar is,
=FREQUENCY(C5:C17, F5:F8)
  • Where C5:C17 is the data_array and F5:F8 is the bins_array,

  • Hence, simply press Enter on your keyboard. As a result, you will be able to get the output of the FREQUENCY function in column G.

Use FREQUENCY Function to Do a Frequency Distribution on Excel

Read More: How to Make a Categorical Frequency Table in Excel (3 Easy Methods)


2. Apply COUNTIFS Function to Do a Frequency Distribution

In this method, we will make a frequency distribution by using the COUNTIFS Function. This is an easy and time-saving task also. Let’s follow the instructions below to do a frequency distribution!

Step 1:

  • First, select a cell. We will select H5 for the convenience of our work.

Apply COUNTIFS Function to Do a Frequency Distribution on Excel

  • After selecting cell H5, write down the below function.
=COUNTIFS($D$5:$D$17,">="&F5,$D$5:$D$17,"<="&G5)

  • Hence, simply press ENTER on your keyboard. As a result, you will get 3 as the return of the COUNTIFS function.

Apply COUNTIFS Function to Do a Frequency Distribution on Excel

Step 2:

  • Further, autoFill the COUNTIFS function to the rest of cells in Column H.

Read More: How to Make a Relative Frequency Table in Excel (with Easy Steps)


3. Create a Pivot Table to Do a Frequency Distribution

Undoubtedly, creating a pivot table to do a frequency distribution is an easy task. This is time-saving also. Let’s follow the instructions below to do a frequency distribution!

Step 1:

  • First of all, select data range B4 to D17. After that, from your Insert tab, go to,

Insert → PivotTable → From Table/Range

Create a Pivot Table to Do a Frequency Distribution on Excel

  • As a result, a PivotTable from table or range dialog box will appear in front of From the PivotTable from table or range dialog box, firstly, select ‘Pivot Table’!$B$4:$D$17 in the Table/Range selecting box. Secondly, check the Existing Worksheet. At last, press OK.

  • As a result, a pivot table will be created. Then Drag the Quantity field to the Rows and Values region as shown in the below screenshot.

Create a Pivot Table to Do a Frequency Distribution on Excel

Step 2:

  • Now, press right-click on any cell in the column heading with Sum of Quantity. Hence, a will will appear in front of you. From that window, select Value Field Settings.

Create a Pivot Table to Do a Frequency Distribution on Excel

  • Further, a Value Field Settings dialog box pops up. From the Value Field Settings dialog box, firstly, select Count. Secondly, Press OK.

  • After pressing OK, you will be able to count the frequency number which has been given in the below screenshot.

Create a Pivot Table to Do a Frequency Distribution on Excel

  • Alongside, press right-click on any cell in the column heading with Row Labels. Hence, a will will appear in front of you. From that window, select Group.

  • Further, a dialog box named Grouping pops up. From the Grouping dialog box, firstly, type 81 in the Starting at typing box. Secondly, type 120 in the Ending at typing box. Thirdly, type 10 in the By typing box. At last, press OK.

Create a Pivot Table to Do a Frequency Distribution on Excel

  • After completing the above process, you will be able to create a frequency distribution which has been given in the below screenshot.

Create a Pivot Table to Do a Frequency Distribution on Excel

Read More: How to Calculate Relative Frequency Distribution in Excel (2 Methods)


Create a Histogram Using Frequency Distribution

In this portion, we will make a Histogram to understand the frequency distribution. Let’s follow the instructions below to create a Histogram!

Steps:

  • First, select data range F5 to G8. After that, from your Insert tab, go to,

Insert → Charts → 2D Column

  • Further, you be able to create a histogram chart to understand the frequency distribution easily which has been given in the below screenshot.

Create a Histogram Using Frequency Distribution

Read More: How to Make a Relative Frequency Histogram in Excel (3 Examples)


Things to Remember

👉 Frequency distributions can display the number of observations that fall into each range or the percentage of data that fall into each range.


Conclusion

I hope all of the suitable methods mentioned above to calculate frequency distribution will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.


Related Articles

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo