Relative Frequency Histogram in Excel: Steps and Formulas

Charts & pivot tables · Updated November 21, 2026

To make a relative frequency histogram in Excel, create bins, count how many observations fall in each bin, divide each count by the total number of observations, and chart the resulting percentages. In Microsoft Excel for Microsoft 365 on Windows, a reliable approach is to build a frequency table with COUNTIFS and insert a column chart; Excel’s built-in Histogram chart shows frequencies rather than automatically converting them to relative frequencies.

What a Relative Frequency Histogram Shows

A histogram groups numerical observations into ranges called bins and displays how many values fall in each range. A relative frequency histogram displays each bin’s share of all observations instead of its raw count. For example, if 4 of 10 measurements fall in a bin, that bin’s relative frequency is 0.4, or 40%.

The percentages make it easier to compare distributions when data sets have different sample sizes. A bin containing 20 observations might look large in a data set of 100, but small in a data set of 1,000. Relative frequency puts both distributions on a comparable proportion scale. The bars still represent ranges of numerical values, so the horizontal axis should be ordered and the bars should normally touch, as they do in a histogram.

Relative frequency is not the same as cumulative relative frequency. The first describes the share in one bin; cumulative relative frequency adds the shares from the first bin through the current bin. A cumulative display is useful for questions such as what proportion of measurements are below a threshold, but it is usually shown as a cumulative table or curve rather than a standard histogram.

Prepare the Data and Choose Bins

Put the observations in one column, with a single numeric measurement in each cell. The example below uses ten values in B2:B11: 2, 4, 5, 7, 8, 9, 10, 12, 13, and 15. Put the lower and upper limits for each bin in separate columns. For this integer-valued example, use the non-overlapping ranges 0–4, 5–9, 10–14, and 15–19.

Bin choice affects the appearance and interpretation of the chart. Too few wide bins can hide patterns, while too many narrow bins can make random variation look like a meaningful pattern. Select ranges that suit the data’s units and purpose, and keep their widths consistent when practical. There is no single bin count that is best for every data set; consider the number of observations, the spread of the values, and whether the chart is meant to reveal detail or summarize the overall shape.

  • Use ranges that cover the smallest and largest valid observations.
  • Make the limits unambiguous. The example uses whole-number values and inclusive endpoints, so 4 belongs to the first bin and 5 to the second.
  • For decimal data, write down a consistent boundary rule, such as including the lower limit and excluding the upper limit. Make the final bin include the largest value if necessary.
  • Check for values outside the selected bins. Otherwise, the percentages in the table may add to less than 100% because some observations were not counted.

Build the Relative Frequency Table

In the example, keep the observations in B2:B11, enter bin lower limits in D2:D5 and upper limits in E2:E5, and add headings such as Lower, Upper, Count, and Relative frequency in row 1. Enter 0 and 4 in D2 and E2, then 5 and 9 in D3 and E3, 10 and 14 in D4 and E4, and 15 and 19 in D5 and E5. These ranges are suitable for the example’s integer measurements.

With observations in B2:B11 and the current bin’s lower and upper limits in D2 and E2, enter this formula in F2 to count values on both inclusive endpoints:

=COUNTIFS($B$2:$B$11,">="&D2,$B$2:$B$11,"<="&E2)

Copy F2 down through F5. The counts are 2, 4, 3, and 1: two values fall from 0 through 4, four from 5 through 9, three from 10 through 14, and one from 15 through 19. The count column should total ten, matching the number of observations. COUNTIFS is available in current Excel versions, including Microsoft 365.

With observations in B2:B11 and the first bin’s count in F2, enter this formula in G2 to divide the bin count by the number of numeric observations:

=F2/COUNT($B$2:$B$11)

Copy G2 down through G5, then select G2:G5 and choose Home > Number > Percent Style. The results are 20%, 40%, 30%, and 10%. Their sum is 100%, which confirms that every numeric observation was included exactly once. The COUNT function counts numeric cells, so blank cells do not inflate the denominator.

With the relative frequencies in G2:G5, enter this formula in G6 if you want a visible check on their total:

=SUM(G2:G5)

G6 should display 100% when formatted as a percentage. If it does not, check whether values lie outside the bins, whether ranges overlap, or whether the denominator includes a different set of observations than the counts do.

Create the Chart

