Create Frequency Table in Excel: A Step-by-Step Guide

Photo of author

By Matthew Simpson

Creating a frequency table in Excel can be done in just a few simple steps. First, organize your data in a column or row. Next, use the COUNTIF function to tally how often each value appears. Finally, arrange your results in a table format. After reading this paragraph, you’ll be ready to create your own frequency table with ease.

Step by Step Tutorial: Create Frequency Table in Excel

Creating a frequency table in Excel involves using functions and organizing data effectively. Let’s walk you through how to do it.

Step 1: Organize your data

Make sure your data is organized in a column or row on your Excel worksheet.

Organizing your data makes it easier to create a frequency table because you have all the information you need in one place. This step is crucial, so take your time to ensure your data is accurate and well-arranged.

Step 2: Use the COUNTIF function

Next to your data column or row, use the COUNTIF function to calculate how often each value occurs.

The COUNTIF function is a powerful tool in Excel that can save you a lot of time. It takes two arguments: the range of cells to search and the criteria to search for. For example, =COUNTIF(A2:A10, "Red") would count how many times the word "Red" appears in cells A2 through A10.

Step 3: Arrange your results

After calculating the frequency of each value, input the results into a new table to visualize your data.

This step is about presenting your data in a clear and understandable way. You can use Excel’s built-in table features to create a professional-looking frequency table. Remember to label your columns and rows appropriately.

After completing these steps, you’ll have a comprehensive frequency table that clearly shows the frequency of each value in your data set. This table can be used for further analysis or reporting.

Tips: Excel Frequency Tables

  1. Make sure your data does not contain any duplicates or errors before creating the frequency table.
  2. Use the "Sort" function to organize your data alphabetically or numerically before applying the COUNTIF function.
  3. You can use the "Pivot Table" feature for a more dynamic and customizable frequency table.
  4. Format your frequency table with colors or borders to make it more reader-friendly.
  5. If you have a large data set, consider using the "Advanced Filter" to remove any duplicates before creating your frequency table.

Frequently Asked Questions

What is the COUNTIF function in Excel?

The COUNTIF function in Excel tallies how often a particular value or criteria appears in a specified range of cells.

Can I create a frequency table with non-numeric data?

Absolutely, a frequency table can be created with any type of data, including text-based values.

How can I visualize my frequency table data?

You can use charts or graphs in Excel, such as a bar chart or pie chart, to visually represent the data from your frequency table.

Can I use a frequency table for multiple data sets?

Yes, you can create separate frequency tables for each data set or combine them into one table if the data sets are related.

What should I do if my data is not organized?

Take the time to clean and organize your data first. This will make the process of creating a frequency table much smoother and more accurate.


  1. Organize your data in a column or row.
  2. Use the COUNTIF function to calculate frequency.
  3. Arrange your results in a table format.


Creating a frequency table in Excel may seem daunting at first, but with the right approach and understanding of the functions involved, it can be a straightforward task. The key is to start with well-organized data and become familiar with the COUNTIF function, which is essential for calculating the frequency of each value. Once you have your frequencies calculated, arranging them in a table format will help you to visualize the data and draw meaningful conclusions from it.

Whether you’re a student trying to analyze research data, a business professional examining sales trends, or just someone who loves to organize and make sense of information, mastering the skill of creating frequency tables in Excel will serve you well. It’s a fundamental task that, once learned, can be applied in countless situations to help you make data-driven decisions.

So, what’s stopping you from becoming an Excel frequency table pro? Dive into your data, follow the steps outlined in this article, and start creating frequency tables that can provide valuable insights and inform your next move. Happy analyzing!