How to Create an Excel Frequency Distribution Table



Creating an Excel frequency distribution table is an effective way to organize large sets of data. Frequency distribution tables are used to categorize and summarize data by displaying information in columns and rows. For example, you can use an Excel frequency distribution table to count how many people within a given population have a certain age or income level.

With the right instructions, it’s easy to set up your own frequency distribution table in Microsoft Excel.

In this tutorial, you’ll learn how to create a frequency distribution table using the “Data Analysis Toolpak” add-on for Excel. We’ll show you an example of how you can input data and generate summary statistics from the frequency distribution table. To get started, make sure that your Excel sheet has gathered any data that you want to include in your analysis. Once all the information is in place, we’ll show you how to input it into Excel and organize it into columns and rows for comparison.

What is Frequency Distribution?

Frequency distribution is a statistical analysis method for organizing and summarizing data. It involves determining the frequency—or the number of occurrences—of different values that are present in a set of data. Frequency distributions are used to ensure that data is organized, making it easier to analyze or interpret. They also make it possible to create tables and charts, which can provide an overview of an entire dataset. You can create an Excel frequency distribution table using Microsoft Excel’s built-in Histogram tool.

A frequency distribution table is typically used for quantitative nominal data sets. With this type of data set, values are classified into groups known as categories or classes. It’s easy to create a frequency distribution table with such types of variables since the categories remain consistent throughout the entire dataset. When creating a frequency distribution table you should define how many classes you want to include by choosing a maximum value and minimum value as well as providing an appropriate class width.

How to Create a Frequency Distribution Table in Excel

Creating a frequency distribution table in Microsoft Excel is a quick and easy way to organize and analyze your data. It allows you to quickly identify patterns and trends in your data, and can help you make more informed decisions.

In this article, we will walk you through the steps of creating a frequency distribution table in Excel:

Gather the Data

To create a frequency distribution table in Excel, you will first need to gather all of the data you wish to analyze. This can be time-consuming if your data set is large, but it’s the necessary initial step for any type of analysis. Assemble your data into one Excel spreadsheet – typically, a series of rows and columns – and save it for future reference.

Next, decide what type of frequency distribution table you want to create. If you want to look at how certain pieces of data appear in your dataset you may choose a simple frequency table or a grouped frequency distribution table. A grouped frequency distribution table is best used when certain values overlap and should be analyzed as a whole.

Once you have decided on the type of frequency table for your project, begin creating the chart by selecting the number data cells or columns that correspond to those values in your Excel spreadsheet. Highlight these cells or columns and then select “Insert Statistics Chart” from the taskbar menu at the top of your screen. Select “Frequency Distribution” as your chart type and click “OK” to continue.

Choose whether you would like Excel to make an ungrouped or grouped frequency distribution table based on its existing class limits. If not using class limits, specify them by indicating minimum value, maximum value and size/number of classes; use even-sized classes for best results. Note: Unchecking “Use Automatic Class Limits” may result in skewed distributions due to too few or too many classes; use with caution! When finished setting class limits click “Ok” to view your created chart!

Enter the Data into Excel

Creating a frequency distribution table in Microsoft Excel can help you identify patterns and understand the distribution of data. Frequency tables are useful to determine the average or mode of a dataset, and they’re especially helpful when you’re dealing with large volumes of data.

In Excel, frequency distribution tables are easy to create using the FREQUENCY function. This function can take multiple columns as arguments and also provide cumulative frequencies if desired. To get started, enter your data into an Excel spreadsheet. If your data is in columns or rows on different sheets, use the INDIRECT function to reference those ranges. You can include up to 255 cells or arrays as arguments in this function, so there’s plenty of flexibility in how you structure your data for analysis.

Once your data is ready for use, you can start creating your frequency table. Input the following formula into an empty cell: =FREQUENCY(data_range,bins_range). This will generate a frequency table from the provided range and bin numbers – that is, the number of intervals that you want to divide the data set into such as “0-10”, “11-20” etc… The output will display both the absolute frequencies (number of observations) for each range as well as their relative frequencies (percentage).

Create a Frequency Distribution Table

Frequency distribution tables are a useful way of displaying data in an easy-to-understand format. They summarize a large amount of information by organizing raw data values into different categories or ranges. You can easily create frequency distribution tables in Excel for any type of data you may have, such as numbers, percentages, or text.

To create an Excel frequency distribution table:

  1. Open your spreadsheet and select the range of cells that contain your data values – one column should contain the value types and one column should contain the frequency counts.
  2. Set up your bins – these are the ranges that will successfully divide your data into separate groups/categories (e.g., 0-5, 6-15). How many you choose is dependent upon how many unique frequencies fall within each bin range. It’s important to set up these bin ranges first so that it’s clear what goes where when you start entering your numbers into the chart.
  3. Go to Data > Data Analysis > Frequency Distribution and enter your selected range of cells in order to begin creating your table – make sure to check “Label Range” under Output Range if you chose this option while setting up your bins earlier on – this ensures that all frequencies are properly labeled with their respective point range (e.g., 0-5).
  4. Hit OK and a frequency distribution table should be created in a new worksheet within the same Excel spreadsheet!

Format the Table

To create a frequency distribution table in Excel, begin by setting up an empty table. Input the values for each category into separate columns. If you are using a range of numbers (such as years) instead of labels, it is helpful to create categories with specific intervals so that all values fall into one particular category. For example, if you want to analyze data from the last five years, use five ranges or bins for each year instead of using one row per value.

Once you have determined the number and size of your categories and entered your data points in the first row, fill out the remaining cells with one common formula which can be used to calculate a frequency distribution table quickly. Enter ‘=COUNTIF($A$1:A1,”>=”& E$1)/COUNT($A$1:$A$25)’ into cell F2 and then copy this formula down column F until all of your categories are evaluated. This formula will return the percentage of data points within each interval that fall within that interval.

Next, fill out the G column by copying the same formula from column F; only adjust ‘F’ to ‘G’ for each cell entry in column G. This will give you a cumulative total which adds up each prior category value together until it reaches 100%. To finish formatting your frequency distribution table within Excel, add black borders around every cell in columns F and G as well as bold text for any headings.


As you have seen, Excel’s functions allow you to quickly and accurately create frequency distribution tables. You can then use the results of your analyses to generate descriptive statistics or make predictions about future data sets. The flexibility of Excel’s functions makes it an excellent choice for researchers and business decision makers alike.

Now that you know how to work with the Frequency command, try creating some additional frequency tables with other variables from the same dataset. You can also try varying bin sizes and widths to get different outputs from the same data set. Finally, if you are just starting out with frequencies and distributions, you may want to consider using a statistical software package instead of Excel; many of these packages offer additional features for working with frequency distributions that may be more flexible than what Excel provides.