How to make a histogram in Excel

Method 1: the built-in histogram chart

• Select the raw values, here A2:E9. One column or a block of cells both work, but select numbers only.
• Go to Insert > Insert Statistic Chart (the icon with blue columns in the Charts group) and choose Histogram.

Excel bins the data itself and draws the bars touching each other. The chart type is available in Excel 2016 and later on Windows and Mac, including Microsoft 365. Next to Histogram you will also find Pareto, a histogram sorted by frequency with a cumulative percentage line.

Setting the bins: bin width, number of bins, overflow and underflow

Right-click the horizontal axis and choose Format Axis. Under Axis Options > Bins you have these choices:

• Automatic: the default. Excel picks the width with Scott's normal reference rule, which usually gives odd edges such as 18.5 to 38.5.
• Bin width: type a round width, for example 20. This is the setting you will use most.
• Number of bins: Excel divides the range into that many equal bins (the count includes any overflow and underflow bins).
• Overflow bin: one bar for every value above a cutoff, for example above 100. It stops a few very large orders from stretching the axis.
• Underflow bin: one bar for every value at or below a cutoff.
• By Category: for text data, it counts each category instead of binning numbers.

Two details matter. The first bin starts at the smallest value in the data, not at zero, so with a bin width of 20 and a minimum of 18.50 you get 18.5 to 38.5. Setting an underflow bin at 20 forces the edges onto round numbers. And Excel's bins include their upper edge: an order of exactly 40.00 lands in the 20 to 40 bar. Write the bin labels on the chart or in a footnote so readers know which side an edge belongs to.

Method 2: a bin table with COUNTIFS

The built-in chart does not give you the counts as numbers you can reuse. A small bin table does. For each bin write a lower edge (From, included) and an upper edge (Under, excluded), then count with two conditions:

=COUNTIFS($A$2:$E$9,">="&B12,$A$2:$E$9,"<"&C12)

Lock the data range with $ and fill the formula down. Every value lands in exactly one bin, so the counts must add up to the number of values, 40 here. Divide each count by the total for the share: =D12/$D$19. If you are new to multi-condition counting, the SUMIFS and COUNTIFS examples explain the criteria syntax.

To chart the table, select the bin labels and the counts (A11:A18 and D11:D18), insert a Clustered Column chart, then open Format Data Series and set Gap Width to 0%. The bars must touch, because the bins are consecutive stretches of one number line, not separate categories. Add axis titles such as Order value (USD) and Orders.

Worked example: 40 order values in $20 bins

Rows 2 to 9 hold the raw data. The bin table below counts them with COUNTIFS. Each bin includes its From edge and excludes its Under edge, so the orders of exactly 40.00 and 60.00 count in the 40–60 and 60–80 bins.

Worked example: 40 order values in $20 bins

	A	B	C	D	E
1	Order values (USD), 40 online orders
2	22.40	38.90	47.50	31.20	64.80
3	29.90	18.50	42.00	55.30	36.70
4	27.10	71.40	33.80	24.60	49.90
5	40.00	58.20	26.30	93.60	35.40
6	19.80	44.70	30.50	67.20	38.10
7	52.60	28.90	125.00	41.80	34.20
8	46.30	23.70	60.00	37.50	81.90
9	32.40	29.20	54.80	43.60	108.50
10
11	Bin (USD)	From	Under	Orders	% of total
12	0–20	0	20	2	5.0%
13	20–40	20	40	18	45.0%
14	40–60	40	60	12	30.0%
15	60–80	60	80	4	10.0%
16	80–100	80	100	2	5.0%
17	100–120	100	120	1	2.5%
18	120–140	120	140	1	2.5%
19	Total			40	100.0%
20
21	Average order (mean)			46.21
22	Median order			39.45
23	Largest order			125.00

To chart it: select A11:A18 and D11:D18, insert a clustered column chart and set the gap width to 0%.

FREQUENCY: the array alternative

=FREQUENCY(data_array, bins_array) counts all bins in one formula. The bins array holds only the upper edges, and each bin counts the values above the previous edge and up to and including its own edge, so 40.00 belongs to the bin ending at 40. It returns one more value than there are edges: the last one counts everything above the top edge.

In Microsoft 365 the result spills into the cells below automatically. In Excel 2019 and older, select the output range first (one cell more than the number of edges), type the formula and confirm with Ctrl+Shift+Enter. FREQUENCY is quick, but COUNTIFS with explicit From and Under columns is easier to read and to audit, which is why the example uses it.

Method 3: the Analysis ToolPak

The free Analysis ToolPak add-in also builds histograms. Turn it on under File > Options > Add-ins, choose Excel Add-ins in the Manage box, click Go and tick Analysis ToolPak. Then use Data > Data Analysis > Histogram, enter the input range and a bin range of upper edges, and tick Chart Output. It works like FREQUENCY, with a "More" row for values above the last edge. The output is static values, so it does not update when the data changes. Use it for a one-off, and COUNTIFS or the built-in chart for anything you will refresh.

How to read the histogram

Look at the shape before the numbers. In the example most orders sit between $20 and $60 (30 of 40), the tallest bar is $20 to $40, and a thin tail runs out to $125.00. That is a right-skewed distribution: many small orders and a few large ones. The tail pulls the average ($46.21) above the median ($39.45), so the median is the better answer to "what is a typical order?".

Other shapes to watch for: two peaks (two customer groups mixed together), a cliff at a round number (a free-shipping threshold), or a single bar far away from the rest (a data entry error or a one-off). Try two or three bin widths. Too wide hides the shape, too narrow turns it into noise.

Common mistakes

• Gaps between the bars. A column chart has a gap width of about 150% by default. Set it to 0% so the bins read as one continuous scale.
• Edges counted twice. With "<=" on both sides, a value of exactly 40 lands in two bins. Include one edge and exclude the other, and check that the counts add up to the total.
• Unequal bin widths. Bins of 0–20, 20–50 and 50–200 make the wide bins look more important. Keep all bins equally wide, apart from an overflow bin.
• Leaving the bins on Automatic. Edges like 18.5 to 38.5 are hard to read. Set a round bin width and, if needed, an underflow bin.
• Text or blanks in the data. Numbers stored as text are ignored by the chart and by COUNTIFS. Convert them to numbers first.
• Using a pie chart for a distribution. Bins are steps along a number line, not slices of a whole. A histogram keeps them in order.