Frequency indicates the number of times an event has occurred. A categorical frequency table indicates how many times a specific category occurred in a set of data. This is very helpful for many statistical calculations, like finding out the highest occurring value, least occurring values, and other purposes like depicting frequency distributions. In this article, we will focus on how to make a categorical frequency table in Excel.
What Is Categorical Variable?
In statistics, a categorical variable or qualitative variable is defined as a variable that can take a limited number of fixed values, assigning each value to a particular group. This assigning of variables usually depends on the qualitative property of the variable. It is the variable we use to place data on groups or categories. While the quantitative variable is based on the quantitative property of the variable. For example, the colors of a set of balls, or the breed of an animal is a categorical variable. On the other hand, the population of a city is a quantitative variable, as the quantity is of focus here.
Overview of Categorical Frequency and Categorical Frequency Table
Categorical Frequency is one way to summarize all the categorical variables in question. In brief, frequency means how many times a specific value or variable occurred. This can also be shown for categorical variables. As we can easily observe the number of times a variable has occurred- regardless of whether it is of quantitative or qualitative type.
The categorical frequency table is a classified series of data where the values of the categorial frequency are placed side by side with the categorical variables. The process is usually performed manually by making tallies in statistics. During this, a chart is also added sometimes for better visualization.
How to Make a Categorical Frequency Table in Excel: 3 Easy Ways
To make a categorical frequency table we first need a dataset to gather data from. We are using the following dataset for demonstration here.
This is a list of books- a list of categorical variables listed with the published date in centuries- another set of variables. In this case, you can see some books belong to the same century. To make a categorical frequency table, we will determine how many times a book was published in a particular century.
With this intention, we can use three methods to find the frequency of a particular variable or a set of variables. The first one uses a pivot table to find the frequency and the other two methods use functions for that. Each of the methods is listed in its sub-section. Follow along to see how each works or find the one suitable for you from the table at the top of the page.
1. Using Pivot Table
Using a pivot table is one convenient way to make a categorical frequency table in Excel. Especially, if you don’t want to go into formulas. With this in mind, follow these steps to make a categorical frequency table from our dataset.
Steps:
- First, select the whole dataset.
- Then go to the Insert tab in your ribbon.
- After that, select PivotTable under the Tables group.
- Now, in the box that popped up, select the place where you want to create your pivot table. At this instant, you can select either a new worksheet for the table or the existing one. We are using the existing one and selecting cell E4 for the pivot table to start. After your selections click on OK.
- Next, in PivotTable Fields that popped up on the right of the spreadsheet once you select the pivot table area, click the Published field and drag while clicking to both the Rows and Values.
- As a result, a pivot table will emerge on the spreadsheet.
- Finally, modify the pivot table to your liking.
Steps to Add Chart:
If you want to add a graph to the categorical frequency table you have just made in Excel, follow these steps.
- First, select a cell in the pivot table.
- Then go to the Insert tab on your ribbon.
- After that, select Recommended Charts from the Charts group.
- Next, select the type of chart you want in the Insert Chart Here, we have selected the column chart from the Column tab.
- After clicking on OK a chart will appear depending on the values of the categorical frequency table.
- Finally, modify the graph to make it more presentable.
Read More:Â How to Calculate Percent Frequency Distribution in Excel
2. Applying COUNTIF Function
Instead of using pivot tables, we can use the Advanced Filter option to find unique values from a series of data and then use the COUNTIF function to count the frequency of each category. And thus make a categorical frequency table in Excel.
The COUNTIF function takes two arguments. The first one is a range where it searches for a value in. Where the second argument is the one it searches for. Finally, it returns the number of times the second argument appears on the range.
Follow these steps to make a categorical frequency table in Excel with the help of this function.
Steps:
- First of all, select the column containing the variable you are making a categorical frequency table of.
- Now go to the Data tab on your ribbon.
- Then select Advanced from the Sort & Filter group.
- As a result, the Advanced Filter box will open up. Now select the Copy to another location option as we are making the table in a different space.
- And then select the cell you want to copy to. Make sure you check the Unique records only option.
- Then click on OK.
- Consequently, a column with all the categorical variables will be created.
- Now, select cell F5 and write down the following formula.
=COUNTIF($C$5:$C$16,E5)
- Then press Enter on your keyboard. You will have the frequency of the first value.
- Now, select the cell again and click and drag the fill handle icon to the end of the column to fill the rest of the cells with the formula.
Thus you will have the categorical frequency table in Excel.
Steps to Add Chart:
Follow these steps to make a chart from the categorial frequency table you have just made in Excel.
- First of all, select the table.
- Now go to the Insert tab on your ribbon.
- After that, select Recommended Charts from the Charts group.
- Then, select the type of chart you want in the Insert Chart Here, we have selected the column chart from the Column tab.
- After clicking on OK a chart will appear depending on the values of the categorical frequency table.
- Finally, modify the graph to make it more presentable.
Read More:Â How to Make a Contingency Table in Excel
3. Use of FREQUENCY Function
Similar to the previous method, we can use the Advanced Filter option to make the column for unique values and then use the FREQUENCY function to make a categorial frequency table from it.
The FREQUENCY function takes two arguments- data array and bins array. It searches for the bins array values in the data array range and then returns an array consisting of the number of times each value of the bins array occurred in the former array.
Unfortunately, this function can only be worked around numbers. So we have to use a modified version of our dataset which looks like this.
Follow these steps to know how you can use this function to make a categorical frequency table in Excel.
Steps:
- First of all, select the column containing the variable you are making a categorical frequency table of.
- Now go to the Data tab on your ribbon.
- Then select Advanced from the Sort & Filter group.
- As a result, the Advanced Filter box will open up. Now select the Copy to another location option as we are making the table in a different space.
- And then select the cell you want to copy to. Make sure you check the Unique records only option.
- Then click on OK.
- Consequently, a column with all the categorical variables will be created.
- Now, select the range F5:F8 and write down the following formula.
=FREQUENCY(C5:C16,E5:E8)
- Finally, press Ctrl+Shift+Enter on your keyboard. This will automatically fill up the array and will give us a categorical frequency table.
Steps to Add Chart:
Follow these steps to make a chart from the categorial frequency table you have just made in Excel.
- First of all, select the table.
- Next, go to the Insert tab on your ribbon.
- After that, select Recommended Charts from the Charts group.
- Then, select the type of chart you want in the Insert Chart Here, we have selected the column chart from the Column tab.
- After clicking on OK a chart will appear depending on the values of the categorical frequency table.
- Finally, modify the graph to make it more presentable.
Read More:Â How to Make a Relative Frequency Table in Excel
Things to Remember
- While using the pivot table method, put in the same categorical variable field in both rows and values area.
- Make sure to copy the categorical variable column while using advanced filtering or it will overlap the existing dataset. Thus no frequency table would be possible regardless of the function you use.
- The FREQUENCY function can only be used for numerical values. So make sure your dataset from where you are taking categorical frequency is numerical.
- A range of cells should be selected before entering the FREQUENCY function. The range should be the length of the result array.
- Always use Ctrl+Shift+Enter for functions like the FREQUENCY function where the output is an array.
Download Practice Workbook
Conclusion
These are all the methods you can use to make a categorical frequency table in Excel. Hope this article was helpful and informative for you and you will be able to make categorical frequency tables with ease now. If you have any questions or suggestions, let us know below.
Related Articles
<< Go Back to Frequency Distribution in Excel | Excel for Statistics |Â Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
