An Excel histogram frequency distribution groups numerical observations into intervals, or bins, and shows how many values fall in each interval. In Microsoft Excel for Microsoft 365 on Windows, select the data and choose Insert > Insert Statistic Chart > Histogram; for a table of exact counts, use the FREQUENCY function or Excel’s Analysis ToolPak.
What a histogram frequency distribution shows
A frequency distribution is a summary of data that reports the number of observations in each group. A histogram displays those groups as adjacent bars: the horizontal axis represents numeric intervals, and the vertical axis represents their frequencies. For example, a survey of delivery times might group results into 0–5, 6–10, 11–15, and 16–20 minutes. The bar for 6–10 minutes would show how many deliveries took that long.
Histograms are useful for spotting the overall shape of a numerical dataset. A chart can reveal a cluster, a long tail, multiple peaks, or possible outliers more quickly than a long column of measurements. It is not the same as a bar chart of categories: histogram bars represent ordered numeric ranges and normally touch, while category bars usually represent distinct labels and have spaces between them.
The choice of intervals matters. Very narrow bins can make ordinary variation look like many separate spikes; very wide bins can conceal meaningful patterns. A histogram is therefore a view of the data shaped partly by the bin settings, not a unique summary with only one correct appearance.
Make a histogram chart in Excel
For a fast visual summary, put the observations in one column and create Excel’s built-in histogram chart. A simple example is a list of test scores in A2:A101, with a heading in A1.
- Select the numeric observations, or select the full range including the heading.
- Choose Insert > Insert Statistic Chart > Histogram.
- If you included a heading, check that Excel has treated it as a series name rather than as a data value.
- To change the intervals, right-click the horizontal axis and choose Format Axis. Under Axis Options, adjust the bin settings.
- Add a clear chart title and, if useful, axis titles so readers know what was measured and what the bars count.
Excel chooses bins automatically when the chart is first created. In the Format Axis pane, you can set a Bin width or a Number of bins. You can also set an Underflow bin or Overflow bin to collect values below or above a threshold. These settings are alternatives for controlling the grouping; changing one can change how the data are divided. The precise axis labels and pane layout can vary slightly with Excel updates.
Excel histogram charts are available in Microsoft 365 desktop Excel. On Mac, the histogram chart is also available in supported desktop versions, though ribbon layout and pane placement may differ. Excel for the web may not expose every chart-formatting control; use desktop Excel if you need the full set of bin options.
Choose useful bin intervals
A bin is an interval of values, and its width is the distance between boundaries. If you measure commute times in minutes, a width of five minutes might produce intervals such as 0–5, 5–10, and 10–15. The exact boundary convention should be clear, especially for values exactly on a boundary. In Excel’s FREQUENCY function, each listed bin value is an upper limit: a value equal to that limit is included in that bin.
There is no universally best bin width. Consider the measurement scale, the number of observations, and what comparison the chart needs to support. Equal-width bins are usually easiest to interpret. If a chart uses unequal-width bins, bar heights alone can be misleading because interval widths differ; consider whether the chart communicates frequency density appropriately or use a table alongside it.
- Use fewer, wider bins when a small dataset produces a choppy chart or when the main goal is to show a broad pattern.
- Use more, narrower bins when there are enough observations to show meaningful detail without leaving most bars empty.
- Use consistent boundaries when comparing groups or time periods. Otherwise, differences may reflect changed bin settings rather than changed data.
- Check the extremes. An unusual minimum or maximum can stretch the axis and compress the main cluster. Underflow and overflow bins can make tails easier to summarize.
For a report, record the chosen width and boundary rules near the chart. If one chart uses bins of five units and another uses bins of ten, their bar heights are not directly comparable even when both charts show the same underlying measure.
Create an exact frequency table with FREQUENCY
A frequency table makes the counts explicit and can feed a chart, percentage calculation, or report. FREQUENCY is particularly convenient when the bin limits are already known. In Microsoft 365, its results spill into the cells below the formula.
For a worked example, assume the 12 observations are in A2:A13: 2, 4, 5, 7, 8, 9, 10, 12, 13, 15, 17, and 20. Put the upper bin limits 5, 10, 15, and 20 in C2:C5. With the observations in A2:A13 and bin limits in C2:C5, enter this in D2:
=FREQUENCY(A2:A13,C2:C5)
The result spills into D2:D6 as 3, 4, 3, 2, and 0. The first count is the number of observations less than or equal to 5. The next counts are greater than the previous limit and less than or equal to the current limit. The extra fifth result counts values greater than the largest bin limit; here there are none. The counts add to 12, matching the number of observations.
Leave the cells below the formula clear so the results can spill. If Excel shows #SPILL!, another value or merged cell may be blocking the output. In older Excel versions without dynamic arrays, FREQUENCY may need to be entered as a legacy array formula across a selected output range; Microsoft 365 normally handles the spill automatically.
Calculate percentages and cumulative frequency
Counts answer “how many?” Relative frequencies answer “what share?” Using the example above, the bin from greater than 5 through 10 contains four observations out of 12, or about 33.3%. If FREQUENCY results are in D2:D6, including the overflow count, the percentage in E3 can be calculated as follows.
With frequency counts in D2:D6, enter this in E3 to calculate the share represented by the second bin:
=D3/SUM($D$2:$D$6)
For this example the result is 4/12, or approximately 33.3%. Format the cell as a percentage if desired. Including the overflow count in the denominator ensures all counted observations are represented. If you intentionally exclude some observations, state that rule and use a denominator that matches the population being described.
Cumulative frequency adds each bin to all the bins before it. It helps answer questions such as “What share of observations is at or below this threshold?” With frequencies in D2:D6, enter this in F2 and fill down through F6:
=SUM($D$2:D2)/SUM($D$2:$D$6)
The resulting cumulative percentages should rise or stay the same and reach 100% in the last row when all observations are included. A chart of cumulative percentages is an ogive-style summary; it is different from the ordinary histogram, which shows each bin’s individual frequency.
Use the Analysis ToolPak for a table and chart
The Analysis ToolPak’s Histogram tool creates a frequency table and can also create a chart. It is a useful option when you want output cells you can inspect or reuse rather than relying only on the chart’s automatic bins.
- If needed, enable the add-in: choose File > Options > Add-ins. At the bottom, set Manage to Excel Add-ins, select Go, check Analysis ToolPak, and select OK.
- Choose Data > Data Analysis > Histogram.
- Set the Input Range to the observation cells. Set the Bin Range to a separate range of upper limits, if you want to specify bins.
- Select Labels only if the selected input or bin range includes a heading.
- Choose an output location or a new worksheet, then select Chart Output if you also want a chart. Select OK.
The ToolPak reports frequencies for the supplied bin limits and includes a More category for values above the highest limit. Its options can also sort the histogram as a Pareto-style chart or add cumulative percentages. The ToolPak is included with desktop Excel but may need to be enabled; it is not the same as the chart command. On Excel for Mac, add-in management is generally under Tools > Excel Add-ins, and availability or dialog details can vary by release. Desktop Excel is the dependable choice when the ToolPak is required.
Histogram chart, frequency table, or PivotTable?
Choose the method according to the result you need. The built-in histogram chart is quickest for exploring one numeric column and adjusting bins visually. FREQUENCY is well suited to a compact formula-driven table with specified numeric limits. The ToolPak is handy for a generated table plus an optional chart. A PivotTable can summarize grouped values and is helpful when the data also have categories such as region or product.
For a PivotTable, place the numeric field in Rows and again in Values, then summarize the Values field by Count. If Excel permits grouping for that field, right-click a row label, choose Group, and set the starting value, ending value, and interval. Grouping may be unavailable when the source contains text, blanks, or mixed types in the numeric field; cleaning the source or using a frequency table can be easier. PivotTable grouping and histogram bins are not interchangeable in every situation, so confirm the interval boundaries before interpreting counts.
A frequency distribution can also be made with COUNTIF or COUNTIFS when you need custom inclusion rules or separate criteria. For example, formulas can count values strictly above one boundary and less than or equal to another. That flexibility is useful for irregular ranges, but it also means you must maintain the formulas and verify that bins do not overlap or leave gaps.
Common errors and how to check the result
A chart that looks wrong often reflects an issue in the source data or bin choices rather than an Excel calculation error. Check the following before drawing conclusions:
- Numbers stored as text: text-formatted values may not be counted as numeric observations. Convert them to numbers and check for spaces, currency symbols, or imported characters.
- Headers included as data: if a heading is in the selected range, use the Labels option where available or select only the numeric cells.
- Unexpected overflow: FREQUENCY and ToolPak output include an extra count for values above the largest bin limit. Include it when checking the total.
- Boundary confusion: write down whether a bin includes its upper or lower endpoint. With FREQUENCY, bin limits are inclusive upper limits.
- Zeros or negative values omitted: check that the selected range and the first bin cover the full valid data range. A chart’s underflow setting can group values below a specified threshold.
- Counts do not add up: compare the sum of the frequency counts with the number of valid numeric observations. Investigate excluded values, blanks, errors, and any deliberate filters.
Also distinguish frequency from frequency density. Frequency is a count; relative frequency is a proportion of the total. Neither is the same as the value of the measurement itself. A histogram bar of height 10 means ten observations in that bin when the vertical axis is count, not that the observations have a value of 10.
Frequently Asked Questions
How do I create a frequency distribution in Excel?
Select the numeric data and use Insert > Insert Statistic Chart > Histogram for a chart. For exact counts, list upper bin limits and use FREQUENCY, or choose Data > Data Analysis > Histogram after enabling the Analysis ToolPak.
What is the difference between a histogram and a frequency table?
A frequency table lists the intervals and their counts in cells. A histogram visualizes those same kinds of counts as adjacent bars. A table is easier to read for exact values; a chart makes the distribution’s shape easier to see.
How do I change the bin size in an Excel histogram?
Right-click the horizontal axis, choose Format Axis, and under Axis Options set Bin width or Number of bins. You can also define underflow or overflow bins. Review the resulting boundaries because changing the bin settings changes the counts shown in each bar.
Why does FREQUENCY return one more value than my number of bins?
FREQUENCY returns one count for each supplied upper limit plus an additional count for values greater than the largest limit. That final result is the overflow count, and it should be included when checking that the frequencies cover the data.
Can a histogram show percentages instead of counts?
The built-in histogram’s vertical axis is based on frequency. To show proportions, create a frequency table, divide each count by the total number of included observations, and chart those percentages. Label the axis clearly so readers can distinguish percentages from raw counts.
Related
- Can Excel Generate a PivotTable? How to Create One
- Excel Flash Fill for Rows: How It Works and When to Use It
- Excel Flash Fill: Does It Only Copy Data?
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- How to Insert a Bar Graph in Excel: A Step-by-Step GuideTo insert a bar graph in Excel, select your category labels and values, then choose Insert > Insert…
- Create a Free Gantt Chart in Google Sheets: Steps and TipsYes. You can create a free Gantt chart in Google Sheets by listing tasks and their start and…
- Excel Polynomial Trendline Order: What the Number MeansIn Excel, a polynomial trendline order is its degree: choose order 2 for a quadratic curve, up to…
- Excel Gantt Chart Not Showing All Data? How to Fix ItIf an Excel Gantt chart is not showing all its data, check the chart’s source range, date-axis limits,…
- Excel for Mac Spiderweb Graph: Create and Format ItYes. In Excel for Mac, a spiderweb graph is called a Radar chart: arrange your categories and values…
- Excel Chart Format Code for Millions: Examples and TipsTo show Excel chart values in millions, apply the custom number format #,##0,,"M"; for one decimal place, use…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.