How to Make a Frequency Table in Google Sheets (2024 Update)

Frequency Table in Google Sheets

A frequency table in Google Sheets is a tool used to display the frequency of various outcomes in a dataset. It counts how many times each unique value occurs and organizes this information into a table format.

‍

You can create a frequency table using methods such as formulas, pivot tables, or Google Sheets' built-in functions like FREQUENCY(). We will discuss the step by step process for each method in the following sections.

FREQUENCY Syntax

The syntax for the FREQUENCY() function in Google Sheets (and similarly in Excel) is:

‍

FREQUENCY(data_array, bins_array)

‍

Where:

‍

• data_array is the array or range containing the dataset for which you want to calculate frequencies.
• bins_array is the array or range that defines the intervals (bins) for which you want to count the frequencies. Each bin in the bins_array represents the upper limit for that bin.

How to Make a Frequency Table in Google Sheets Using the ‘FREQUENCY’ Function

Follow the steps below to easily create a frequency table in Google Sheets. 

1. Organize Data and Create Bins for Satisfaction Ratings

Ensure your data is in one column, say column A (A2:A21 for our 20 responses).

Create another column for "bins," which in this case are the categories of satisfaction ratings from 1 to 5. Since we're using a scale of 1 to 5, your bins will end at 1, 2, 3, 4, and 5. Place these bins in another column, say B2:B6.

‍

2. Apply FREQUENCY() to Calculate Response Frequencies

In a new column next to your bins, say C2, type '=FREQUENCY(A2:A21, B2:B6)'. Since Google Sheets automatically applies array formulas, simply press Enter.

This will fill down the frequencies of each bin.

‍

3. Add Labels for Satisfaction Ratings and Frequency Counts

Add labels above your bins and frequency counts. For example, label the bins column as "Satisfaction Rating" and the frequency column as "Frequency". This labels the satisfaction scale and how many responses fell into each category.

How to Create a Frequency Table in Google Sheets Using Pivot Tables

Follow the steps below to make a frequency distribution table in Google Sheets. 

1. Highlight Dataset to Initiate Pivot Table Creation

Click any cell within your dataset, ensuring it includes headers. For our example, the dataset is in A1:A21, with "Satisfaction Rating" as the header in A1.

2. Create Pivot Table for Analyzing Satisfaction Ratings

Go to Insert > Pivot table. Choose whether you want the pivot table in a new sheet or an existing sheet.

Select "New sheet" then click "Create".

3. Set Up Pivot Table to Count Satisfaction Rating Frequencies

In the pivot table editor, drag the "Satisfaction Rating" to both the Rows and Values areas.

After dragging the "Satisfaction Rating", it should look like this.

For the Values, change the summarization from "SUM" to "COUNTA" to count the occurrences of each rating.

4. Customize Pivot Table Layout for Enhanced Clarity

Customize the layout and format of your pivot table for better clarity. You can rename the pivot table headers if necessary to make them more descriptive, such as "Rating" for rows and "Count" for values.

How to Make a Frequency Distribution Table in Google Sheets Using 'COUNTIF()'

Follow the steps below to make a frequency table in Google Sheets using the COUNTIF() function. 

1. Arrange Data in Single Column for Manual Frequency Calculation

Place your data in a single column, for instance, in column A from A2 to A21. This method is practical for our dataset size.

2. Identify and Record Each Unique Satisfaction Rating Once

Next to your data, in a new column (say B2:B6), manually list each unique satisfaction rating from 1 to 5. This is a manual approach to identifying unique values.

3. Utilize COUNTIF() to Determine Each Rating's Frequency

Next to each unique value in column B, use 'COUNTIF()' in column C to find its frequency in column A. For a value in B2, the formula in C2 would be '=COUNTIF(A:A, B2)'.

Drag this formula down through C6.

4. Label Columns and Adjust Formatting for Table Readability

Add headers above your columns, such as "Rating" in B1 and "Frequency" in C1, and adjust the formatting for easier reading. Your frequency table is now complete and ready to analyze.

We hope that you now have a better understanding of how to make a frequency table in Google Sheets. If you enjoyed this article, you might also like our article on how to connect Google Sheets to HubSpot or our article on how to add arrows in Google Sheets.