Excel does not have a worksheet function named HISTOGRAM. To make a histogram in Microsoft 365 for Windows, select your numeric data and choose Insert > Insert Statistic Chart > Histogram; to calculate the counts behind one, use the FREQUENCY function.
What Excel means by a histogram function
A histogram groups numerical observations into ranges, called bins, and displays how many values fall into each range. It helps reveal the distribution of a dataset: where values cluster, whether the data is spread out, and whether unusually high or low values appear. It is different from a bar chart of categories because the intervals on a histogram represent adjoining ranges of a numeric scale.
People searching for an Excel histogram function may mean one of three things: a chart that displays the distribution, a formula that counts observations in ranges, or a built-in analysis tool that can produce a frequency table and chart. Excel provides all three approaches, but they are separate features. The chart is usually the quickest choice; FREQUENCY is useful when you need counts you can reference in other calculations; and the Analysis ToolPak offers a dialog-based report.
Excel’s histogram chart is available in current desktop versions, including Microsoft 365. It is not a normal worksheet formula that you enter in a cell, and there is no valid =HISTOGRAM() formula. If a tutorial suggests that formula for ordinary Excel worksheets, it may be referring to a different product, an add-in, or a custom function.
Create a histogram chart in Excel
For a straightforward visual, put the observations in one column, ideally with a clear header and no totals mixed into the data. Select the numeric values, then follow these steps in Excel for Microsoft 365 on Windows:
- Choose Insert > Insert Statistic Chart > Histogram.
- Excel inserts a chart and chooses an initial bin arrangement automatically.
- To change the grouping, right-click the horizontal axis and choose Format Axis.
- Under Axis Options, adjust Bin width or Number of bins. You can also set underflow or overflow bins when those options are appropriate.
The chart updates as you change the bin settings. Use a descriptive chart title and, when useful, label the horizontal axis with the measured quantity and units, such as Delivery time (days). On Excel for Mac, the histogram is also available from the Insert tab’s statistical chart options; exact ribbon wording can vary by release. Excel for the web may show different chart-creation and formatting options, so use the desktop app if the histogram option or a bin control is missing.
A histogram chart is linked to its source data. If the observations change, Excel can update the chart, although you may need to review the bin settings if the new data has a substantially different range. A chart is a good choice when the goal is to explore or communicate the shape of one numeric dataset without first building a frequency table manually.
Choose useful bins
Bin choice affects what a histogram communicates. Narrow bins show detail but can make a small dataset look irregular; wide bins make the pattern easier to scan but can hide clusters or gaps. There is no universally best bin width. Consider the purpose of the chart, the number of observations, the measurement scale, and whether readers need to compare it with another dataset.
- Use meaningful intervals. For ages, whole-year or five-year intervals may be easier to interpret than arbitrary decimal cutoffs. For response times, intervals in seconds or minutes may fit the context.
- Keep intervals consistent. Equal-width bins generally make visual comparisons more straightforward. If intervals differ, the heights can be harder to compare as frequencies.
- Check the extremes. An overflow bin groups values above a chosen limit, and an underflow bin groups values below one. These can keep a few extremes from stretching the scale, but should be labeled or explained so readers know what was combined.
- Compare alternatives. Try a few plausible widths and see whether the main interpretation changes. Do not pick a width only because it makes the chart look smoother or supports a preferred conclusion.
Excel’s automatic choice is a starting point, not a statement that the selected bins are statistically definitive. If you need consistent comparisons between periods or groups, set the same bin boundaries for each one instead of letting each chart choose independently.
Count values with FREQUENCY
FREQUENCY is Excel’s built-in worksheet function for counting numbers into bins. Its syntax is =FREQUENCY(data_array, bins_array). The first argument is the data to count; the second is a list of bin upper limits in ascending order. Unlike a histogram chart, the function returns counts in cells that you can use in a table, report, or another formula.
For a concrete example, suppose A2:A11 contains 2, 4, 5, 7, 8, 9, 12, 14, 17, and 20. Put the upper limits 5, 10, 15, and 20 in C2:C5. With that layout, enter this formula in D2:
=FREQUENCY(A2:A11,C2:C5)
In Microsoft 365, the result spills into five cells, D2:D6. The counts are 3, 3, 2, 2, and 0: three values are at or below 5; three are greater than 5 and at or below 10; two are greater than 10 and at or below 15; two are greater than 15 and at or below 20; and none exceed 20. The extra final result is for values greater than the last bin limit. This is important: four bin limits produce five frequency counts.
Keep the bin limits sorted from smallest to largest. FREQUENCY treats each listed limit as the upper edge of its range, so a value equal to a limit belongs in that limit’s bin. For example, 10 is counted in the bin ending at 10, not the next bin. The function ignores blank cells and text in the data array, but a cell error in the input can cause an error in the result.
In current Microsoft 365, enter the formula once and allow the result to spill; leave the output cells below it empty. In older Excel versions without dynamic arrays, select the required output range first—one cell for each bin plus one overflow cell—enter the formula, then confirm it with Ctrl+Shift+Enter. If the output range is too small, older versions can omit results. On Mac, the legacy array-entry shortcut is Control+Shift+Return.
Use the Analysis ToolPak
The Analysis ToolPak provides a Histogram analysis that creates a frequency table and can also create a chart. It is useful if you want a report generated from a dialog rather than a formula, or if you are following a process that already uses ToolPak output. It is not the same feature as the Histogram chart on the Insert tab.
If Data Analysis is not visible on the Data tab in Windows, enable the add-in through File > Options > Add-ins. At the bottom, set Manage to Excel Add-ins and choose Go, then select Analysis ToolPak and confirm. After it is enabled, choose Data > Data Analysis > Histogram. In the dialog, provide an Input Range and, if you have prepared upper limits, a Bin Range. You can also select options such as Labels, an Output Range, and Chart Output. The dialog’s options and presentation may vary slightly between releases.
Supplying a bin range gives you control over the intervals and makes repeated analyses easier to compare. If you leave it blank, the tool can create bins automatically, but the resulting boundaries may not match the intervals you want to report. For repeatable work, document the boundaries and keep them fixed when comparing datasets. On Mac, the ToolPak can be enabled through Excel’s add-in management options; menu labels differ from Windows, and availability depends on the installed Excel release.
Read the chart without overinterpreting it
The height of a bar in a frequency histogram represents the number of observations in that interval. A tall central bar indicates many values in that range, while a long tail on one side can suggest skew. A gap may mean that no observations fall in some intervals, and isolated bars may point to unusual values worth checking. These patterns describe the data; by themselves, they do not explain why the pattern exists or prove a cause.
Check the axis labels and bin widths before comparing bar heights. If a chart uses unequal-width bins, raw counts alone can give a misleading visual comparison because a wider interval has more opportunity to contain observations. For a basic Excel histogram, consistent widths are usually easiest to interpret. Also distinguish frequency from percentage: a frequency chart shows counts, while a relative-frequency display expresses each count as a share of the total. You can calculate percentages from FREQUENCY results by dividing each bin count by the total number of valid observations.
Common histogram problems
- The chart option is missing. Confirm that you are using a version of Excel with histogram chart support and that you selected numeric observations. If the ribbon differs, try the desktop app rather than the web version.
- The chart has unexpected bins. Excel may have selected an automatic width that does not fit the question. Open Format Axis and set the bin width or number of bins explicitly.
- Numbers are not being counted. Values stored as text are not valid numeric observations for a numerical histogram. Check for leading apostrophes, imported text, or inconsistent decimal separators, and convert the entries to numbers if appropriate.
- The frequency output has an extra result. That is expected: FREQUENCY returns a final count for values above the last upper limit.
- The formula will not spill. In Microsoft 365, clear any content or merged cells in the intended output area. A blocked spill range prevents the results from appearing.
- Dates appear as numbers. Excel stores dates as serial numbers. A histogram of dates can therefore work numerically, but choose boundaries and axis labels that make the date intervals understandable.
When a result looks wrong, first inspect the source range and its data types, then verify the bin limits and the treatment of boundary values. A quick manual check of a few observations—especially values equal to a bin limit—often identifies whether the issue is the input data or the interpretation of the bins.
Which method should you use?
Choose the method based on what you need from the result, rather than looking for one universal histogram feature:
- Insert Histogram chart: best for quickly seeing a distribution and adjusting its appearance interactively.
- FREQUENCY: best when you need editable counts in worksheet cells, custom downstream calculations, or a table that is easy to audit.
- Analysis ToolPak: best when you want a dialog-generated frequency report and optional chart without building the output formula yourself.
All three methods depend on sensible data and bins. A chart is convenient but may use automatic boundaries; a formula gives explicit, reusable counts but requires you to understand its upper-limit rules; and the ToolPak automates a report but still needs appropriate input and bin choices. For a one-off visual, start with the chart. For a recurring report with fixed ranges, use FREQUENCY or a ToolPak bin range so that the grouping remains consistent.
Frequently Asked Questions
What is the Excel formula for a histogram? Excel has no built-in HISTOGRAM worksheet function. Use the Histogram chart for a visual, or use =FREQUENCY(data_array,bins_array) to return counts for specified bin limits.
How do I make a histogram in Excel? Select the numeric data, then choose Insert > Insert Statistic Chart > Histogram. Right-click the horizontal axis and choose Format Axis to adjust the bin width or number of bins.
What does FREQUENCY return? FREQUENCY returns one count for each bin upper limit and one additional count for values above the final limit. In Microsoft 365, those results spill into adjacent cells when the output area is clear.
Can Excel make a histogram automatically? Yes. The Histogram chart chooses an initial bin arrangement, and the Analysis ToolPak can make bins when no bin range is supplied. Review the result and set meaningful boundaries when accuracy or comparisons matter.
Why does FREQUENCY return one more number than I expect? The extra number counts values greater than the last specified bin limit. It is part of the function’s normal output, not a duplicate of the final bin.
Related
- Excel Goes Blank and Stops Responding? Fixes to Try
- Excel Goal Seek With Two Variables: Solver Options
- Excel Goal Seek With Multiple Inputs: Use Solver
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Chart Line of Best Fit: How to Add and Read ItTo add a line of best fit in Excel for Microsoft 365 on Windows, select a chart, then…
- Power Pivot Hierarchy: Create and Use One in ExcelA Power Pivot hierarchy is an ordered set of related columns from one table in Excel’s Data Model,…
- Excel Dashboard Sample: A Practical Sales TemplateAn Excel dashboard sample is a one-page summary that turns a structured data table into a quick view…
- Excel Pie Chart Template: Create, Customize, and ReuseAn Excel pie chart template is a reusable chart design: it saves a chart type and formatting so…
- Add Excel Charts to PowerPoint: Link or Embed?To add an Excel chart to PowerPoint, select the chart in Excel, copy it, then paste it onto…
- Google Sheets Gantt Chart Tutorial: Build a Project TimelineTo make a Gantt chart in Google Sheets, put each task and its start and end dates in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.