How To Make Frequency Table In Excel

To make a frequency table in Excel, you can use the COUNTIF function to count the occurrences of values in a dataset and create a table with the values and their corresponding frequencies.

Excel is a powerful tool that offers a multitude of features for data analysis and manipulation. One such feature is the ability to create frequency tables, which help you understand the distribution of values in a dataset. Frequency tables provide a convenient way to summarize data and gain insights into patterns or trends. In this blog post, we will explore how to make a frequency table in Excel, step by step. Whether you are a beginner or an experienced user, this guide will equip you with the skills to effectively analyze your data and make informed decisions. So, let’s dive in and learn how to harness the power of frequency tables in Excel!

How To Make Frequency Table In Excel: Step-by-Step


Step 1: Prepare Your Data,

Once you have compiled the data you wish to analyze, ensure it is structured in a single column or row within an Excel sheet. Additionally, determine the specific categories or ranges you intend to group this data into to construct an informative and concise frequency table.

Next Step

Step 2: Define Bins,

Bins are essential for organizing data into specific ranges or categories. They act as markers that determine the upper limit for each group. For instance, when evaluating test scores from 0-100, bins of 10, 20, 30, and so on up to 100 are created to group the data efficiently.

Next Step

Step 3: Use the FREQUENCY Function,

To create a frequency table using the FREQUENCY function, select the cell next to the first bin and enter “=FREQUENCY(“, followed by selecting your data set and bins. Press CTRL+SHIFT+ENTER.

Next Step

Step 4: Review Your Frequency Table,

This method allows you to display all the frequency values in the output by selecting the first cell and a range of cells equal to the number of bins. By pressing F2 and then CTRL+SHIFT+ENTER, Excel will populate the selected range with the frequency values.

Next Step

Step 5: Label Your Table,

To enhance clarity, it is advisable to add labels to the frequency table. Label the column displaying the bins or categories as ‘Bins’. Additionally, tag the column showcasing the frequency as ‘Frequency’. This labeling approach aids in better comprehension of the table, making it more user-friendly.

Next Step

Step 6: Validate Your Results,

After labeling your table, verify it accurately reflects your data. Confirm the frequency column’s total equals the count of data points. Comparing the frequency table with a graph or visualization of the original data can aid in ensuring consistency.


In conclusion, creating a frequency table in Excel is a simple and effective way to analyze and summarize data. By following the step-by-step process outlined in this blog post, you can easily transform a long list of data into a clear and organized table that provides valuable insights.

Using Excel’s built-in functions, such as COUNTIF and PivotTable, allows you to calculate the frequency of occurrences for each unique value in a dataset. This enables you to identify patterns, trends, and outliers that may otherwise go unnoticed.

Whether you are working with sales data, survey results, or any other type of information, frequency tables provide a powerful tool for data analysis. The flexibility and functionality of Excel make it a perfect platform for creating and manipulating these tables.

By understanding how to make a frequency table in Excel, you can unlock the full potential of this versatile software for your data analysis needs. So, go ahead and start creating your own frequency tables in Excel to gain valuable insights and make informed decisions.

Table of Contents