To set histogram bins manually in Excel for Microsoft 365 on Windows, select the chart’s horizontal axis, right-click it, choose Format Axis, and enter a value under Bin width or Number of bins. For specific or uneven boundaries, use the Data Analysis ToolPak or build a frequency table, because the built-in histogram chart does not let you type an arbitrary list of bin edges into its axis settings.
What “manual bins” means in Excel
A histogram groups numeric observations into ranges, called bins, and displays how many observations fall into each range. A bin width of 10, for example, groups data into intervals of equal size. Choosing the width yourself gives you control over how detailed or broad the chart looks, but it does not necessarily let you set every boundary independently.
Excel’s built-in Histogram chart has options for automatic bins, a chosen bin width, a chosen number of bins, and optional underflow and overflow bins. These controls are useful when equal-width ranges are appropriate. If you need ranges such as 0–5, 5–20, and 20–100, those unequal intervals require a different approach, such as a custom frequency table.
It helps to distinguish two meanings of “manual”: you may want to set how many equal-width bins appear, or you may want to specify the exact limits of each bin. Excel’s chart settings handle the first case directly. The second calls for a bin list and a frequency calculation or the ToolPak.
Set a fixed bin width on a histogram chart
Use this option when the data should be grouped into equal-sized intervals, such as every five points or every 10 minutes.
- Put the observations in one column, with a descriptive heading at the top. Select the numeric data, including the heading if you want Excel to use it as a series name.
- Choose Insert > Insert Statistic Chart > Histogram.
- Click the chart’s horizontal axis labels, right-click, and choose Format Axis. In the Format Axis pane, open Axis Options if it is not already displayed.
- Under Bins, select Bin width and enter a positive number in the field. For measurements recorded to one decimal place, a width such as 2.5 may be more meaningful than a whole-number width.
- Check the resulting axis labels and bars. If the chart is too crowded or too coarse, change the width and review the distribution again.
The width is the span of each regular interval, not the number of intervals. If values run from roughly 0 to 100 and the width is 10, the chart will need about 10 regular intervals, subject to the data range and any underflow or overflow settings. Excel determines the displayed boundaries from the data and the width; do not assume that the first displayed boundary will be zero unless the chart shows that it is.
Choose a number of bins instead
If you know how many bars you want but not the interval size, set the number of bins. Select the horizontal axis, open Format Axis, and under Bins choose Number of bins. Enter the desired count. Excel adjusts the interval widths to divide the data range into that many bins.
For instance, a class presentation might call for six bars so that the chart remains readable on a slide. A larger number can reveal detail, but it can also leave many bins with only one or two observations. A smaller number smooths the display, but may conceal clusters, gaps, or unusual values. There is no universally correct number: the choice depends on the sample size, the spread of the measurements, and the question the chart should answer.
Number of bins and bin width are alternative controls for the regular bins. Choose one in the axis settings rather than expecting both to impose separate rules at once. After changing the setting, inspect the actual labels; the resulting intervals may not match tidy round values you would choose for a report.
Use underflow and overflow bins thoughtfully
The Format Axis pane also offers Underflow bin and Overflow bin settings. These group values at the low or high end into a single end bin, using the limit you enter. They can be useful when the main interest is in a central range and extreme values would otherwise stretch the chart’s scale.
For example, if most exam scores lie between 40 and 100 but a few imported records are below 0, an underflow bin can group the low-end values instead of giving them several separate intervals. An overflow bin can similarly collect values above a chosen threshold. These options do not remove or correct the observations; they change how the chart groups them. Check the boundary labels and the data counts before interpreting an end bin, especially if the threshold itself matters to the analysis.
A common misconception is that an overflow bin is a way to exclude large values. It is not: the values are still represented, but combined into the overflow category. If you need to exclude records, filter or clean the source data deliberately and document that decision rather than using a chart bin setting as a substitute.
Make exact or uneven bins with the ToolPak
When the required boundaries are specific, provide a list of bin limits and use the Histogram tool in the Analysis ToolPak. The listed values act as upper limits for the bins. This is a better fit for reporting bands, grading ranges, age groups, or other categories whose widths are not all equal.
- Enter the observations in one column and the desired bin upper limits, in ascending order, in another. Include a heading only if you will identify the corresponding option in the dialog.
- If Data Analysis is not on the Data tab, enable the add-in on Windows using File > Options > Add-ins. At the bottom, set Manage to Excel Add-ins, click Go, select Analysis ToolPak, and click OK.
- Choose Data > Data Analysis > Histogram. Set the Input Range to the observations and the Bin Range to your upper-limit list. Select Labels only if the selected ranges include headings.
- Choose an output location, and select Chart Output if you want Excel to create a chart along with the frequency table. Click OK and inspect the table against the original data.
The ToolPak output uses the supplied limits to report frequencies and includes a final category for values above the last limit. Plan the list with that behavior in mind, and do not mistake the final category for an ordinary interval with a known upper boundary. The ToolPak produces an output table; if you need a polished chart, you can format the resulting chart or create one from the frequency table.
On Excel for Mac, the Analysis ToolPak is also available in supported desktop versions; enable it through Tools > Excel Add-ins, then look for Data Analysis on the Data tab. The exact dialog presentation can differ slightly from Windows. Excel for the web does not provide the same desktop add-in workflow, so use desktop Excel for this ToolPak method.
Build a frequency table for complete control
A frequency table is often the clearest option when bins have irregular limits or need custom labels. It also makes the counts visible and easy to audit. For example, suppose the measurements are in A2:A11, the exclusive lower limits are in D2:D5 (0, 10, 20, 30), and the inclusive upper limits are in E2:E5 (10, 20, 30, 40). In F2, enter the formula below and fill it down through F5:
=COUNTIFS($A$2:$A$11,">"&D2,$A$2:$A$11,"<="&E2)
This counts observations greater than the lower limit and less than or equal to the upper limit. If A2:A11 contains 4, 7, 10, 12, 18, 20, 21, 27, 30, and 35, the four frequencies are 3, 3, 3, and 1. Their sum is 10, which matches the number of observations. The boundary convention matters: a value of 10 belongs to the first interval, not the second. Choose and label a convention that fits your reporting rules.
Once the table is correct, select the interval labels and counts and insert a column chart. A column chart made from a frequency table can communicate grouped counts, but it is not automatically the same as an Excel Histogram chart: you control the categories and labels directly. This method is especially useful when the bins are business-defined categories rather than mathematically equal intervals.
Check the chart and fix common problems
If the bin controls are missing, first confirm that you created a Histogram chart rather than a regular column chart. A column chart built from raw values has different axis settings. Also make sure you selected the horizontal axis, not a bar or the vertical value axis, before opening Format Axis.
- The chart has too many bars: Increase the bin width or reduce the number of bins, then check whether important patterns remain visible.
- Most values appear in one bar: Check whether the selected width is too large, and verify that the source values are numeric and use the expected units.
- Some data seem missing: Look for blanks, text stored as numbers, error values, or records outside the range you selected. Compare the frequency total with the count of valid numeric observations.
- The boundaries look unexpected: Review the axis labels and the entered width or limits. The built-in chart chooses its own starting point for regular bins; use explicit limits in a ToolPak table or custom frequency table when exact cutoffs are essential.
- The chart changes after adding data: Check that the source range includes the new rows. If it does, review whether the selected bin setting still suits the expanded range.
For a fair comparison between two histograms, use the same bin boundaries for both datasets. Separate charts with different automatic boundaries can make similar distributions look different, or different distributions look similar. A shared frequency table or a consistent manually set width makes the comparison easier to interpret.
Which bin method should you choose?
Use Bin width when equal-sized intervals have a meaningful unit, such as 5 millimeters, 10 dollars, or 15 minutes. Choose Number of bins when the visual space or a reporting requirement sets the number of bars and the precise interval width is secondary. Use underflow or overflow only when grouping values beyond a threshold is part of the story.
Choose the ToolPak or a frequency table when exact upper limits matter, intervals are uneven, or each category needs a defined label. The ToolPak is convenient for a quick frequency output. A formula-based table is easier to customize and audit, especially if the bins are part of a repeatable workbook. Whichever route you use, retain the raw observations and state the boundaries clearly so readers can tell what each count means.
Frequently Asked Questions
Can I enter custom bin boundaries directly in an Excel histogram chart?
No. The built-in chart lets you set a regular bin width or number of bins and configure underflow and overflow bins, but it does not provide a field for an arbitrary list of individual boundaries. Use the ToolPak or a frequency table for exact or uneven limits.
How do I change the bin width in Excel?
Select the histogram’s horizontal axis, right-click and choose Format Axis, then select Bin width under Bins and enter the width you want. Check the axis labels afterward to confirm the intervals make sense for your data.
What is the difference between bin width and number of bins?
Bin width sets the span of each regular interval, while number of bins sets how many regular intervals Excel should display. Either choice affects the chart’s detail; use a width with a meaningful unit when possible, or choose a count when the number of bars is the priority.
Can histogram bins have different widths?
Not through the built-in Histogram chart’s bin-width control, which creates regular intervals. To use different-width ranges, define the boundaries in a ToolPak bin list or calculate counts in a custom frequency table, then chart that output.
Why can’t I see the Bins options?
You may have inserted a regular column chart instead of a Histogram chart, or selected the wrong chart element. Select the horizontal axis of a Histogram chart and open Format Axis; the bin controls appear under Axis Options.
Related
- Excel Group Duplicate Rows Without Losing Data
- Excel Group Rows: How to Expand and Collapse Rows Easily
- Excel File Corrupted? How to Recover and Repair It
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Line Chart by Date: Make Dates Plot CorrectlyTo make an Excel line chart by date, put real Excel dates in one column and the values…
- Excel Pivot Chart Histogram: What Works and What Doesn’tExcel does not provide a true Histogram chart as a PivotChart type. You can, however, group numeric values…
- Excel Line Chart: Ignore Null Values or Show GapsYes. In Excel for Microsoft 365 on Windows, you can control how a line chart handles empty cells:…
- Excel Trendline: How to Ignore Zero ValuesExcel has no built-in setting to make a trendline ignore zero values, but you can exclude them from…
- Best Excel Gantt Chart Templates for Every ProjectFor most projects, the Vertex42 Gantt Chart Template is the best place to start: it gives you a…
- Basic Excel Line Chart: Create and Format OneTo make a basic line chart in Microsoft Excel for Microsoft 365 on Windows, arrange time periods in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.