Excel Histogram: Show Percentages Instead of Counts

Excel’s built-in Histogram chart displays counts, not the percentage of observations in each bin. To show percentages, calculate each bin’s relative frequency in a worksheet, format those results as percentages, and chart the table; this keeps the bar heights mathematically correct.

What a histogram percentage means

A histogram groups numeric observations into intervals called bins. The height of each bar represents how many observations fall into that interval. To show a percentage instead, divide each bin’s count by the number of numeric observations included in the analysis. This is called relative frequency.

For example, if 4 of 12 measurements fall in a particular bin, its percentage is 4 ÷ 12, or 33.3%. Across bins that cover every observation, the percentages add up to 100%, allowing for rounding. The bars still describe the distribution; their vertical values are now shares of the total rather than raw counts.

This is different from changing the number format on the built-in chart’s count axis. Formatting a count such as 4 as a percentage does not turn it into 33.3%; it only changes how Excel displays the number. The percentage must be calculated from the count and the sample size.

Make a percentage table from your data

For a dependable result, create a small frequency table next to the raw data. The example below assumes 12 numeric observations in A2:A13: 5, 8, 12, 15, 18, 20, 22, 25, 27, 31, 34, and 38. Set up bin upper limits in B2:B5 as 10, 20, 30, and 40. Those limits define the groups: up to 10, over 10 through 20, over 20 through 30, and over 30 through 40.

With the observations in A2:A13 and the first bin’s upper limit in B2, enter this in C2 to calculate the first bin’s share:

=COUNTIF($A$2:$A$13,"<="&B2)/COUNT($A$2:$A$13)

With the same data range and the current and previous upper limits in B3 and B2, enter this in C3, then fill it down through C5:

=COUNTIFS($A$2:$A$13,">"&B2,$A$2:$A$13,"<="&B3)/COUNT($A$2:$A$13)

The first bin contains 2 observations, the second 4, and the last two 3 each. The percentages are therefore 16.7%, 33.3%, 25.0%, and 25.0%, which add to 100.0% when displayed to one decimal place. Select C2:C5 and choose Home > Number > Percent Style. Use the Increase Decimal or Decrease Decimal buttons to choose how many decimal places to show.

COUNT counts numeric cells, so blank cells and text in the source range do not increase the denominator. Make sure the bin limits cover the full numeric range: values above 40 in this example would not be counted. If some values should be excluded, define that choice deliberately and use a denominator that matches the observations you intend to summarize.

Build a chart with percentage heights

Once the table is ready, chart the bin labels and percentage values—not the original measurements. In the example, use the upper limits in B2:B5 as labels and C2:C5 as the percentages. You can add a label column with descriptions such as ≤10, 10–20, 20–30, and 30–40. Because the formula’s intervals are open on the left and closed on the right after the first bin, labels such as “>10 to 20” are more explicit if boundary precision matters.

Select the bin-label cells and the corresponding percentage cells. If the columns are not next to each other, select one range, then hold Ctrl on Windows while selecting the other. On Mac, use Command while selecting the additional range.
Choose Insert > Insert Column or Bar Chart > 2-D Column > Clustered Column.
If you want the bars to look more like a traditional histogram, right-click a bar, choose Format Data Series, and set Gap Width to 0% or a small value. This is a visual adjustment; it does not change the percentages.
Add a clear chart title and a vertical axis title such as Percentage of observations. Use Chart Design > Add Chart Element > Axis Titles > Primary Vertical, then edit the title on the chart.

Because the source values are already percentages, Excel plots the correct heights. The vertical axis might show decimal values such as 0.25 depending on the chart and number format. To display percent labels, right-click the vertical axis, choose Format Axis, and set its number format to Percentage. This formats values such as 0.25 as 25%; unlike formatting raw counts as percentages, it is appropriate here because the plotted values are proportions.

Why the built-in Histogram chart shows counts

Excel’s built-in chart is designed to group raw values into bins and show the frequency in each one. In Microsoft 365 for Windows, select the numeric data and choose Insert > Insert Statistic Chart > Histogram. Right-click the horizontal axis and choose Format Axis to adjust options such as bin width, number of bins, and underflow or overflow bins. These controls change how observations are grouped, but they do not convert the bars into per-bin percentages.

The chart’s Cumulative Percentage option, when available in the axis settings, is not the same as a percentage for each individual bin. A cumulative percentage adds each bin’s share to the shares before it, so it rises across the chart toward 100%. For example, if the first two bins contain 16.7% and 33.3%, the cumulative share at the second bin is 50%. Use a cumulative chart when you want to answer how much of the data falls at or below a threshold, not when you want each bar to show its own share.

