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.
The syntax for the FREQUENCY() function in Google Sheets (and similarly in Excel) is:
FREQUENCY(data_array, bins_array)
Where:
Follow the steps below to easily create a frequency table in Google Sheets.
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.
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.
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.
Follow the steps below to make a frequency distribution table in Google Sheets.
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.
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".
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.
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.
Follow the steps below to make a frequency table in Google Sheets using the COUNTIF() function.
Place your data in a single column, for instance, in column A from A2 to A21. This method is practical for our dataset size.
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.
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.
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.
