How to Create a Frequency Chart in Excel

A frequency chart shows how often values appear within a dataset — how many students scored between 70 and 80, how many orders fell in a certain price range, how many support tickets arrived each day. Excel has more than one way to build these, and the right approach depends on your data structure, your Excel version, and what you want the chart to do.

What a Frequency Chart Actually Shows

Before building anything, it helps to be clear on what "frequency" means in this context. A frequency distribution groups raw data into bins (ranges or categories) and counts how many data points land in each one. The result is usually displayed as a histogram — a bar chart where each bar represents a bin, and the height shows the count.

This is different from a regular bar chart. In a standard bar chart, each bar is a category you define explicitly. In a frequency chart, Excel (or you) defines the ranges, and the chart shows the distribution of your data across those ranges.

Method 1: Using the Built-In Histogram Chart (Excel 2016 and Later)

If you're on Excel 2016 or newer, this is the fastest route. 📊

Steps:

1. Enter your raw data in a single column — no need to pre-sort or group it.
2. Select the data range.
3. Go to Insert → Charts → Insert Statistic Chart → Histogram.
4. Excel will automatically calculate bins and plot the frequency distribution.

Once the chart is created, you can customize the bins:

• Right-click the horizontal axis → Format Axis
• Under Axis Options, adjust:
  • Bin width — the size of each range interval
  • Number of bins — how many groups you want
  • Overflow/Underflow bin — capture outliers above or below a threshold

This method is quick, but it gives you less control over exactly how bins are calculated compared to doing it manually.

Method 2: Using the FREQUENCY Function

The FREQUENCY function gives you more control and works in older Excel versions too. It calculates how many values fall into each bin you define yourself.

Steps:

1. Enter your raw data in one column (e.g., A2:A100).
2. In a separate column, enter your bin boundaries — the upper limit of each range (e.g., 10, 20, 30, 40).
3. Select a blank range of cells one row taller than your bin list — this is where the frequency counts will go.
4. Type =FREQUENCY(A2:A100, C2:C5) (adjust ranges to match your data).
5. Press Ctrl + Shift + Enter to enter it as an array formula (in older Excel). In Excel 365 or Excel 2021, it spills automatically — just press Enter.

The output gives you a count for each bin, plus a final count for any values above the highest bin.

Then build the chart:

1. Select your bin labels and frequency counts.
2. Go to Insert → Column or Bar Chart → Clustered Column.
3. Adjust gap width to 0% (Format Data Series → Gap Width) to make it look like a true histogram.

Method 3: Using the Data Analysis ToolPak

For users doing statistical analysis, Excel's Analysis ToolPak add-in includes a dedicated Histogram tool.

Enable it first:

• Go to File → Options → Add-ins → Manage: Excel Add-ins → Go
• Check Analysis ToolPak and click OK

Then use it:

1. Go to Data → Data Analysis → Histogram
2. Set your Input Range (your data) and Bin Range (your boundary values)
3. Check Chart Output to generate the histogram automatically
4. Choose an output location

This method produces both a frequency table and a chart in one step, which is useful for reporting or when you need the raw counts visible alongside the visual.

Key Variables That Affect Your Approach

Customizing Your Frequency Chart

Once built, a few adjustments make frequency charts more readable:

• Remove gaps between bars — frequency distributions should have touching bars to visually represent continuous data ranges
• Label your bins clearly — "0–10", "11–20" is clearer than just "10", "20"
• Add data labels — showing the count above each bar helps readers without needing to cross-reference a table
• Use a consistent color — unlike categorical bar charts, frequency charts typically use a single color since the bars represent one continuous variable

Where the Results Diverge

Two users working with the same raw data can end up with very different-looking charts depending on how they define their bins. Narrower bins reveal more detail but can look jagged. Wider bins smooth out the distribution but may hide important patterns — like a bimodal distribution where data clusters around two separate peaks.

There's no universally correct bin size. Statisticians use rules like Sturges' formula or the square root of n as starting points, but what works depends on your data spread and what you're trying to communicate.

Someone analyzing 20 survey responses needs a completely different bin structure than someone charting 10,000 transaction values. The shape of your distribution, the range of your data, and your audience's familiarity with the subject all shape which settings produce a genuinely useful chart versus one that's technically correct but misleading.

What your data looks like — and what decisions it needs to support — is what determines which of these methods, and which configuration within that method, actually fits.