# How to Create a Frequency Table in Excel A Step-by-Step Guide

October 20, 2023 3.5K views

Frequency tables are a great way to organize and analyze data in Excel, but they can be tricky to create if you're not sure how. This article will provide you with a step-by-step guide on how to create a frequency table in Excel, as well as some easy tips to make the process even simpler.

## Part 1. How to Create a Frequency Table in Excel

### Method 1: Using Pivot Table

Step 1: Select the entire dataset containing the values you want to create a frequency table for.

Step 2: Go to the "Insert" tab in the Excel ribbon.

Step 3: From the "Tables" group, choose "PivotTable."

Step 4: In the "Create PivotTable" dialog box, ensure that the correct range is selected in the "Table/Range" field (e.g., B4 to D19).

Choose the option to place the PivotTable on a new worksheet.Click "OK" to create the PivotTable.

Step 5: In the PivotTable Fields pane, drag the field you want to analyze (e.g., "Sales") into the "Values" section.

Step 6: By default, it may show the sum of sales. Right-click on any cell in the "Sum of Sales" column, select "Value Field Settings," and change the calculation to "Count."

Step 9: It will count each occurrence of values.

Step 10: To create groups or bins, right-click on any cell in the "Sales" column, select "Group," and specify the grouping criteria (e.g., grouping by 500).

Step 11: Go to the "Insert" tab, choose "Recommended Charts," and select a column chart to visualize the frequency distribution.

I find this method efficient for summarizing data quickly. If you're already comfortable with Pivot Tables, it's a solid choice.

### Method 2: Utilizing FREQUENCY Function

Step 1: Create two columns for lower and upper range values based on your dataset.

Step 2: In an empty cell (e.g., G5), enter the formula =FREQUENCY(C5:C16, F5:F14) where C5:C16 is your data range, and F5:F14 are the bin values.

Step 3: Press "Ctrl+Shift+Enter" to apply the array formula. Excel will generate frequency counts for each bin.

This method is excellent for straightforward frequency tables, especially for beginners. Pressing Ctrl+Shift+Enter takes a bit to get used to, but it's worth it.

### Method 3: Applying COUNTIFS Function

Step 1: Create a lower range and an upper range for your bins.

Step 2: In cell G5, enter the formula =COUNTIFS(C5:C16, "<=10") to="" count="" values="" less="" than="" or="" equal="" 10.="">

Step 3: Press "Enter" to apply the formula.

Step 4: In cell G6, enter the formula =COUNTIFS(\$C\$5:\$C\$16, ">10", \$C\$5:\$C\$16, "<=20") 10="" to="" count="" values="" greater="" than="" and="" less="" or="" equal="" 20.="">

Step 5: Press "Enter" to apply the formula.

Step 6: Repeat steps 3-4 for other cells to count values in different ranges.

This method is the most balanced choice for beginners.

### Method 4: Use of Data Analysis Tool

Step 1: Enable the Data Analysis Toolpak (File > Options > Add-Ins > Analysis Toolpak).

Step 2: Create a bin range based on your dataset.

Step 3: Go to the "Data" tab in the Excel ribbon.

Step 4: Click on "Data Analysis" in the "Analysis" group.

Step 5: In the "Data Analysis" dialog box, choose "Histogram" from the list of tools.

Step 6: Select the input range (e.g., Sales) and the bin range.

Choose the output options (e.g., "New Worksheet" for results, "Cumulative Percentage," and "Chart Output").

Click "OK" to generate the frequency table and histogram chart.

While setup might take a few extra minutes, the Data Analysis Toolpak is user-friendly and provides excellent frequency tables and histograms. Great for beginners seeking visualization.

Using Pivot Table: Quick and user-friendly, suitable for most users.

Utilizing FREQUENCY Function: Involves formulas, better for those familiar with Excel functions.

Applying COUNTIFS Function: Beginner-friendly and straightforward.

Use of Data Analysis Tool: Offers advanced options for in-depth analysis, better for experienced users.

## Part 2 The Importance of Frequency Tables on Data Analysis in Excel

Frequency tables play a crucial role in data analysis using Excel for several reasons:

1. Summarize Large Amounts of Data Quickly: Excel's frequency tables allow you to efficiently condense extensive datasets. Instead of sifting through rows of raw data, you can instantly see the distribution of values, making it easier to understand the data's overall characteristics.

2. Identify Patterns and Trends: Frequency tables provide a structured view of data, making it simpler to identify patterns, trends, and outliers. By visualizing the distribution of values, analysts can spot recurring themes or irregularities that might go unnoticed when examining raw data.

## Part 3.Best Alternative——WPS Office

WPS Office is a robust office software suite known for its stability and modern design. It offers a wide range of features and advantages that make it an excellent alternative for various office tasks. Some key features and advantages of WPS Office include:

• Text Processing: WPS Office provides powerful text processing capabilities, making it easy to create, edit, and format documents with efficiency.

• Table Production: The software offers tools for creating and managing tables, allowing users to organize data effectively.

• Slide Production: WPS Office includes a comprehensive set of features for designing and presenting slides, making it a valuable tool for creating visually appealing presentations.

• Graphic Image Processing: Users can work with graphics and images within WPS Office, enhancing the visual aspects of their documents and presentations.

• Simple Database Processing: WPS Office provides basic database processing functionality, allowing users to manage data conveniently.

One significant advantage of WPS Office is its stability, ensuring a reliable and smooth user experience. Additionally, it excels in handling Excel files, making it a trustworthy choice for users who rely on Excel's capabilities.

Step 1: Visit the official WPS Office website by going to https://www.wps.com/download/.

Step 2: Choose the version of WPS Office that you want to download, depending on your operating system (Windows, Mac, Linux, Android, or iOS).

Trustpilot4.8
WPS Office- Free All-in-One Office Suite

• Edit PDF files with the powerful PDF toolkit.

• Microsoft-like interface. Easy to learn. 100% Compatibility.

• Boost your productivity with WPS's abundant free Word, Excel, PPT, and CV templates.

5,820,008 User
Algirdas Jasaitis
logo

## FAQs

### How to Calculate Standard Deviation of a Frequency Distribution in Excel?

Calculating standard deviation for a frequency distribution in Excel involves finding the mean, then computing the squared differences for each value from the mean, taking into account their frequencies. Finally, sum these squared differences, divide by the total frequency minus 1, and take the square root to get the standard deviation.

### How to Calculate Cumulative Relative Frequency in Excel?

To calculate cumulative relative frequency in Excel, organize your data, add a column for relative frequencies, and another for cumulative relative frequencies. For each row, sum the relative frequencies from the first row to the current one to compute the cumulative relative frequency. Excel functions like SUM or SUMIFS can assist in these calculations, helping you analyze data distribution over a range.

## Summary

In 2023, creating frequency tables in Excel has become easier than ever. The step-by-step guide outlines various methods, catering to different user preferences. Using Pivot Table stands out as an efficient way to summarize data quickly. For beginners, the FREQUENCY function offers a straightforward approach. COUNTIFS function strikes a balanced choice for simplicity and effectiveness. The Data Analysis Toolpak is user-friendly, providing excellent frequency tables and histograms, making it great for visualization.

While all methods have their merits, it's worth noting that WPS Office is an excellent alternative for various office tasks. It offers a stable and modern design, making it a reliable choice for those who rely on Excel's capabilities. Downloading WPS Office for free is straightforward and can be done through their official website.

15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.