How to Draw Frequency Distribution in Excel | Histogram & Table

Frequency distributions in Excel are drawn with the FREQUENCY function, a PivotTable with grouped count, or the Histogram tool in the Analysis ToolPak.

The first job of any data recap is grouping raw numbers so you can see what’s happening—how many sales fell into each price bracket, how many test scores hit each letter grade, or how many visits your site got each hour. Excel has three solid ways to draw a frequency distribution, and the right one depends on whether you need a static table, a grouped interactive report, or a clean histogram chart.

When you learn how to draw frequency distribution in Excel, you pick between these approaches based on your data’s layout and the version of Excel you’re using. The steps below cover each method end to end, including the one common gotcha that trips up most beginners.

What Data Layout Does Excel Expect?

Excel works best when raw observations sit in a single column and bin limits occupy a separate column. A bin limit is simply the upper boundary for each group—if you’re counting ages in decades, your bins might be 29, 39, 49, and so on. The PivotTable method is the one exception: it lets you set intervals directly through a dialog, so you can skip the separate bin column.

Method 1: The FREQUENCY Function (Array Formula)

The FREQUENCY function is the most direct route to a distribution table and works in every modern version of Excel without any add-ins. Its syntax is =FREQUENCY(data_array, bins_array).

To use it:

1. Enter your bin upper limits in a column next to your data.
2. Select a range of cells that is exactly one cell taller than your bins column.
3. Type =FREQUENCY(A2:A100, B2:B10)—adjust the ranges for your actual data and bin cells.
4. Press Ctrl+Shift+Enter in Excel 2019 or earlier, or plain Enter in the current dynamic-array version of Microsoft 365.

The one quirk of the FREQUENCY function is that it returns an array, so trying to enter it as a standard formula with just Enter gives you a #VALUE error or only the first count repeated. When entered correctly, each cell in the output range shows the count of values that fall at or below each bin limit.

Method 2: PivotTable Grouping (No Formula Needed)

A PivotTable does the counting for you and lets you adjust intervals without touching a formula. This method is ideal when you want to explore the data and change groupings on the fly.

Steps:

1. Select your data column and go to Insert > PivotTable.
2. Drag the same field into both the Rows area and the Values area.
3. In the Values area, change the summary setting to Count if it isn’t already.
4. Right-click any cell in the Row labels, choose Group, and enter the starting value, ending value, and interval size (for example, 0 to 100 by 10).

The PivotTable instantly groups your data into the intervals you specified. The big advantage here is that you can right-click and ungroup or re-group with a new interval size in seconds—no rewrite of formulas needed.

Method 3: The Built-in Histogram Chart

Excel’s modern Histogram chart, introduced in Excel 2016 and available in Microsoft 365, gives you a visual distribution in under a minute. It handles binning automatically or lets you take full control through the Format Axis pane.

Steps:

1. Highlight your data column.
2. Go to Insert > Insert Statistical Chart > Histogram.
3. Right-click the horizontal axis of the chart and choose Format Axis > Axis Options.
4. Adjust the bin width manually, set the number of bins you want, or add an overflow bin and underflow bin to catch outliers.

The chart appears as a standard Excel chart object, so you can resize it, move it, or apply a chart style as soon as it’s created. This method is documented in Microsoft Q&A guidance on histogram creation, which confirms both the chart approach and the legacy ToolPak method as valid options depending on your Excel version.

Method 4: The Analysis ToolPak Add-In

The Analysis ToolPak is a legacy add-in that still ships with all desktop versions of Excel. It produces both a frequency table and a histogram in a single operation, which makes it useful for batch work.

Steps:

1. Enable the add-in: File > Options > Add-ins > Analysis ToolPak.
2. Go to Data > Data Analysis > Histogram.
3. Select the Input Range (your data) and the Bin Range (your upper-limit bins).
4. If your ranges include headers, check the Labels box.
5. Choose an output location—the current sheet, a new sheet, or a new workbook—and check Chart Output if you want the visual.

The one catch: the ToolPak must be loaded before it appears in the Data tab. If you don’t see Data Analysis in your Data tab, the add-in hasn’t been enabled yet.

What Common Mistakes Can Throw Off Your Frequency Distribution?

Even experienced analysts hit one of these snags when binning data. The good news is each mistake has a straightforward fix once you know what to look for.

Choosing the Right Method for Your Distribution

The best approach depends on what you need from the frequency distribution and which Excel version you’re running. Use the FREQUENCY function when you want a formula-driven table that updates automatically with new data—it works in every version and requires no add-ins. Use a PivotTable when you need to change intervals on the fly without editing formulas or recalculating arrays.

Use the built-in Histogram chart for a publication-ready visual that takes just a few clicks, provided you have Excel 2016 or later. And keep the Analysis ToolPak in your back pocket for legacy datasets where you want a table and chart delivered together from a single dialog.

One rule applies across all four methods: define your bins carefully before you start. The accuracy of any frequency distribution begins with how well you set the boundaries between each interval, regardless of the tool you choose.