Excel’s built-in Histogram chart shows frequency—the number of observations in each bin—not a normalized percentage option. To show the share of observations in each bin, calculate each bin’s count divided by the number of valid observations, then chart those proportions; formatting a count axis as Percentage does not perform that calculation.
What Excel’s Histogram Chart Shows
In Microsoft Excel for Microsoft 365 on Windows, select a column of numeric data and choose Insert > Insert Statistic Chart > Histogram to create a histogram. Excel groups the values into ranges called bins and plots how many values fall into each range. The vertical axis is therefore a count, or frequency.
The chart’s Format Axis options let you adjust choices such as bin width, number of bins, and underflow or overflow bins. Those settings affect how the data is grouped, but they do not convert the frequencies into percentages. If a bin contains 12 observations, its bar represents 12 observations, not 12 percent—unless you have separately calculated and plotted a proportion.
This distinction matters because number formatting changes how a value is displayed, not what the value means. For example, formatting the number 12 as a percentage displays 1,200%. It does not turn 12 observations out of 100 into 12%. The calculation must come first.
Frequency, Percentage, and Cumulative Percentage
A bin’s percentage, also called its relative frequency, is its count divided by the total number of observations included in the analysis. If 18 of 120 measurements fall in a particular range, that bin represents 15% of the observations. Percentages across all bins add to 100% when the bins cover the data and each observation is counted exactly once.
Cumulative percentage answers a different question: what share of observations is at or below a particular point? If the first three bins contain 10%, 15%, and 20% of the observations, the cumulative percentage at the third bin is 45%. A cumulative series is useful for questions such as “What percentage of delivery times are under 30 minutes?” It should not be confused with the percentage in an individual bin.
Excel’s Pareto chart is also different from a percentage histogram. A Pareto chart orders categories by frequency and can display a cumulative percentage line. That presentation is useful for ranked categories, such as reasons for customer complaints, but it does not replace a histogram when the goal is to show the distribution of continuous numeric measurements across intervals.
Calculate Percentages for Your Bins
A straightforward approach is to create a small frequency table, calculate each bin’s share, and make a column chart from that table. The example below assumes 100 numeric test scores in A2:A101, with five bins: 0 to less than 20, 20 to less than 40, 40 to less than 60, 60 to less than 80, and 80 through 100. The example counts are 10, 25, 35, 20, and 10, respectively.
- Enter each bin’s lower limit in C2:C6 and its upper limit in D2:D6: 0 and 20, 20 and 40, continuing through 80 and 100.
- In E2:E6, calculate the number of scores in each interval. Use inclusive lower limits and exclusive upper limits for the first four bins so a score on a boundary is counted only once.
- In F2:F6, divide each bin count by the number of numeric scores. Format the results as percentages.
- Enter clear bin labels in G2:G6, such as “0 to <20” and “80 to 100,” and put the percentage values in H2:H6 for charting.
With the lower limits in C2:C6, upper limits in D2:D6, and source scores in A2:A101, enter this in E2 and copy it through E5: =COUNTIFS($A$2:$A$101,">="&C2,$A$2:$A$101,"<"&D2). For example, if 10 scores fall from 0 up to but not including 20, E2 returns 10. For the final bin, use this formula in E6 so a score of exactly 100 is included: =COUNTIFS($A$2:$A$101,">="&C6,$A$2:$A$101,"<="&D6). If that bin contains 10 scores, E6 returns 10.
With the counts in E2:E6 and all 100 numeric scores in A2:A101, enter this in H2 and copy it through H6: =E2/COUNT($A$2:$A$101). If E2 is 10, the result is 0.1, which displays as 10% when formatted as a percentage. The five example counts total 100, so the five percentages—10%, 25%, 35%, 20%, and 10%—also total 100%.
Build a Percentage Histogram-Style Chart
After calculating the shares, select the bin labels and percentage values, including their headings. In the example, select G1:H6. On Windows, choose Insert > Insert Column or Bar Chart > 2-D Clustered Column. This creates a regular column chart from the percentage table rather than Excel’s automatic Histogram chart.
To make the columns look more like histogram bars, right-click a column, choose Format Data Series, and reduce Gap Width in Series Options. A gap width of 0% makes neighboring columns touch. This visual choice is appropriate when the bins represent continuous, adjoining ranges. If the groups are distinct categories rather than continuous intervals, leaving some space between columns may be clearer.
Check that the vertical axis is displaying percentages. Right-click the vertical axis, choose Format Axis, and in the Number settings select Percentage. This formatting is correct because the plotted values are proportions such as 0.1 and 0.25. If you instead charted raw counts such as 10 and 25, formatting the axis as Percentage would misleadingly display 1,000% and 2,500%.
You can add values above the columns by selecting the chart and choosing Chart Design > Add Chart Element > Data Labels. Data labels are useful when readers need to compare exact shares, while the axis helps them compare the overall shape. If you change the source data or bin limits, check that the formulas and chart range still include all relevant rows.
Choose Bin Widths That Make Sense
The percentage calculation depends on which observations belong in each bin, so bin design is part of the analysis. Equal-width bins are a useful starting point for many distributions because the bar heights are easy to compare. For test scores, ranges of 20 points may be understandable; for response times, ranges of five seconds might be more informative. Use units that make sense to the people reading the chart.
Very wide bins can hide important patterns, such as two clusters of values. Very narrow bins can make a small dataset look irregular because only a few observations fall into each interval. Try a reasonable width, check whether the chart answers the question, and adjust it if meaningful detail is obscured. Excel’s automatic binning can be a starting point, but it is not guaranteed to be the clearest choice for every dataset.
Take extra care with unequal-width bins. A bin containing 20% of observations may be wider than its neighbor, so its bar can appear more prominent simply because it covers a larger interval. When comparing concentration across intervals of different widths, consider plotting density—percentage divided by bin width—instead of percentage alone, or use equal-width bins. Label the intervals clearly so readers do not assume that every bar covers the same range.
Check the Denominator and Bin Boundaries
Percentages only add to 100% when the denominator and bins describe the same set of observations. In the example, COUNT includes numeric cells in A2:A101. Blank cells and text entries are not counted. If some numeric scores are below 0 or above 100, the specified bins will omit them, and the percentages will sum to less than 100% when divided by the count of all scores.
Decide how to handle invalid, missing, or out-of-range values before calculating shares. You might exclude them intentionally, add underflow and overflow bins, or adjust the bin limits. State that choice in a chart note when it could affect interpretation. Also check boundary rules: using an inclusive upper limit for one bin and an inclusive lower limit for the next can count a value on the shared boundary twice.
A useful check is to compare the total of the bin counts with the number of observations intended for analysis. If the totals differ, investigate gaps between bins, overlaps, text stored in place of numbers, or data outside the chosen limits. Do not quietly force the percentages to add to 100% by changing the denominator; first establish why observations are missing from the bins.
Common Misconceptions and Troubleshooting
- “I can just format the frequency axis as Percentage.” Not if the chart contains counts. Calculate count divided by total first, then format the resulting fractions as percentages.
- “A percentage histogram must use Excel’s Histogram chart type.” It does not. A column chart built from a frequency table can show percentage values and offers direct control over labels and bin boundaries.
- “The percentages do not add to 100%, so the formula is wrong.” Check for data outside the bins, blank or text cells, and whether the denominator includes excluded observations. Small displayed differences can also result from rounding; inspect the underlying values before changing calculations.
- “The first bar is missing some boundary values.” Confirm the lower and upper limit rules. A consistent convention—lower bound included and upper bound excluded, except for the final bin—avoids double counting shared boundaries.
If you prefer the automatic Histogram chart, keep it for a count-based view and add a separate percentage table or chart for relative frequencies. That makes the distinction visible rather than implying that a frequency axis has been normalized. In Excel for Mac, the same calculation approach works; chart and formatting controls may be arranged differently from the Windows ribbon, so use the Insert chart controls and Format Axis pane available in your version.
Frequently Asked Questions
Can Excel’s built-in Histogram chart show percentages directly?
No simple percentage switch converts its bin frequencies into relative frequencies. Calculate each bin’s count divided by the relevant total, then plot those results in a column chart.
How do I show 10% instead of a count of 10?
If there are 100 observations in total, calculate 10 divided by 100 to get 0.1, then format that result as Percentage. If the total differs, use the actual number of valid observations as the denominator.
Why does my percentage axis show 1,000%?
The plotted value is probably 10 rather than 0.1. Percentage formatting multiplies a stored value by 100 for display; divide the count by the total before applying that format.
Should the percentages in a histogram add up to 100%?
They should if every included observation belongs to exactly one bin and the denominator is the number of observations covered by those bins. If not, check the range limits, out-of-range data, and denominator.
What is the difference between percentage and cumulative percentage?
A bin percentage is the share inside one interval. A cumulative percentage adds the shares up through a specified bin, showing the proportion at or below that interval’s upper limit.
Can I use a percentage histogram with unequal bin widths?
You can, but bar heights then show the share in each interval, not concentration per unit of measurement. Equal-width bins are easier to compare; with unequal widths, consider density and label the interval sizes clearly.
Related
- Excel Heat Map Over a Map: Methods, Data, and Setup
- Excel Heat Map UK: Create and Format One in Microsoft 365
- Excel Heat Map: How to Create and Customize One
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Pivot Charts in Excel: Create, Customize, and RefreshTo make a PivotChart in Excel for Microsoft 365 on Windows, select a cell in a clean data…
- Excel Pivot Table: Show Only Filtered Data in Your Report?Yes. In Excel for Microsoft 365 on Windows, filter a PivotTable field using its dropdown, or add a…
- Excel Bar Chart Different Color Bars: How to Set ThemYes. In Excel for Microsoft 365 on Windows, select the chart, right-click a bar, choose Format Data Series,…
- Excel Pivot Table Group by Value Range: How It WorksYes. In Excel for Microsoft 365 on Windows, put a numeric field in a PivotTable’s Rows or Columns…
- Can You Rotate a Pie Chart in Excel? Steps and TipsYes. In Excel for Microsoft 365 on Windows, select the pie chart, right-click a slice, choose Format Data…
- Excel Pivot Table: How to Use the Same Field TwiceYes. In Excel for Microsoft 365 on Windows, you can add the same source field to a PivotTable’s…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.