Excel’s Analysis ToolPak Histogram tool also produces a frequency table with counts. You can use that table as a starting point, divide each frequency by the appropriate total, and chart the resulting percentage column. The ToolPak may need to be enabled through File > Options > Add-ins > Manage: Excel Add-ins > Go, then select Analysis ToolPak. The manually constructed table is often easier to maintain when you want visible formulas or custom labels.

Choose bins and the right denominator

Percentages are meaningful only when the bin rules and denominator are clear. Decide the bin width based on the question and the scale of the data. Narrow bins reveal more detail but can make the pattern noisy; wide bins simplify the picture but can hide clusters or gaps. Use consistent widths where practical, and label the intervals so readers can tell which values are included.

Be especially careful with values exactly on a boundary. In the worked example, the second bin includes 20 because its rule is greater than 10 and less than or equal to 20. The next bin starts above 20, so 20 is counted only once. This convention avoids overlap and gaps when the bins are consecutive. Check that the first and last bins also capture the minimum and maximum values you intend to include.

The denominator should represent the observations being summarized. If the raw range includes numeric values that are intentionally left out of the bins, the percentages will total less than 100%. If the data has blanks, COUNT excludes them; if it contains numbers stored as text, COUNT also excludes those cells. Convert or investigate such entries rather than assuming the percentages are wrong.

Relative frequency, cumulative percentage, and density

Relative frequency is the usual meaning of “percentage per bin”: the bin count divided by the total count. Cumulative percentage answers a different question by adding relative frequencies from the first bin onward. It is useful for identifying the share of observations below a selected boundary, but it should generally be shown as a cumulative line or in a separate table rather than mistaken for the height of each bin.

There is also a distinction between percentage and probability density. When every bin has the same width, plotting percentage per bin makes the bar heights easy to compare. When bins have different widths, a taller bar might simply cover a narrower interval. In that case, a density histogram uses frequency divided by both the total and the bin width, so the bar’s area—not just its height—represents the share. If the goal is a straightforward Excel report with equal-width bins, relative frequency is usually the clearer choice.

Common problems and quick checks

Bars show counts after formatting the axis. Build percentage values in the worksheet first. An axis number format cannot convert frequency counts into relative frequencies.
Percentages do not total 100%. Check whether all observations fit within the first and last bins, whether bins overlap or leave gaps, and whether the denominator includes only the intended numeric records.
A boundary value appears in the wrong group. Confirm whether each bin includes its upper or lower endpoint. State the rule in the labels or a note.
The chart has gaps between bars. For a column chart, reduce Gap Width in Format Data Series. This changes appearance, not the underlying bin calculation.
New data does not appear. Extend the formula and chart source ranges, or convert the source data to an Excel table so added rows are easier to include. Review the bin limits too; new values may fall outside them.

Excel for Mac and Excel for the web

The worksheet approach—COUNTIF or COUNTIFS, division by the numeric total, and a column chart—works in current Excel for Windows and Mac. Ribbon wording and chart-formatting panes can differ slightly between platforms and updates, but the underlying calculations are the same. In Excel for the web, you can create and format ordinary charts and edit formulas, although some advanced chart controls or add-in features may differ from desktop Excel. If you cannot find the built-in Histogram chart or a particular axis setting, the percentage table and clustered-column method avoids depending on that feature.

Frequently Asked Questions

Can I make Excel’s built-in histogram display percentage instead of count?
Not simply by changing its axis number format. Calculate relative frequencies in a worksheet and chart those values, or use the Analysis ToolPak frequency output as a starting point and divide by the total.

Should the percentages in a histogram add up to 100%?
Yes, if the bins are mutually exclusive, cover every included observation, and use the same total as the denominator. Small differences can appear if displayed values are rounded.

Is cumulative percentage the same as percentage per bin?
No. Percentage per bin reports the share in one interval. Cumulative percentage adds the shares up to that interval and normally increases toward 100%.

Can I use the built-in Histogram chart and show percentage data labels?
The built-in chart’s bars are based on frequencies, so adding or formatting labels does not automatically calculate each bar’s share. For accurate percentage heights, chart a table whose values are already relative frequencies.

Why do my histogram percentages add up to less than 100%?
Some values may be outside the bin limits, the bins may have a gap, or the denominator may include records the bins do not count. Check the smallest and largest values and the rules at every boundary.

Which approach to use

Use the built-in Histogram chart when you want a quick count-based distribution and want Excel to manage the bins. Use a calculated percentage table and clustered-column chart when the vertical axis must show each bin’s share, when labels need to be customized, or when you need transparent control over boundary rules. For a cumulative question, calculate and chart cumulative percentages separately. Keeping the bin definitions, denominator, and chart values explicit makes the result easier to interpret and verify.

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.