How To Create A Frequency Table In Excel

To create a frequency table in Excel, you need to organize your data in a single column, select the column, go to the “Data” tab, click on the “Data Analysis” option, choose “Histogram” and provide the input range and bin range.

In today’s data-driven world, Excel proves to be an indispensable tool for organizing and analyzing information. One fundamental task in data analysis is creating a frequency table, which allows us to gain insights into the distribution of values in a dataset. Whether you are a student dealing with survey responses or a professional managing sales data, understanding how to create a frequency table in Excel is a skill that will undoubtedly enhance your ability to make informed decisions based on data. In this blog post, we will explore the step-by-step process of creating a frequency table in Excel, empowering you to efficiently summarize and analyze your data like a pro. So, let’s dive in and learn how to harness the power of Excel to unlock valuable insights from your datasets.

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


Step 1: Open Excel Document,

To create a frequency table in Excel, start by opening the Excel document where you want to analyze your data. This table will help you summarize and organize the frequencies of different values or categories within your dataset.

Next Step

Step 2: Highlight Required Data,

Click and drag your cursor over the cells containing the data you want to analyze. This will enable you to create a frequency table, which shows the number of occurrences for each unique data value.

Next Step

Step 3: Click on ‘Data’ Tab,

At the top of the Excel window, click on the ‘Data’ tab. This tab is located alongside other tabs like ‘Home’ and ‘Insert’. By clicking on the ‘Data’ tab, a dropdown menu will appear, providing you with various data-related options and tools.

Next Step

Step 4: Select ‘Data Analysis’,

From the ‘Data’ dropdown menu, select ‘Data Analysis’. This will open a new window where you can perform various statistical and data manipulation tasks to gain insights and make informed decisions based on your data.

Next Step

Step 5: Choose ‘Histogram’,

In the ‘Data Analysis’ window, scroll down and select ‘Histogram’ then click ‘OK’. This action triggers the opening of the ‘Histogram’ window where further analysis and visualization of the data can be performed.

Next Step

Step 6: Define Input Range,

Once you open the ‘Histogram’ window, simply select the ‘Input Range’ box and then click on the specific data range that you’ve already highlighted in your Excel sheet. The range will automatically be populated in the ‘Input Range’ box for your convenience.

Next Step

Step 7: Define Bin Range,

The ‘Bin Range’ in Excel allows you to specify the intervals in which you want to group your data. By selecting the appropriate cells in your sheet, you can define the desired bins. If no ‘Bin Range’ is set, Excel will automatically create evenly distributed bins based on the minimum and maximum values of your input data.

Next Step

Step 8: Choose Output Range,

By selecting the ‘Output Range’ option, you can specify the location in your spreadsheet where you want to place the generated table. It is recommended to choose a cell that is outside your data range but close enough for easy analysis.

Next Step

Step 9: Select ‘Chart Output’,

By selecting the ‘Chart Output’ option, you can choose to generate a histogram chart alongside the frequency distribution. This visual representation enhances the understanding of data trends and patterns, making it easier to analyze and interpret the results.

Next Step

Step 10: Click ‘OK’,

Installing the Data Analysis Toolpak is essential for generating a frequency table in Excel. If the ‘Data Analysis’ option is not visible, it can be installed by accessing the ‘Options’ under the ‘File’ menu, selecting ‘Add-Ins’, checking ‘Excel Add-ins’ in the window that appears, and then enabling the ‘Analysis Toolpak’. A restart of Excel may be required.


In conclusion, creating a frequency table in Excel is a simple and efficient way to organize and summarize data. By following the step-by-step process discussed in this blog post, you can easily turn raw data into meaningful insights. Utilizing the built-in features of Excel, such as the PivotTable tool and COUNTIF function, allows you to generate accurate frequency counts and percentages, providing a clear picture of the distribution of your data. The ability to manipulate and analyze data using Excel’s powerful capabilities empowers you to make informed decisions and identify patterns and trends. So, whether you are dealing with survey results, sales figures, or any other type of data, knowing how to create a frequency table in Excel is a valuable skill that will enhance your data analysis capabilities. Start applying these techniques in your own Excel spreadsheets and unlock the potential of your data today.

Table of Contents