How to Make a Frequency Graph in Excel

A frequency graph is one of the best ways to understand the distribution of your data at a glance, showing you which values are common, which are rare, and where the peaks and valleys are. Instead of staring at a wall of numbers, you get a simple visual story. This guide will walk you through exactly how to create a frequency graph (also called a histogram) in Excel, from simple data preparation to customizing your chart for a professional look.

Graphed

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.

Build Your First Agent

What Exactly is a Frequency Graph (and Why Bother?)

Imagine you have a list of test scores for 100 students. A frequency graph, or histogram, takes those scores and groups them into ranges. For example, it might count how many students scored between 90-100, how many scored between 80-89, and so on. Each of these groups, or "bins," is represented by a bar on the graph. The height of the bar shows the frequency - the number of students who fell into that range.

This is incredibly useful for spotting patterns you'd otherwise miss. You can instantly see:

• The most common outcomes: The tallest bar shows the range where most of your data points fall.
• The shape of your data: Is it a bell curve? Skewed to one side? Spread out evenly?
• Outliers: Are there isolated bars far away from the main group?

You can use this for anything from analyzing customer purchase amounts in Shopify to tracking website session durations from Google Analytics or viewing response times from a customer survey.

Step 1: Get Your Data Ready for Excel

Before you build your graph, your data needs to be organized correctly. All you need is a single column of numerical data points. For this tutorial, we'll use a sample dataset of 50 customer survey ratings, measured on a scale of 1 to 100.

Your spreadsheet should look something like this, with all your raw data in one column:

(Imagine a screenshot here showing an Excel sheet with column A titled "Survey Rating" and 50 rows of numbers ranging from 1 to 100.)

Having clean, organized data is the most important first step. Make sure your column doesn't contain any text or errors, as this can prevent Excel from creating the chart.

AI Agents for Marketing

Crash Course

Graphed

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.

Download the PDF

Step 2: Create a Frequency Graph in Modern Excel (The Easy Way)

If you're using Excel 2016 or a newer version (including Microsoft 365), creating a histogram is a simple, built-in feature. This is the fastest and most highly recommended method.

1. Select Your Data

Click and drag to select the entire column of your raw data, including the header. In our example, you would select cell A1 down to the last survey rating in the column.

2. Insert the Histogram Chart

With your data selected, navigate to the Insert tab on the Excel ribbon. In the "Charts" section, find and click the "Insert Statistic Chart" icon (it looks like a small blue bar chart). From the dropdown menu, select Histogram.

