How To Create A Frequency Distribution In Excel

To create a frequency distribution in Excel, you need to input the data in a column, apply a formula or use the Data Analysis Toolpak, and choose the desired output range.

In today’s data-driven world, analyzing and organizing data effectively is crucial. One widely used method in data analysis is creating a frequency distribution, which allows us to understand the distribution and patterns of data values within a dataset. While there are several tools available for this task, Microsoft Excel offers a user-friendly and versatile platform to effortlessly create frequency distributions. Whether you are a business professional, a student, or just someone looking to gain insights from data, this blog post will guide you through the process of creating a frequency distribution in Excel. So, let’s dive in and discover how you can leverage Excel’s features to gain a deeper understanding of your data!

How To Create A Frequency Distribution In Excel: Step-by-Step


Step 1: Setup the Data Set

To create a frequency distribution in Excel, start by organizing your data set in one column. Let’s take the example of analyzing a list of ages. Enter the ages in a single column, ensuring that each age is in a separate cell.

Next, select an empty column adjacent to your data set. This is where your frequency distribution will be displayed. Now, go to the “Formulas” tab in Excel and click on “More Functions” in the “Function Library” group. From the drop-down menu, select “Statistical” and click on “FREQUENCY.”

In the “Data Array” field, select the range of cells containing your data set (ages). Then, in the “Bins Array” field, select the cells corresponding to the intervals or bins you want to group your data into. For example, if you want to group the ages into intervals of 10 (e.g., 0-9, 10-19, 20-29, and so on), enter the starting values of each interval in separate cells.

Finally, press Ctrl + Shift + Enter to create the frequency distribution array. This will populate the selected range with the frequency count for each interval.

To make the frequency distribution more visually appealing, you can insert a bar chart. First, select the range of cells that contain both the interval boundaries and the frequency counts. Then, go to the “Insert” tab, click on “Bar,” and select the chart type you prefer.

The bar chart will give you a visual representation of the frequency distribution, making it easier to interpret the data. You can further customize the chart by adding titles, axis labels, and adjusting the formatting to suit your needs.

Using Excel, you can easily create frequency distributions to analyze and summarize data across various categories, helping you identify patterns and trends.

Next Step

Step 2: Define the Frequency Bins

Frequency bins are numerical divisions used to categorize data in specific intervals. These intervals, or “bins,” can be determined based on your desired grouping. You have flexibility in choosing the size of these bins, such as grouping ages into decades like 0-10, 10-20, 20-30, and so on, with 10 being the bin size.

Next Step

Step 3: Create Bins Column

In the new column next to the dataset, include the upper limit of each frequency bin. This binning should start from the minimum number, incrementing by the interval you have determined until reaching the desired upper limit (e.g., 40).

Next Step

Step 4: Use the FREQUENCY Function

The FREQUENCY function in Excel calculates the frequency of a value in a dataset or array, making it easier to analyze and understand data. By simply selecting the data range and bin range, you can quickly generate a frequency distribution.

Next Step

Step 5: Input Array Formula

The FREQUENCY function is an array formula that requires input as such. To create an array formula, select the ranges and press Ctrl + Shift + Enter, wrapping the FREQUENCY formula in braces {}.

Next Step

Step 6: Drag the Formula Down

Once you’ve entered the array formula, the frequency of the first bin will appear as a number. Click on this cell, find a blue box in the bottom right corner, and drag it down to populate the frequency distribution column with corresponding frequencies for each bin.

Next Step

Step 7: Label Your Frequency Distribution Column

Click on the cell directly above your frequency distribution table and enter a header such as “Frequency” to label the column. This will help to organize and identify the data in your table more effectively, facilitating analysis and understanding.

Next Step

Step 8: Analyze Your Frequency Distribution

This comprehensive frequency distribution provides a solid foundation for further analysis. Consider visualizing the data using a histogram or bar chart to gain valuable insights and effectively communicate your findings.


In conclusion, creating a frequency distribution in Excel is a simple yet powerful way to analyze and understand data. By organizing data into distinct categories and counting the number of occurrences, a frequency distribution provides valuable insights into the distribution and patterns within a dataset.

Excel offers several features and functions that make it easy to create and visualize frequency distributions. From using the COUNTIF function to categorize data, to utilizing charts and pivot tables for graphical representations, Excel provides a wide array of tools to analyze data with ease and efficiency.

Creating a frequency distribution not only helps in understanding the distribution of data but also aids in identifying outliers, trends, and patterns. Whether you are working with sales data, survey results, or any other dataset, Excel’s flexibility and functionality enable you to gain valuable insights and make data-driven decisions.

By following the step-by-step guide in this blog post, you now have the knowledge and skills to create a frequency distribution in Excel. So go ahead and start exploring your data, uncovering hidden patterns, and gaining valuable insights that can drive your business or research forward.

Remember, Excel is an incredibly powerful tool, and understanding how to create a frequency distribution is just the beginning. Continuously learning and exploring Excel’s features and functions will empower you to become a data analysis expert and unlock the full potential of your data.

So, don’t hesitate – dive into Excel, create your frequency distributions, and unlock the power of data analysis!

Table of Contents