Graphed LogoGraphed
Graphed LogoGraphed
How It WorksAgentsIntegrationsPricing

How to Make a Frequency Chart in Google Sheets

Cody Schneider••6 min read

Counting how often a specific item or value appears in your dataset is a fundamental task in data analysis. A frequency chart is the perfect way to visualize this information, giving you a quick, clear view of the most and least common entries in your data. This article will show you exactly how to transform your raw data into an insightful frequency chart using a few different methods in Google Sheets.

GraphedGraphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Watch Graphed demo video

What is a Frequency Distribution and Why is it Useful?

Before building the chart, you first need to create a frequency distribution. Don't let the name intimidate you, it's simply a table that summarizes how many times each unique value appears in your dataset. Imagine you run a t-shirt business and have a long list of every single shirt sold in the past month.

  • The raw data is just a jumble of sizes: "M", "L", "M", "S", "M", "XL", "L"...
  • A frequency distribution table would summarize that data neatly:

This simple summary is incredibly powerful. It helps you quickly spot patterns and trends ("Medium is our best-selling size!"), identify outliers or errors ("Why do we have a few sales for XXS? Is that a data entry mistake?"), and provides the foundation for building a visual chart.

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Step 1: Get Your Data Ready

The first step is always preparing your data. For a frequency chart, you need a single column containing the raw data you want to analyze. This list could be anything from customer survey text responses, product categories, or numerical data like test scores.

For our main example, let's say we have a list of responses from a customer survey asking, "What is your favorite feature of our service?" Our data is a single column in Google Sheets (column A) that looks something like this:

  • Speed
  • Support
  • Design
  • Speed
  • Integration
  • Support
  • Speed
  • ...and so on for a hundred rows.

Once your column of data is ready, you can choose one of the following methods to create your frequency table.

Method 1: Creating a Frequency Table with COUNTIF

This manual approach uses a combination of formulas to build your summary table. It's a great way to understand exactly what's happening behind the scenes.

1. Create a List of Unique Values

First, we need to generate a clean list of all the unique categories from our raw data. The easiest way to do this is with the UNIQUE function.

  1. Click on an empty cell where you want your list to begin (e.g., C2).
  2. Type in the following formula:
  3. Press Enter. Google Sheets will automatically populate a list of the unique feature names from your data range (Speed, Support, Design, etc.).

Note: Make sure to adjust the range A2:A101 to match the actual range of your data.

2. Count Each Item with COUNTIF

Now that you have your list of unique items, you can count the occurrences of each one using the COUNTIF function. This function counts cells in a range that meet a specific criterion.

  1. In the cell directly to the right of your first unique value (e.g., in D2, next to "Speed"), type the following formula:
  2. Let's break that down:
  3. Press Enter. You'll see the total count for the first item.
  4. Now, click on the cell with the formula (D2), grab the small blue square (the fill handle) in the bottom-right corner, and drag it down. Google Sheets will automatically apply the formula for each unique item in your list.

You now have a clean frequency distribution table!

GraphedGraphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Watch Graphed demo video

Method 2: The Fastest Way - Using a Pivot Table

For speed and flexibility, nothing beats a Pivot Table. It may seem advanced, but it can create a frequency table in just a few clicks without you having to write a single formula.

  1. Click anywhere inside your column of raw data.
  2. Go to the menu and select Insert > Pivot Table.
  3. A dialog box will appear. It should have already selected your data range correctly. Choose whether you want the pivot table on a "New sheet" or the "Existing sheet" and click Create.
  4. The Pivot table editor will open on the right side of your screen. Now, you just need to tell it what to do:
  5. Check that the "Values" box is set to summarize by COUNTA (for text) or COUNT (for numbers). This is usually the default, but if it says "SUM" or something else, just click on it to change it.

Instantly, your frequency table appears. This method is incredibly efficient, especially for large datasets.

Step 2: Turn Your Frequency Table into a Chart

Once you have your frequency table (created with either the formulas or a Pivot Table), visualizing it is simple.

  1. Highlight the data in your frequency table, including both the category names and their counts.
  2. Go to the menu and click Insert > Chart.
  3. Google Sheets will almost always suggest a Column Chart (a vertical bar graph), which is the perfect choice for displaying frequencies.
  4. The Chart editor will open, allowing you to fine-tune your visualization:

Tips for Making Your Frequency Chart More Effective

A good chart tells a story. Here are a few tips to make your frequency chart easier to understand at a glance.

Sort Your Data

Before creating your chart, sort your frequency table from highest count to lowest count. This directs the viewer's attention to the most significant categories immediately. In a pivot table, you can do this by clicking the sort option in the "Rows" box and choosing to sort by COUNTA of [Your Field] in descending order.

Choose the Right Chart Type

While Column Charts are the standard, a Bar Chart (with horizontal bars) can be a better choice if your category names are long. It gives them more space and prevents them from overlapping or being angled awkwardly. Avoid Pie Charts for displaying frequencies unless you have very few categories (five or fewer), as they become hard to read with more slices.

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Use Clear and Simple Labels

Nobody knows what "COUNTA of Q1_Survey" means except you. Change default titles and axis labels into plain language that anyone can understand. Simple, direct labeling is the key to an effective data visualization.

Final Thoughts

Creating a frequency chart in Google Sheets transforms a simple list into a powerful visual insight, showing you what's most and least common in your data. Whether you use the manual COUNTIF function to understand the mechanics or jump straight to the speed of a Pivot Table, you can easily turn your raw numbers into a clear, actionable chart.

While these Sheets techniques are perfect for one-off analyses, creating and updating these reports across multiple platforms like Shopify, Google Analytics, and Facebook Ads can become a repetitive, time-consuming task. We created Graphed to remove this manual work. You connect your data sources just once, and then you can simply ask questions in plain English like "Show me a bar chart of our top selling products for the last month" or "Count the number of users from Canada vs the US" and get a live, automated dashboard in seconds, no formulas required.

Related Articles