A frequency table categorizes and provides a summary of the categories or groups present in a dataset. It’s a tool that provides insight into the elements of the datasets in tabular (or table) form. A frequency table is effectively a bar or column chart in tabular format.

A pivot table is the tool of choice for creating a frequency table in Google Sheets. Not only does it summarize data, but a pivot table also allows you to view data from various perspectives. In this post, we will show you how to create a frequency table from your dataset using a pivot table.

Quick Jump

Toggle## How to Create a Frequency Distribution Table

Our sample data contains product and sales channel information. We want to create a frequency table that shows the total number of products sold as well as the number of products sold in each category.

To create a frequency distribution table for the products, we will follow these steps:

1. Select the **product** column.

2. Go to the **Insert** menu and select **Pivot table**.

3. In the **Create pivot table** window, select the **New sheet** radio button to insert the pivot table into a new sheet or use the **Existing sheet** option to insert the pivot table in the current sheet. Click on **Create** to make the pivot table.

Since we selected **New sheet**, the pivot table preview appears on the new spreadsheet.

Additionally, the **Pivot table editor** will open on the right side of the spreadsheet. In the **Pivot table editor** are two tabs. The first contains the dimensions—**Rows**, **Columns**, **Values**, and **Filters**—that are used to organize data in the pivot table.

The second tab in the **Pivot table editor** displays a list of the selected column(s) containing the data.

4. Add the **product** data to the **Rows** dimension in the pivot table by dragging and dropping from one tab to the other.

You can also use the **Add** option for each dimension (in this case, **Rows**) to select the data you want for the pivot table.

The data in the **product** column will now be categorized in the pivot table preview.

To get the number of each **product** category, drag the **product** data into the **Values** dimension.

Data in the **Values** dimension can be summarized using a number of mathematical operations. That is because, ideally, data in the **Values** dimension is expected to be numeric.

To see the options with which you can summarize the data in the **Values** dimension, click on the dropdown arrow in the **Summarize by** section.

In the pivot table preview, you can now see the number of products available in each **product** category.

With this setup, the pivot table will update each time new data is added to the **product** category column.

## How to Create a Relative Frequency Table

While the frequency table organizes data into categories, a relative frequency table allows you to see the percentage of items in a category relative to the total number of items in that dataset.

Building on the process for creating a frequency table in the previous section, follow these steps to create a relative frequency table:

1. Go to the item in the **Values** dimension and select the dropdown in the **Show as** section.

The options in the dropdown are explained below:

**% of row**: This returns the relative percentage of the sum of all the numbers in the rows.**% of column**: This returns the relative percentage of the sum of all the numbers in the column.**% of grand total**: This returns the relative percentage of the column or row grand total.

Select the **% of column** option to return the relative frequency for the **product** data.

Usually, the relative and categorical frequencies appear together on the same table. If you want to do this, follow these steps:

1. Select the **relative frequency table** as is and copy using **Ctrl** + **C **for Windows or **Cmd **+** C **for Mac.

2. Select any cell in the sheet, right-click on the cell, and select the **Paste special** option. From the contextual menu, click on **Values only**. Alternatively, you can use the shortcut **Ctrl** + **Shift** + **V** (**Cmd** + **Shift** + **V**).

This will create a new table that’s not connected with the pivot table. After pasting the table, change the column header and add borders.

Change the number format in the relative frequency column to percentages. To do this, select the values in the column and click on the **Format as percent** command in the font toolbar.

3. When this is done, go back to the pivot table and select the **Default** option in the **Values** dimension. However, this time copy only the **COUNTA of product** column and **Paste special** as done in the previous step.

After pasting, you can rearrange the table so the **frequency **column comes before the **relative frequency** column. Don’t forget to add borders around the **frequency** column.

This table provides a simple summary of all the product categories, the count (frequency) of each product, and their relative counts in percentages.

## Frequency Tables in Google Sheets: FAQs

### What is a frequency table?

A frequency table is a table that displays the number of occurrences (frequency) of each unique value in a dataset. It is a way to organize and summarize data in a concise and informative manner so that it is easy to understand and analyze. The table typically includes two columns: one for the unique values (the categories or levels in the data) and one for the corresponding frequencies. The values can be arranged in ascending or descending order based on the frequency. A frequency table is also known as a frequency distribution table.

### How do you make a two-way frequency table in Google Sheets?

A two-way frequency table, also known as a contingency table or a cross-tabulation, is a table that displays the frequency (or count) of observations for each combination of two categorical variables.

To create a two-way frequency table, you typically would have two categorical variables. A categorical variable is not numerical.

Earlier, we created a frequency distribution using one column of data. To create a two-way frequency table, follow these steps:

1. Select the **sales_channel** and **product** columns from the sample dataset.

2. Insert a pivot table as described earlier.

3. Drag the **sales_channel** data to the **Columns** dimension and the **product** data to the **Rows** dimension. Also, drag the **product** data to the **Values** dimension.

With this table, you can see an analysis of the number of product categories sold in each **store_channel**, and the total number of sales each **store_channel** made.