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:
=FREQUENCY(
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.
- Open the Insert tab and choose the Insert Static Chart option. You will find a drop-down menu.
- Choose Histogram from there.
- Ultimately, your screen will appear with a chart-like figure.
- Right-click on the values given on the X-axis side.
- Choose Format Axis in the Context Menu.
- Format Axis settings menu will appear.
- Now, set the Bin Width to whatever you prefer but here we’ll choose 7 in the Axis Options.
- A picture will appear on the screen that shows how smoothly the frequency distribution plotting is done.
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:
- Open the Insert menu and choose the Pivot table option to create a pivot table for the data given above.
- Now, drag the Sales in Row Labels. And drag the same sales in Values as well.
- You must ensure that the pivot field setting is selected so that you can count the sales count number.
- Choose the Row Label Sales number and right-click on it.
- Select the “Group” option from the menu list.
- The Grouping dialog box will appear.
- Start editing the grouping numbers ranging from 5000 to 18000 and then Group them by 1000.
- Press OK.
- You will get results in which sales data is grouped by 1000.
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.
