How to Create a Frequency Distribution in Excel – Frequency Distribution Formula

  • Home
  • / How to Create a Frequency Distribution in Excel – Frequency Distribution Formula

Using frequency distribution in Excel lets you understand how you can spread out data evenly. You may use a histogram to present data distribution. Data Analysis Toolkit is used t create a frequency distribution in Excel. You can easily find an answer to how to create a frequency distribution in Excel and how it helps in presenting data smoothly.

Frequency Formula in Excel:

The FREQUENCY formula is used when you need to make a frequency distribution table.

Syntax of Frequency Formula:


This formula contains two arguments:

Data Array: It helps in counting the frequencies. Suppose your data array values are zero or Null values, the frequency function gives an array of zero values.

BIN Array: It helps in grouping the values in the data array. Suppose the BIN array values are zero, you will get the number of array elements from the data array.

Let’s find out the complete process of creating frequency distribution in Excel:

How to Create Frequency Distribution in Excel with Histogram Chart?

Histogram is a great option to avail while making the frequency distribution. This simplest procedure does not need any sort of training. Below are the steps to follow:

  • First, select all the cells from the dataset.

frequency histogram

  • Open the Insert tab and choose the Insert Static Chart option. You will find a drop-down menu.
  • Choose Histogram from there.

frequency histogram2

  • Ultimately, your screen will appear with a chart-like figure.

frequency histogram3

  • Right-click on the values given on the X-axis side.
  • Choose Format Axis in the Context Menu.
  • Format Axis settings menu will appear.

frequency histogram4

  • Now, set the Bin Width to whatever you prefer but here we’ll choose 7 in the Axis Options.

frequency histogram5

  • A picture will appear on the screen that shows how smoothly the frequency distribution plotting is done.

frequency histogram6

Price banner Earn and Excel

How to Create a Frequency Distribution in Excel with Pivot Table?

In this section, you will learn how easily you can use graphical data presentation to make a frequency distribution. With the help of Pivot Table, you can effortlessly make frequency distribution, so let’s get started with the procedure:

  • In the example given here, year-wise sale data is presented. Suppose, you need to use this data to use a Pivot table, let’s follow the steps given below:

frequency pivot table

  • Open the Insert menu and choose the Pivot table option to create a pivot table for the data given above.

frequency pivot table2

  • Now, drag the Sales in Row Labels. And drag the same sales in Values as well.

frequency pivot table3

  • You must ensure that the pivot field setting is selected so that you can count the sales count number.

frequency pivot table4

  • Choose the Row Label Sales number and right-click on it.
  • Select the “Group” option from the menu list.

frequency pivot table5

  • The Grouping dialog box will appear.

frequency pivot table6

  • Start editing the grouping numbers ranging from 5000 to 18000 and then Group them by 1000.
  • Press OK.

frequency pivot table7

  • You will get results in which sales data is grouped by 1000.

frequency pivot table8

Things to Consider About Excel Frequency Distribution:

In Excel frequency distribution, remember that classes must be of similar size with an upper limit and lower limit.

While grouping, it might be possible that some of the data is lost, that’s why you need to be extra careful whenever you are in the group phase.

How Frequency Distribution Works?

Now, you have learned how to create a frequency distribution in Excel, but do you know how it works?

Basically, frequency signifies the number of times something happens and the frequency of an observation that shows how something appears in a dataset.

For instance, have a look at the numbers given below:

1, 3, 1, 5, 5, 6, 1, 9, 8, 4, 2, 1

In this dataset, the frequency of number 1 is four because it appears four times here. A frequency distribution shows complex data whereas the purpose is not different. Commonly, you need to count the number of frequencies that appear within a given range.

For instance, how many test scores appear between 90-100? And how many appear between 80-89?

Referring to frequency distributions, these ranges are known as “bins”.

Let’s Wrap Up:

In this post, you have learned how to create a frequency distribution in Excel besides the basics of frequency formulas. Apart from that you also have learned how frequency distribution works. Excel always has multiple options to perform any function.

Price banner Earn and Excel

Write your comment Here