Histogram Bin Width Calculator

What this calculator gives you

Paste a column of numbers — straight from Excel or Google Sheets, from a CSV, or typed with spaces, commas, semicolons or line breaks between them. The page reads the count, range, standard deviation, interquartile range and skewness, then runs the six standard bin-width rules and a cost-minimising scan, each with its formula and your own numbers substituted. Click any rule to redraw the histogram with it, or drag the width slider to choose your own. The edges come out as a column you can paste into Excel’s Bin Range or the FREQUENCY function, and the bin table downloads as CSV. Nothing leaves your browser.

Bin width and number of bins are the same decision

The range ties them together: k = ⌈(max − min) / h⌉. Some rules produce a count directly (Sturges, square root, Rice, Doane) and others a width (Scott, Freedman–Diaconis). numpy then lays k equal bins exactly from the minimum to the maximum, so the width it actually draws is range / k, a little narrower than the rule’s h. On the sample loaded at the top, Freedman–Diaconis asks for h = 1.452 and numpy draws 7 bins of 1.301. The cards show both numbers so you can tell which one a given program means.

The formulas

The six standard rules on this page follow numpy’s histogram_bin_edges definitions exactly — the Scott constant is (24√π)1/3 = 3.4908 and σ is the population standard deviation (divide by n), the quartiles use linear interpolation (the same as Excel’s QUARTILE.INC), and the skewness in Doane’s formula is the population g₁ (Excel’s SKEW.P, not SKEW). The verification script for this page checks every rule against numpy’s output on fixed data.

What numpy, matplotlib and Excel actually do

np.histogram and plt.hist default to 10 bins whatever the data. Ask for bins='auto' and numpy takes the smaller of the Freedman–Diaconis and Sturges widths — that is, the larger number of bins, not the smaller. If the IQR is zero, FD gives a width of zero and 'auto' falls back to Sturges; plain bins='fd' would give you a single bin instead. seaborn’s histplot uses 'auto' as its default.

Excel’s Histogram chart (Excel 2016 and later) defaults to Automatic bins, which Microsoft’s documentation says are “calculated using Scott’s normal reference rule”. The documentation shows the formula only as an image and does not say which standard deviation it uses or where the first bin starts, and this page has not been checked against Excel itself — so read the Scott card as an approximation of Excel’s automatic axis. If the exact bins matter, set Number of bins or Bin width under Format Axis yourself.

One difference is not approximate: Microsoft documents that the Analysis ToolPak counts a value in a bin when it is “greater than the lowest bound and equal to or less than the greatest bound”, i.e. (a, b], and FREQUENCY counts the same way. numpy, matplotlib and this page use [a, b). With whole numbers that land exactly on an edge the two disagree, so the Excel panel recounts the copied column the way Excel would and warns you when any value would move.

Why the choice matters: the experiment behind this page

The article this tool is built on drew a sample from an equal mixture of two normal distributions, N(−2, 1) and N(2.2, 1). The true density is clearly two-peaked, but only just: its valley is 22% of the peak height (valley/peak 0.220). From it the article took 220 points with numpy’s generator and seed 42, and histogrammed the same 220 numbers with three widths, the edges starting 0.3 below the minimum:

• width 2.4: counts 48, 66, 65, 41 and an empty fifth bin — one hump. The real valley is still there, hidden between the 66 and the 65.
• width 0.8: 13 bins with a clear 37 → 9 → 35 dip — close to the true shape.
• width 0.15: 65 bins holding about 3.4 points each, and counting by eye, more than 20 “peaks” made of one or two stray points.

The article also noted that Freedman–Diaconis and Scott recommend a width near 1.4 for this sample, and that a width of 1.5 gives 17, 61, 34, 21, 60, 24, 3 — both peaks and the valley between them.

