When I talk about data, It can be any type of numerical calculation. For example, a shopkeeper maintains a record of customers in his notebook. A teacher maintains the data of students’ attendance in the register. There are many more such examples we can talk about. But since the introduction of Microsoft Excel this process has become much easier. Excel is such a software that helps us to keep the record, data of all things in a simplified manner. It offers a variety of functions to keep proper track of our data. One of the functions is Frequency Distribution Table or Frequency Distribution in Excel.
Microsoft Excel is spreadsheet software used for analyzing data and documentation. It contains several rows and columns with an intersection of a small point which we call a “cell”. These cells contain data and information in a very organized manner. It makes it easier to find the information and makes changes if required. Excel is today used in almost every sphere of life either it’s a big MNC or small business. With time Microsoft has added various features and functions to excel. One of its most important features is Frequency Distribution Table.
in today’s topic, I will throw some light on What is Frequency distribution table. What is the frequency formula in excel? Steps involved in frequency distribution in excel. So let’s begin the discussion with a simple definition of the Frequency distribution table:
Frequency distribution in Excel/ Frequency Distribution table
Frequency distribution gives us an idea of how to distribute data on a spreadsheet. We can also use it in combination with the histogram to analyze the frequency of data. It gives you an overview of the distribution of data in the excel sheet. It also displays a list of values that occur at different frequencies. A frequency distribution table is a graphical representation of data sets in a spreadsheet. It shows the frequency distribution of each value by grouping them together. You can display it in both graphical and tabular form.
For creating frequency distribution in excel, we need certain things. The basic need is Data Analysis Toolpak. To activate it, you need to go to the Add-Ins option in the developer many tabs. Once you activate it, go to the histogram and choose the data you want to project. But it’s impossible to create a frequency distribution table without the FREQUENCY formula. It forms the basics to create any type of arrangement in the excel sheet.
FREQUENCY(data_array,bins_array).
Two main functions of the frequency formula are:
- Data Array: It counts the set of frequencies used in distribution. If the array value appears zero (Null), then the function of frequency returns an array of zero values.
- Bins Array: It groups the set of array values in the data array. If the bin array values appear zero(Null). It returns all the elements from the data array.
Steps to Create Frequency Distribution in Excel
Creating frequency distribution in excel is quite a simple and easy process. Let’s take an example of employees to score to create the frequency table. Some of the main steps involved are:
- Open Microsoft Excel on your Pc and choose a workbook. You can also create a new workbook, depends on your need.
- Type proper data in the worksheet. Put data into columns using column headers. These columns headers act as labels on the histogram.
- Type upper levels of Bins in separate columns. Write them in increasing order so they don’t overlap numeric ranges.
- Create labels for the upper limits of Bins in the third column.
- Go to the Data Tab in the header interface. Click on the data analysis to arrange your particular data. If you don’t see the Data Analysis option, it’s better to install Data Analysis Toolpak.
- To install Data Analysis Toolpak: Go to Files-options-Add ins and click OK. Chose that Analysis took and it will appear on your header ribbon.
- On the main window, click on the OK option of Histogram.
- Type the location of your data in the Input range filed textbox. Taking the sample you can type “A4:A19”.
- Likewise, type the location of upper limits in the Bin Range textbox. For example, “C5:C8”.
- Select the location where you want the output to appear by clicking on the Output option. Your output will display on your desired location. It can be on the same sheet, a new excel sheet, or even the whole new workbook.
- Go to the Chart Output and click on the OK option. It will locate the histogram chart beside your frequency table.
- Complete! This creates your frequency distribution table in Microsoft Excel.
It depends on you how many Bins you choose in a column. But it’s better to choose 5-10 columns including all lower and upper values.
So, this was all about frequency distribution in excel. I hope you take some positives from this article and it clears all your queries about Microsoft Excel.
