In this article:

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).

frequency table google sheets

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.

how to make a frequency table in google sheets

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.

how to make a frequency table in google sheets

This will fill down the frequencies of each bin.

how to create a frequency table in google sheets

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 make a frequency distribution table in google sheets

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.

Automate repetitive tasks with Lido

Save hours on repetitive and tedious work. Lido is a new spreadsheet that connects your spreadsheets, forms, PDFs, and email inbox.

Trigger
Action
Select trigger
When a cell value in Google Sheets changes
Check Google Sheets for today's date
When a new row is added to Google Sheets
On new Google Form submission
Send me a daily reminder
On new Typeform submission
When a cell value in database changes
Check database for today's date
When a new row is added in database
When a new HubSpot customer is created
Select action
ADDCALENDAREVENT
Creates a one- or multiple-day calendar event with optional attendees
ADDHUBSPOT
Adds an object to Hubspot
ADDSLACKCHANNEL
Create a Slack channel, and optionally add a topic or members
CALLURL
Makes any HTTP request
CREATEGOOGLEDOC
Replaces [@column_name] values in a Google Doc with the corresponding table row's values
CREATEPDF
Replaces [@column_name] values in a Google Doc with the corresponding table row's values, then export it as a PDF
FETCH
Makes any HTTP request and returns the response
INSERTROWS
Inserts given array below defined values in given worksheet
SENDGMAIL
Sends an email using your Google account
SENDOUTLOOK
Sends an email using your Microsoft Outlook account
SENDSLACK
Sends a Slack message
SENDSMS
Sends an SMS message using your Twilio account
UPDATECELL
Updates cells with given values
UPDATEHUBSPOT
Updates a property of a Hubspot object
Trigger is required
Action is required
Get started