Excel’s native Histogram chart starts from the raw observations and groups them into bins. For a relative frequency display, use the completed frequency table so the chart’s values are the percentages rather than counts. Create a readable label for each bin, such as 0–4, 5–9, 10–14, and 15–19, in H2:H5, or use another column for the labels.

  1. Select the four bin labels and the corresponding relative frequencies. If the ranges are in separate columns, select the labels first and then, while holding Ctrl on Windows, select G2:G5. Alternatively, arrange labels beside relative frequencies and select the adjacent cells together.
  2. Choose Insert > Insert Column or Bar Chart > Clustered Column. The chart should show one column for each bin.
  3. Add a descriptive chart title, such as Relative Frequency of Measurements. Label the horizontal axis with the measurement or bin ranges and the vertical axis Relative frequency.
  4. To make the columns touch, right-click a column, choose Format Data Series, and reduce Gap Width to 0% or a small value. A zero gap is a common histogram convention, but clear labels and consistent bin widths matter more than cosmetic styling.

Use percentage formatting for the vertical axis if Excel displays decimals such as 0.2 and 0.4. Select the axis, open Format Axis, and set its number format to Percentage. The maximum can be set to 1, or 100% after percentage formatting, when that scale suits the data; do not force it if doing so makes the differences hard to read. Excel for Mac has the same underlying chart and formula capabilities, though ribbon layout and some formatting-pane details can differ. In Excel for the web, chart commands and panes may also be arranged differently.

Interpret the Shape and Check the Result

Read a bar as the proportion of the full data set that falls in its bin. In the example, the 5–9 bin contains 40% of the measurements, the largest share. That does not mean 40% of the values are equal to 5 or equal to 9; it means 40% fall anywhere in that range. A histogram summarizes the distribution, not the individual observations.

Check the table before drawing conclusions from the chart. The bin counts should add to the number of valid observations, and the relative frequencies should add to 1 or 100%, subject to small display-rounding differences. Confirm that the bins are in ascending order and that the chart uses the relative frequency column rather than the count column. Also consider whether a bin’s width is comparable with the others: with unequal widths, bar height as a proportion alone can give a misleading impression of concentration. In that situation, a density scale may be more appropriate than a basic relative-frequency chart.

Common Problems and Fixes

  • The percentages do not total 100%: Some observations may be outside the bin limits, or a boundary rule may leave a gap. Inspect the smallest and largest values and make sure every valid observation falls in exactly one bin.
  • A value appears in two bins: This can happen when adjacent ranges both include the shared boundary. For decimal or continuous measurements, use a consistent half-open rule, for example lower limit inclusive and upper limit exclusive, with the final bin adjusted to include the maximum.
  • The chart shows counts instead of percentages: Check the selected data range. The chart’s series values must come from the relative frequency column, not the count column.
  • The result is unexpectedly small: Verify that cells contain numbers rather than numeric-looking text. COUNT ignores text, and numeric comparisons in COUNTIFS will not treat every text entry as a number.
  • The bars have gaps: A clustered column chart uses a gap by default. Adjust Gap Width in Format Data Series to make it resemble a histogram.
  • Percentages look rounded and add to 99% or 101%: The underlying proportions may still sum to 100%; displayed values rounded to whole percentages can create this appearance. Increase decimal places to inspect the values.

Relative Frequency Table Versus Excel’s Histogram Tool

The built-in Histogram chart is convenient when the goal is to see counts and let Excel group the raw data. Select the data, then use Insert > Insert Statistic Chart > Histogram in Excel for Microsoft 365 on Windows. You can adjust bin settings through the horizontal axis formatting options. However, the chart does not simply offer a switch that turns each bar into a share of the total. For an explicitly relative-frequency chart, calculate the table first and chart its percentage column.

The Analysis ToolPak can also create a frequency output table, but it is not necessary for the formula method. A table based on COUNTIFS is transparent: the bin limits, counts, and denominator are visible and can be checked or changed directly. This can be helpful when a report needs fixed, documented intervals, or when the chart must use particular percentage labels. Both approaches can be useful; choose the one that gives the needed bin control and makes the calculation easy to audit.

Frequently Asked Questions

Does Excel have a relative frequency histogram chart type?
Excel’s built-in Histogram chart is designed to display frequencies for bins. To show relative frequencies, calculate each bin’s share in a separate table and make a column chart from that percentage column.

How do I calculate relative frequency in Excel?
Divide the number of observations in a bin by the total number of numeric observations. In the example, the bin count is divided by COUNT of the data range, then formatted as a percentage.

Should relative frequencies add to 1 or 100%?
They should add to 1 as proportions, or 100% when formatted as percentages, if the bins cover every observation exactly once. Small displayed discrepancies can result from rounding.

Can I use a PivotTable for relative frequencies?
Yes. A PivotTable can group suitable numeric data and show counts as a percentage of a total, but the exact grouping and display choices depend on the data layout and Excel interface. A formula table is often easier when you need explicit bin boundaries or a clearly controlled denominator.

What is the difference between frequency and relative frequency?
Frequency is the number of observations in a bin. Relative frequency is that count divided by the total number of observations, so it expresses a proportion or percentage and can be compared across data sets of different sizes.

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.