The Article sample in the data menu is that exact sample, written out to six decimals; the rounding changes none of the counts. The first three presets redraw the article’s histograms with the same edges and the same counts. The only difference is at the right end: the page stops at the first edge past the maximum, where the article’s np.arange added empty bins — so width 2.4 shows four bars, 0.8 shows 12 and 0.15 shows 63. At 0.15 the Peaks in the bars readout counts 21 bars taller than their left neighbour and at least as tall as their right one. On this sample the rule cards give Sturges 9 bins, square root 15, Rice 13, Scott 7, Freedman–Diaconis 7, Doane 10 and Shimazaki–Shinomoto 10, and numpy’s 'auto' picks Sturges because its width (1.037) is smaller than FD’s (1.452). All of them show two peaks, which is the reassuring half of the story: the widths that flattened or shredded the sample were the ones nobody’s rule would choose.

KDE: the same knob, smoother picture

A kernel density estimate puts a small Gaussian on every point and adds them up. The width of each Gaussian, the bandwidth, plays the role of the bin width. The article measured, with scipy.stats.gaussian_kde, where the bandwidth is a factor times the sample standard deviation: factor 0.08 (bandwidth 0.19) gave 7 peaks, 0.15 (0.36) gave 2 peaks with a valley/peak ratio of 0.29, and 0.8 (1.93) gave a single hump. scipy’s default, Scott’s factor n−1/5 = 0.340, kept both peaks but with a ratio of 0.52; Silverman’s 0.360 gave 0.56 — a valley about half as deep as the hand-picked one, against a true 0.22.

The KDE curve here uses the same definition (bandwidth = factor × the standard deviation with n − 1, as scipy does) and the same peak test, and on the article sample it reproduces all of those figures to two decimals. Note that scipy’s “silverman” is (3n/4)−1/5 s, not the 0.9 min(s, IQR/1.34) n−1/5 rule of thumb that R’s density() uses; the menu offers both, and on this sample the R version gives a narrower bandwidth, 0.739, and a valley/peak of 0.47.

Using the result in Excel or Google Sheets

The panel under the rule cards does three things. It gives the upper limits as a column: in Excel’s Analysis ToolPak (Data → Data Analysis → Histogram) paste them as the Bin Range; with formulas, select k + 1 cells and enter =FREQUENCY(data, limits), which also works in Google Sheets. It gives the Excel formula for each rule, so the bin count updates with your sheet: =ROUNDUP(LOG(COUNT(A:A),2)+1,0) for Sturges, =2*(QUARTILE.INC(A:A,3)-QUARTILE.INC(A:A,1))/COUNT(A:A)^(1/3) for the Freedman–Diaconis width. And it recounts the copied limits the way Excel would, so you know before you paste whether any value sits on an edge.

For whole numbers — scores, ages, counts — choose Rounded edges. The width becomes a whole number and the edges fall half-way between integers (for a width of 5: 39.5, 44.5, …), so no value lands on an edge and Excel, numpy and this page all agree. A non-integer width on integer data gives a comb pattern instead, because neighbouring bins cover different numbers of possible values — with a width of 5.6, some cover five whole numbers and others six.

Limits

• These are rules of thumb. Scott and Freedman–Diaconis aim at the smallest average squared error for a smooth density; none of the rules promises to reveal how many modes the data has.
• The Shimazaki–Shinomoto scan uses equal bins anchored at the minimum, k from 2 up to 500 (fewer when the values are so coarsely spaced that bins would be narrower than half a step), and no averaging over shifted bin positions. On the article sample its cost is nearly flat — k = 5, 10 and 15 are within 3% of each other — so treat its answer as a range, not a number.
• Heavy tails break the rules that use the range. In the Student t sample (500 values, 2 degrees of freedom) four values lie beyond ±10 and stretch the range from −8.3 to 37.3, so Sturges’ 10 bins are 4.56 wide and two of them hold 480 of the 500 values. Freedman–Diaconis, which uses the IQR, asks for 120 bins and keeps the bulk resolved.
• The page counts at most 5,000 bins and reads at most 100,000 values. For large samples, where the exact sum would be slow, the KDE is computed on a fine grid by linear binning; in the verification run with 50,000 values it differed from the exact curve by less than 0.01% of the peak height.