(Imagine a screenshot here showing the Excel ribbon's Insert tab with the Statistic Chart dropdown menu highlighted, pointing to the Histogram option.)

3. Review Your Initial Graph

Excel will instantly generate a frequency graph on your spreadsheet. It automatically analyzes your data and creates its own bins (the ranges on the horizontal axis). While quick, these automatic bins might not be perfect for your specific needs, so the next step is to customize them.

(Imagine a screenshot here of the initial, automatically generated Excel histogram with default bins like [1, 18.2], [18.2, 35.4], etc.)

Step 3: Customize Your Frequency Graph for a Clearer Story

The default chart is a great start, but a few tweaks will make it much easier to read and interpret. This is where you can refine the bins and add professional touches.

Graphed

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.

Build Your First Agent

Fine-Tune Your Bins

The ranges Excel chooses might be awkward, like "[1, 18.2]". You probably want cleaner ranges, like 1-10, 11-20, etc. Here's how to fix it:

1. Right-click on the horizontal axis (the numbers at the bottom of your chart).
2. Select Format Axis from the menu. A new "Format Axis" panel will appear on the right side of your screen.
3. Under "Axis Options," you'll see several choices for your bins:

• Bin Width: This is the most common and useful option. You can set the size of each interval. For our survey example, let's enter 10. This will group our data into ranges of 1-10, 11-20, 21-30, and so on.
• Number of Bins: Alternatively, you can tell Excel you want a specific number of bars (e.g., 5 bins), and it will calculate the width for you.
• Overflow Bin: Check this box to group all values above a certain number into one final bar. For example, you could enter 90 to lump all ratings of 90 and above together.
• Underflow Bin: Similar to the overflow, this groups all values below a certain number into one first bar.

(Imagine a screenshot showing the "Format Axis" pane with the "Bin Width" option highlighted and set to 10.)

Add and Format Chart Titles and Labels

An untitled graph is just a picture. Descriptive titles and labels give it meaning.

1. Add Chart Elements: Click anywhere on your chart. A green "+" icon will appear in the top-right corner. Click it to open the "Chart Elements" checklist.
2. Chart Title: Check the box for "Chart Title" if it's not already there. Double-click the title on the chart to edit it. Give it a clear name, like "Distribution of Customer Survey Ratings."
3. Axis Titles: Check the box for "Axis Titles." This will add text boxes to the horizontal (X) and vertical (Y) axes. Label your Y-axis "Frequency" or "Number of Customers" and your X-axis "Survey Rating."
4. Data Labels: Check "Data Labels" to display the exact count on top of each bar. This is a great way to show the precise frequency for each bin without making viewers guess.

After these customizations, your chart will be much more readable and professional.

(Imagine a screenshot of the final, polished histogram with a clear title, axis labels, data labels, and logical bins of width 10.)

The Alternative Method: Using the FREQUENCY Function

Before Excel 2016, or if you need more manual control over your data, you can build a frequency graph using a combination of the FREQUENCY function and a standard column chart. It's a few more steps, but an excellent skill to have.

1. Define Your Bins Manually

In a separate column, list the upper limit for each bin you want to create. For our survey data, you'd create a column called "Bins" and enter 10, 20, 30, 40, 50, 60, 70, 80, 90, 100.

(Imagine a screenshot showing the original data in Column A and a new "Bins" column with the numbers 10, 20...100 listed out.)

AI Agents for Marketing

Crash Course

Graphed

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.

Download the PDF

2. Use the FREQUENCY Function

The FREQUENCY function is a special array formula that calculates how many values fall within each of your specified bins. Here's how to use it:

1. Select the empty cells right next to your "Bins" column where you want the counts to appear. Be sure to select one more cell than you have bins, as the function will also count any values above your highest bin limit.
2. With the cells selected, type the following formula:

=FREQUENCY(A2:A51, C2:C11)

(Adjust the ranges to match your data. A2:A51 is our raw data, and C2:C11 is our manually created bin list). 3. To enter the formula, press Ctrl + Shift + Enter. (In newer versions of Excel with dynamic arrays, you may only need to press Enter). You'll see the frequency counts populate instantly for each bin.

3. Create a Clustered Column Chart

Now that you have your bins and frequencies calculated, you can create a simple bar chart:

1. Select both your "Bins" column and your new "Frequency" column.
2. Go to the Insert tab and choose a 2-D Clustered Column Chart from the Charts area.

4. Make It Look Like a Histogram

A standard column chart has gaps between the bars, but a true histogram does not. To fix this:

1. Right-click on one of the bars in your chart and select Format Data Series.
2. In the "Format Data Series" pane, find the "Gap Width" slider and set it to 0%. This will make the bars touch, creating a proper histogram look.

From here, you can add chart titles, axis labels, and data labels just as we did in the previous method to finish your graph.

Final Thoughts

Creating a frequency graph is a fundamental Excel skill for anyone who works with data. By visualizing the distribution of your numbers, you can uncover key trends in seconds, whether you use the quick built-in Histogram feature or the more traditional formula-based method. It's all about turning a confusing set of data into a story that's easy to tell and understand.

Of course, building these reports in Excel often starts with manually pulling CSVs from different platforms like Google Analytics, Shopify, or Salesforce — a process that can be time-consuming. At Graphed, we automate all of that. You can connect your marketing and sales data sources with a single click and then use simple, plain English to ask for the charts and dashboards you need. Instead of wrestling with spreadsheet functions, you can just ask, "Show me a graph of our Shopify order values this month," and get a live, interactive dashboard instantly.