Written and maintained by CASRAI Editorial Board
Last updated
To create a histogram in Excel, select your numeric data and choose Insert > Insert Statistic Chart > Histogram (Windows), or the Statistical icon on the Insert tab (Mac). Excel groups the values into bins automatically; you then set the bin width or number of bins on the horizontal axis. This guide covers that chart method, the Analysis ToolPak, and the FREQUENCY function, and ends with a worked example you can check by hand.
What a histogram is used for in research
A histogram splits the range of the data into equal-sized bins (classes) and counts the points in each; the vertical axis is frequency and the horizontal axis is the measured variable (NIST/SEMATECH e-Handbook, section 1.3.3.14). NIST lists centre, spread, skewness, outliers and multiple modes among the features a histogram reveals. The same source notes that classes can be defined arbitrarily by the user or by a systematic rule, which is why the bin settings below matter. For the concept itself see histogram; for chart choice see types of graphs in statistics.
Which Excel versions have the Histogram chart
Microsoft documents the built-in Histogram chart for Microsoft 365, Excel 2024 and 2021 (Windows and Mac), and Excel 2019 and 2016 (Windows). Excel for iPad and iPhone also has it and, per Microsoft, requires a Microsoft 365 subscription. If your version lacks the chart, use the FREQUENCY function or the Analysis ToolPak described below.
How to make a histogram in Excel: step by step
Method 1: the built-in Histogram chart
- Put the raw values in one column (not pre-counted frequencies) and select them.
- Windows: choose Insert > Insert Statistic Chart > Histogram. Mac: select the Insert tab, then the Statistical icon, then under Histogram select Histogram.
- Style the chart from the Chart Design and Format tabs.
- Right-click the horizontal axis and choose Format Axis, then open Axis Options to change the bins.
How to set the bins (how to construct a histogram in Excel with the width you want)
In Axis Options Microsoft lists these choices:
- By Category: for text-based categories instead of numbers. Identical categories are grouped and their values summed on the value axis. To count text entries, add a column of 1s and chart that column.
- Automatic: the default for histograms; Excel sets the width using Scott’s normal reference rule.
- Bin width: a positive decimal number giving the range covered by each bin.
- Number of bins: the number of bins, including the overflow and underflow bins.
- Overflow bin: one bin for all values above the number you enter.
- Underflow bin: one bin for all values below or equal to the number you enter.
Boundary rule: a value falls in a bin when it is greater than the bin’s lowest bound and equal to or less than its greatest bound. A value sitting exactly on an edge therefore belongs to the bin that ends at that edge. To choose a sensible width, see how to calculate class width.
Method 2: the Analysis ToolPak
- Windows: File > Options > Add-Ins. In the Manage box choose Excel Add-ins and select Go, check Analysis ToolPak and select OK. On Mac, go to Tools > Excel Add-ins and check Analysis ToolPak.
- Select Data Analysis on the Data tab and choose the Histogram tool.
- Enter your data and your bin numbers (kept in two columns) and send the output to a new worksheet or workbook.
Microsoft describes the tool as calculating “individual and cumulative frequencies for a cell range of data and data bins.” Unlike the chart method it produces a frequency table, so the bin edges are cells you control.
Method 3: the FREQUENCY function
The syntax is FREQUENCY(data_array, bins_array); both arguments are required. The result has one more element than bins_array: the extra last element counts the values above the highest interval. Blank cells and text are ignored. In Microsoft 365 enter the formula in the top-left cell of the output range and press ENTER. In older versions, select the output range, enter the formula, and press CTRL+SHIFT+ENTER. You can then plot the counts as an ordinary column chart.
Worked example: check Excel’s counts by hand
This is an illustrative made-up dataset, not real measurements. Twelve values: 12, 15, 18, 21, 22, 25, 27, 30, 31, 34, 38, 45. Suppose you choose upper bin edges of 19, 29 and 39 (so bins_array has three cells and the output has four).
| Bin (greater than, up to and including) | Values | Count |
|---|---|---|
| up to 19 | 12, 15, 18 | 3 |
| above 19 up to 29 | 21, 22, 25, 27 | 4 |
| above 29 up to 39 | 30, 31, 34, 38 | 4 |
| above 39 (the extra element) | 45 | 1 |
The counts sum to 12, the number of values, which is the quick sanity check for any histogram table. If your counts do not total the number of numeric cells, see troubleshooting below.
Output and how to read it
Read the shape first. A long tail to the right suggests right skew (right-skewed histograms), a long tail to the left suggests left skew (left-skewed histograms), and two or more peaks point to a multimodal distribution. Isolated bars far from the rest flag possible outliers. Report the bin width or bin edges with the figure, because the same data can look different under different bins. For a normal curve overlay see histograms and the normal distribution.
Troubleshooting
- Text instead of bars by value. If the horizontal axis is text-based, choose By Category; Excel then groups identical categories and sums the values.
- Counts do not add up. FREQUENCY ignores blank cells and text, so numbers stored as text are not counted.
- One more bar than expected. FREQUENCY returns one extra element for values above the highest bin; the chart’s Number of bins setting includes the overflow and underflow bins.
- Counts differ from another program. Excel assigns an edge value to the bin that ends at that edge (greater than the lower bound, up to and including the upper bound). Check which rule the other software uses.
- Histogram option missing. Check your version against the list above, or use the ToolPak or FREQUENCY.
Other software for histograms
| Software | CASRAI guide |
|---|---|
| R | Histograms in R |
| SPSS | Histogram in SPSS |
| Stata | Histograms in Stata |
FAQ
How do I make a histogram in Excel?
Select the raw values and use Insert > Insert Statistic Chart > Histogram on Windows, or the Statistical icon on the Insert tab on Mac. Adjust bins under Format Axis > Axis Options.
How do I plot or draw a histogram in Excel without the Histogram chart?
Count the values per bin with FREQUENCY (or the Analysis ToolPak’s Histogram tool), then plot the counts as a column chart.
How do I construct a histogram on Excel with my own bin edges?
Set Bin width or Number of bins in Axis Options, add overflow or underflow bins if needed, or list your edges in cells and use FREQUENCY or the ToolPak.
Which bin does a value on the edge go into?
The bin where it is equal to the greatest bound: a value falls in a bin when it is greater than the lowest bound and equal to or less than the greatest bound.
Sources
- Microsoft Support, Create a histogram (checked 2026-10-29)
- Microsoft Support, Use the Analysis ToolPak to perform complex data analysis (checked 2026-10-29)
- Microsoft Support, FREQUENCY function (checked 2026-10-29)
- NIST/SEMATECH e-Handbook of Statistical Methods, 1.3.3.14 Histogram (checked 2026-10-29)
