Probability Histogram in Excel: A Step-by-Step Guide

Charts & pivot tables · Updated November 18, 2026

To make a probability histogram in Excel, divide your numeric data into bins, count the observations in each bin, divide each count by the total number of observations, and chart those probabilities as columns with no gaps. This produces an empirical histogram whose bar heights show the share of your sample in each interval. The steps below are for Microsoft Excel for Microsoft 365 on Windows.

What You’ll Need

  • A column of numeric observations, such as measurements, test scores, or wait times. Keep the data in one range and use a clear heading above it.
  • A set of bin intervals that covers the smallest and largest values. Equal-width bins make the chart easier to read and compare.
  • Excel for Microsoft 365 on Windows. Excel for Mac has similar chart and formula features, but some ribbon labels or locations can differ slightly.

You do not need Excel’s built-in Histogram chart for this method. That chart is useful for displaying counts, but it does not automatically turn the vertical axis into probability. Building a small frequency table first gives you control over the probability calculation and the chart.

Step-by-Step

  1. Put the observations in one column. For a worked example, enter these 12 values in A2:A13: 10, 12, 14, 16, 18, 20, 22, 24, 26, 28, 30, and 32. Put a heading such as “Value” in A1. Your own data can have more rows; replace the example range in the formulas with your actual range. Make sure Excel recognizes the entries as numbers, not text.
  2. Choose bins that cover the data. Create a table with headings in C1:G1: “Bin,” “Lower,” “Upper,” “Frequency,” and “Probability.” In C2:C6, enter labels such as [10, 15), [15, 20), [20, 25), [25, 30), and [30, 35]. In D2:E6, enter the matching numeric bounds: 10 and 15, 15 and 20, 20 and 25, 25 and 30, then 30 and 35. These intervals include the lower endpoint but exclude the upper endpoint, except for the final bin, which includes its upper endpoint. This convention avoids counting a value on a boundary twice.
  3. Count the values in the first bin. With the observations in A2:A13 and the first bin’s lower and upper limits in D2 and E2, enter this in F2, then fill it down through F5 for the non-final bins: =COUNTIFS($A$2:$A$13,">="&D2,$A$2:$A$13,"<"&E2). COUNTIFS counts numeric values that meet both boundary conditions. As you fill down, the references to the bin limits change by row while the data range stays fixed.
  4. Count the final bin separately. The final interval includes its upper limit, so use a less-than-or-equal condition in F6. With data in A2:A13 and the last bin’s bounds in D6 and E6, enter: =COUNTIFS($A$2:$A$13,">="&D6,$A$2:$A$13,"<="&E6). In the example, the frequencies in F2:F6 should be 3, 2, 2, 2, and 3. They add up to 12, the number of observations.
  5. Convert each frequency to a probability. With the frequencies in F2:F6 and the numeric observations in A2:A13, enter this in G2 and fill it down through G6: =F2/COUNT($A$2:$A$13). COUNT returns the number of numeric observations, so each bin’s count is divided by the same total. For the example, the probabilities are 0.25, about 0.1667, about 0.1667, about 0.1667, and 0.25. Together they equal 1, apart from any display rounding.
  6. Check that the bins account for the whole sample. Add the frequencies and probabilities, or check the totals visually. In the example, the frequency total is 12 and the probability total is 1. If the frequencies do not add to the number of numeric observations, check whether the bins leave a gap, overlap, or fail to include an endpoint. Also check that the data range includes every observation.
  7. Format the probabilities for readability. Select G2:G6 and choose Home > Number, then select Percentage or use the percent-style button. The example will display as 25%, approximately 16.67%, approximately 16.67%, approximately 16.67%, and 25%. Percentage formatting changes how the values appear, not the underlying calculations. Two decimal places are usually enough; rounding the displayed values may make their displayed total look slightly different from 100%.
  8. Insert a column chart using the bin labels and probabilities. Select C1:C6, then hold Ctrl and select G1:G6. Choose Insert > Insert Column or Bar Chart > 2-D Column > Clustered Column. Excel should use the bin labels as the horizontal categories and the probabilities as the bar heights. On a Mac, use Command instead of Ctrl when selecting the second range. If selecting two ranges is awkward, copy the bin labels and probability values next to each other in a separate chart-ready table and select that table instead.
  9. Make the columns touch and label the axes. Click a bar, right-click, and choose Format Data Series. Under Series Options, set Gap Width to 0% or a small value. Touching bars make the chart read like a histogram; a small gap can help distinguish bins. Add a useful chart title, such as “Probability by Measurement Interval.” Use Chart Design > Add Chart Element > Axis Titles to label the horizontal axis with the measured quantity and the vertical axis “Probability” or “Probability of observations.”
  10. Set a sensible vertical scale. Right-click the vertical axis and choose Format Axis. For probabilities displayed as decimals, a maximum of 1 is a clear scale when the tallest bars allow it. If you formatted the values as percentages, use a percentage number format and a suitable maximum such as 100%. The axis does not have to reach 1 if that would make the bars hard to compare, but its scale should be clear and should not exaggerate small differences.

The finished chart is an empirical probability histogram: each bar represents the proportion of recorded observations falling in one interval. In the example, three of the 12 values fall in the first bin, so its probability is 3/12, or 25%. This is a sample-based estimate, not necessarily the true probability for every future observation.

