To analyze a distribution in Excel for Microsoft 365 on Windows, select a column of numeric observations, choose Insert > Insert Statistic Chart > Histogram, then adjust the bins in Format Axis. A histogram groups values into ranges and shows how many observations fall in each range, making patterns such as clustering, spread, gaps, and skew easier to see.
What a histogram shows
A histogram is a frequency chart for numeric data. Each bar represents a range of values, called a bin, and the bar’s height indicates how many observations fall within that range. The bars usually touch because the underlying ranges are continuous or ordered intervals. For example, a histogram of delivery times might use bins from 0 to 10 minutes, 10 to 20 minutes, and so on; the height of each bar shows the number of deliveries in that interval.
Histograms help answer questions that a list of numbers does not answer quickly: Are most measurements near a typical value? Is the data spread broadly or concentrated tightly? Are there unusually high or low observations? Does the distribution have one main peak, several peaks, or a long tail? They are descriptive tools, not proof of why a pattern occurred. A high bar tells you that many values fall in that interval; it does not explain the cause.
A histogram is different from a column chart. A column chart compares separate categories, such as sales by product, while a histogram groups a numeric scale into intervals. It is also different from a time-series line chart: unless time is deliberately used as the measured variable, a histogram does not show the order in which observations occurred.
Create a histogram chart in Excel
For a quick visual analysis in Microsoft 365 for Windows, put the measurements in one column, with one observation per row. Remove or review text entries and errors, and decide whether blank cells should be excluded. Then:
- Select the numeric data, including a header if there is one.
- Choose Insert > Insert Statistic Chart > Histogram.
- Select the horizontal axis and open Format Axis. Under Axis Options, adjust the bin settings, such as Bin width or Number of bins.
- Add a useful chart title and axis titles if the chart will be shared or used in a report.
In current desktop Excel for Mac, the histogram chart is also available from the Insert tab’s Statistical Chart options; ribbon wording and placement can differ slightly by release. Excel for the web may display charts, but the desktop app is a more reliable choice when you need detailed axis formatting or the Analysis ToolPak. If the chart command is unavailable, check that the selection contains numeric observations rather than labels alone.
Excel chooses an initial bin arrangement automatically. Treat that as a starting point, not necessarily the right analytical choice. A chart with too many narrow bins can look noisy; a chart with too few wide bins can hide important structure. The best setting depends on the number of observations, the measurement scale, and what comparison you need to make.
Choose bin width and boundaries
Bin width is the size of each interval. If you are studying quiz scores from 0 to 100, a width of 10 creates ranges about ten points wide. If you are examining a tightly controlled measurement, such as a part diameter recorded to tenths of a millimeter, a much narrower width may be informative. In Excel, select the horizontal axis, open Format Axis, and use Bin width or Number of bins. You can also set underflow and overflow bins to group values below or above a chosen threshold.
There is no universally correct bin width. Consider these practical factors:
- Purpose: Use wider bins for a broad overview and narrower bins when small differences matter and the sample is large enough.
- Sample size: A small dataset can produce many empty or one-observation bins if the intervals are too narrow.
- Units and precision: Choose boundaries that make sense for the way the measurement is recorded. A bin width of 7.3 may be hard to explain if the data are reported in whole units.
- Comparisons: When comparing two groups, use the same bin boundaries and axis scale. Separate automatic settings can make similar distributions appear different.
Boundary choices matter. A bin’s stated limits and the way a method assigns values at an exact boundary can affect which bar contains a value equal to a cutoff. For an important report, inspect the axis labels and verify the counts with a frequency table rather than assuming the chart’s appearance tells the whole story.
Use the Analysis ToolPak for a frequency table
The Analysis ToolPak can produce a table of counts for bins that you specify, and it can optionally create a histogram chart. On Windows, if Data Analysis is not visible on the Data tab, go to File > Options > Add-ins. In the Manage box, choose Excel Add-ins, select Go, check Analysis ToolPak, and choose OK. Then choose Data > Data Analysis > Histogram.
In the dialog, set the Input Range to the observations and the Bin Range to cells containing the upper limits for the intervals. If the ranges include headings, select Labels. Choose an Output Range or a new worksheet, and select Chart Output if you want Excel to make a chart. On Mac, enable the ToolPak through Tools > Excel Add-ins, then look for Data Analysis on the Data tab. Availability and exact dialog layout can vary by Excel release.
ToolPak bin values are upper limits. For example, if the bin range contains 10, 20, and 30, the output counts values up to 10, then values greater than 10 and up to 20, then values greater than 20 and up to 30. The ToolPak also reports an additional count for values above the last listed limit. This explicit table can be easier to audit than a chart whose bin settings were chosen automatically.
Read shape, spread, and unusual values
Start by checking the overall shape. A roughly symmetric, single-peaked distribution has similar tails on each side of its central region. A right-skewed distribution has a longer tail toward higher values; a left-skewed one stretches toward lower values. A second peak may suggest two subgroups, such as measurements taken from two machines or two customer segments. It is a clue to investigate, not evidence by itself that two populations exist.
Look at the horizontal range to understand spread and at the concentration of bars to identify where observations are common. Empty intervals can indicate a real gap, but they can also result from bins that are too narrow or a small sample. A distant bar may represent a possible outlier. Check the source record before excluding it: a legitimate rare event, a measurement error, and a data-entry mistake require different treatment.
Always consider the sample size and the collection process. A histogram of 20 observations will naturally look less smooth than one based on 20,000. If measurements were collected only from a selected group, the chart describes that group and may not represent a wider population. If there is an important sequence, location, or category in the data, analyze those dimensions too; combining unlike groups can conceal meaningful differences.
Verify counts with FREQUENCY
A frequency table is useful for checking whether the chart tells the intended story. For example, suppose A2:A11 contains the values 8, 12, 17, 21, 24, 29, 31, 35, 38, and 46, and D2:D5 contains bin limits 10, 20, 30, and 40. With those data and limits, enter this formula in E2:
=FREQUENCY(A2:A11,D2:D5)
In Microsoft 365, FREQUENCY returns a five-cell vertical spill: 1, 2, 3, 3, and 1. Those counts mean one value is at most 10, two are greater than 10 and at most 20, three are greater than 20 and at most 30, three are greater than 30 and at most 40, and one is above 40. The total is 10, matching the ten observations. Leave the cells below E2 clear so the results can spill. In older Excel versions, FREQUENCY may require selecting the output range and confirming it as an array formula with Ctrl+Shift+Enter.
This check is especially useful when you set custom bins, compare groups, or need reproducible counts for a report. Make sure the bin limits used in the formula match the intervals shown in the chart; otherwise, the table and the visual are not directly comparable.
Common problems and how to fix them
- The chart has unexpected bins: Excel may have selected an automatic width. Open Format Axis and set Bin width or Number of bins explicitly.
- Some values seem missing: Check for numbers stored as text, blank cells, errors, or values outside the chosen range. For a ToolPak analysis, remember the extra overflow count above the final limit.
- The chart looks jagged: Try a wider bin width, but do not widen it so much that important peaks or gaps disappear. Compare a couple of reasonable settings and report the chosen width.
- Two charts look incomparable: Give both datasets the same bin boundaries and horizontal scale. Automatic bins may differ from chart to chart.
- The bars do not tell a useful story: Confirm that the selected column is genuinely numeric and that combining the records makes sense. Separate different periods or groups when they represent distinct processes.
Do not remove an outlier merely to make the chart look smoother. First confirm whether it is valid, then decide whether the analysis should include it, explain it separately, or show results both with and without it. Keep the original data unchanged and document any filtering or exclusions.
Histogram chart or ToolPak table?
The built-in Histogram chart is usually the fastest route to an exploratory picture. Its axis controls make it convenient to adjust bins and inspect distribution shape. The Analysis ToolPak is useful when you want a count table based on explicit upper limits, a chart generated alongside that table, or results that are straightforward to copy into a report. A manually calculated table can offer still more control, but requires you to manage formulas and boundaries carefully.
These methods answer related but not identical workflow needs; neither removes the need to choose meaningful bins. For polished analysis, use the chart to communicate shape and the frequency table to document exact counts. If you need to compare distributions across categories, keep bin definitions consistent and consider whether small multiple charts or a summary table will be clearer than overlapping bars.
Frequently Asked Questions
What is the best number of bins for an Excel histogram?
There is no single best number. Try a setting that reveals the broad shape without creating excessive empty bars, then check whether reasonable alternative widths change your interpretation. State the bin width when precision matters.
Can Excel make a histogram from text categories?
No. A histogram is for numerical measurements grouped into intervals. Use a bar or column chart for categories such as department names or product types.
Why does my histogram show an extra bar?
The Analysis ToolPak reports a final overflow count for observations greater than the largest bin limit. The built-in chart can also use underflow or overflow bins if you enable them in Format Axis.
Can I use a histogram to compare two groups?
Yes, but use identical bin boundaries and scales for both groups. Separate charts can be easier to read; if you overlay series, make sure the design does not hide one group’s bars.
Does a histogram show the average or median?
Not by itself. It suggests where values cluster and how they spread, but calculate summary statistics separately if you need an exact mean, median, or other measure.
Related
- Excel Goal Seek Precision: Accuracy and Limits
- Excel Goal Seek Tolerance: Set Precision and Check Results
- Excel Goal Seek Integer? How to Get Whole-Number Results
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Histogram: Show Percentages Instead of CountsExcel’s built-in Histogram chart displays counts, not the percentage of observations in each bin. To show percentages, calculate…
- Power Pivot Calculated Field: Measures vs ColumnsIn Power Pivot, an aggregated calculation for a PivotTable is usually a DAX measure, often informally called a…
- Dot Progress Chart in Excel: Create One Step by StepExcel does not include a built-in chart type called a dot progress chart, but you can create the…
- Power BI Pivot Table: How to Build a Matrix ReportPower BI does not have an Excel-style PivotTable object, but its Matrix visual provides a similar way to…
- How to Find the Gradient of a Trendline in Excel EasilyTo find a trendline’s gradient in Excel, calculate its slope with SLOPE or display the equation on a…
- COUNTA in Google Sheets Pivot Tables ExplainedIn a Google Sheets pivot table, COUNTA counts how many non-empty values appear in a selected field for…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.