Common Mistakes

  • Charting counts instead of probabilities. A chart made from the Frequency column shows how many observations are in each bin. That is a frequency histogram, not a probability histogram. Use the Probability column for bar heights when the goal is to show proportions.
  • Using inconsistent boundaries. If adjacent bins both include a shared endpoint, a value on that boundary may be counted twice. If neither includes it, the value may be missed. The example uses lower-inclusive, upper-exclusive bins, then includes the final upper endpoint.
  • Leaving data outside the bins. Values below the first lower limit or above the final upper limit will not be counted. Set the first and last limits to cover the full data range, then confirm the frequency total matches the numeric sample size.
  • Including blanks or text in the denominator by mistake. COUNT counts numeric cells, whereas COUNTA also counts text. Use a clean numeric data range and make sure the denominator reflects the observations being summarized. If a number is stored as text, Excel may not count it as numeric.
  • Calling bar height a probability density when bins have different widths. Dividing each bin count by the total gives the probability in that bin. With unequal-width intervals, those probabilities do not make bar area proportional to probability. For a density histogram, divide each bin probability by its width and label the vertical axis as probability density.
  • Rounding the calculation before charting. Keep the full precision in the Probability cells and only format the display. Rounding the underlying values can make the probabilities add to noticeably less or more than 1.

Tips

  • Start with a modest number of equal-width bins. Too few can hide the shape of the data; too many can make the chart look noisy. Try a different bin count and compare the overall pattern rather than treating one choice as definitive.
  • For easier maintenance, put the data in an Excel Table using Insert > Table. A table can expand as you add rows, although you will need to ensure the formulas and chart include the new observations. If you prefer a fixed range, check it whenever the dataset grows.
  • Keep both Frequency and Probability columns in the worksheet. Frequencies help verify the calculation, while probabilities are the chart values. A clearly labeled table makes the result easier for another person to audit.
  • Use decimal probabilities from 0 to 1 or percentages from 0% to 100%, but do not mix the two formats within one chart. State which one you chose in the vertical-axis title if the chart may be read outside its worksheet.
  • If your data are discrete categories or whole-number outcomes, bins may represent individual outcomes rather than ranges. Count each outcome and divide by the total. The result is a probability distribution chart; use interval bins when the variable is measured on a continuous scale.
  • For a distribution with unequal bin widths, use probability density as the bar height if the bar areas should represent probability. The calculation is each bin’s probability divided by its width; make sure the vertical-axis label says “Probability density,” not simply “Probability.”

Troubleshooting

The probabilities do not add to 1. First compare the frequency total with the number of numeric values in the source range. If the frequency total is smaller, inspect the smallest and largest observations and look for gaps between bin limits. If it is larger, check for overlapping boundaries or duplicate source rows. Also verify that the denominator counts the same observations that the bins summarize.

A bin has a surprising zero or very large count. Confirm that the lower and upper values are numbers, not text, and that the formula points to the intended row of the bin table. Check whether the selected interval is narrow enough to contain any observations. For boundary values, apply the chosen endpoint convention consistently.

The chart shows the wrong labels or series. Right-click the chart and choose Select Data. Check that the series values point to G2:G6 and the horizontal category labels point to C2:C6. If Excel interpreted the two selected ranges incorrectly, edit those references there or rebuild the chart from a two-column chart-ready table.

The bars have gaps or look like an ordinary column chart. Select a bar, open Format Data Series, and reduce Gap Width. A zero gap is a familiar histogram appearance, but narrow gaps can improve readability when bin labels are long. Make sure the horizontal labels are ordered by interval, not alphabetically.

Excel shows a formula error or zero counts. Make sure the formula begins with an equals sign, the data range uses the correct column and rows, and the bin limits are numeric. If your Excel regional settings use semicolons rather than commas as argument separators, Excel may expect semicolons when you type formulas; the examples here use the standard comma separator.

Frequently Asked Questions

Is a probability histogram the same as a frequency histogram?
A frequency histogram uses counts as bar heights. A probability histogram uses each bin’s count divided by the total number of observations, so its bar heights are proportions and sum to 1 for a complete set of bins.

Does Excel’s built-in Histogram chart show probabilities?
Excel’s Histogram chart is designed to group data and show frequencies. For a probability histogram, calculate the bin probabilities in a worksheet table and chart that probability column with a column chart.

Do the probabilities always have to add up to exactly 1?
The underlying values should add to 1 when the bins include every observation and do not overlap. The displayed total may appear slightly different because percentages or decimals are rounded for presentation.

Can I use bins with different widths?
Yes, but probability per bin alone is not a density height when widths differ. If the purpose is to compare distribution shape by bar area, divide each bin probability by that bin’s width and use a probability-density axis label.

What if my observations are categorical?
A histogram is intended for numeric values grouped into intervals. For categories such as colors or product types, calculate each category’s share of the total and use a bar chart instead.

Will the chart update when I add more data?
It will update only if its formulas and chart source include the added rows. An Excel Table can make expanding data easier, but check that the frequency formulas, denominator, and chart range have all expanded before relying on the updated probabilities.

Related

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.