Relative Frequency in Excel and Google Sheets

The cursor blinks at the bottom of a column of survey responses, and the number 42 sits there, unhelpful. You need to know what fraction of those responses are, say, 'Blue', not just how many, and the answer is one COUNTIF and one SUM away, which is exactly how you’d compute a relative frequency excel table. There are three spreadsheet routes to a relative frequency table: the simple COUNTIF method, the FREQUENCY array function for numeric bins, and the pivot table's percent of grand total, with Google Sheets differences called out where they matter.

Open a fresh sheet and paste your raw data list in column A, one value per cell, header in A1. In column B, list each unique value you care about, or use the data's own distinct values. The COUNTIF function counts cells meeting a single criterion, so this method works for text, numbers, or dates, and it is the most direct route when your categories are already discrete. The failure case: if your data has blank cells, COUNTA will overcount n and every relative frequency will be slightly low. Fix it by using =COUNTA(A2:A1000)-COUNTBLANK(A2:A1000) for n, or better, ensure your raw list has no stray blanks before you start.

The function returns an array of counts: how many values are less than or equal to each bin, with the last cell capturing everything above the top bin. The empty bin error strikes when you do not select enough cells for the array, producing a #N/A, or when you select one too few and lose the final 'above max' count. The off-by-one bin edge: a value exactly equal to a bin boundary, say 20 in the bin '10-20', is counted in that bin because FREQUENCY uses inclusive upper bounds. For Google Sheets, the same FREQUENCY function exists, but you must still use Ctrl+Shift+Enter. For a table without formulas, use a pivot table. Select your raw data list, insert a pivot table, drag the variable to Rows and again to Values. The pivot table's 'show values as % of grand total' is the cleanest method for a two-way table, where you have two variables and want joint relative frequencies: drag one variable to Rows, the other to Columns, and a third field to Values, then set Show Values As to % of Grand Total for every cell as a proportion of the grand total. For pivot tables, the Show Values As menu in Google Sheets sits under the Values drop-down, labeled 'Show as', and you pick '% of grand total' from the list. One more difference: in Google Sheets, you can use =COUNTIF(range, criterion) where the criterion can be a cell reference without concatenation, e.g., =COUNTIF(A:A,B2), which is a minor convenience. The cumulative relative frequency column, which you build manually by adding each relative frequency to the running total, works the same in both, and the final value must equal 1.0, which is the check that catches the rounding cascade: if you rounded each relative frequency to two decimals and they sum to 0.99 or 1.01, do not re-round, adjust the last value to force the sum to 1.00.

The cumulative relative frequency starts with the first value's relative frequency and adds each subsequent one, ending at exactly 1.0. For nominal data like colors, a cumulative column is meaningless because there is no natural order, so skip it. For ordinal or numeric data, the cumulative column lets you answer 'what proportion is below X?' by reading the row where the value equals X. The failure case: if your relative frequencies do not sum to 1.0, you either used a subtotal as the denominator, producing a conditional frequency mislabeled as marginal, or you rounded intermediate values. Check the denominator is the grand total, not a row or column total. The cumulative relative frequency must end at 1.0.

Relative frequency and probability are not the same thing, and the distinction matters. The law of large numbers says that as n increases, the relative frequency approaches the probability, but for small samples they can diverge wildly. Observed data is the subject, not predicting the next draw. If you need to make a claim about a population from a sample, you are doing inferential statistics, and the relative frequency table is only a descriptive step. The denominator drift error happens when you compute a conditional relative frequency, like the proportion of women who prefer tea, but label it as a marginal relative frequency, the proportion of all respondents who prefer tea. A joint relative frequency is a single cell divided by the grand total, e.g., the proportion of respondents who are both female and prefer tea. The conditional is the only one where the denominator is not the grand total, and it answers a different question. In a pivot table, you can get all three: set Show Values As to % of Grand Total for joint, % of Row Total or % of Column Total for conditional, and the row or column totals give you the marginals. The transpose failure: if you compute P(A|B) but use the row total instead of the column total as the denominator, you get a different number, and the table will not read correctly. Always label the denominator in your head: 'out of the total' for marginal, 'out of that row' or 'out of that column' for conditional.

Fix it by carrying full precision in the spreadsheet and only rounding the final displayed value, or by adjusting the last cumulative value to force the sum to 1.00. The empty bin error happens with FREQUENCY when you select the wrong number of cells for the array formula; the solution is to select one cell taller than the bin range. The off-by-one bin edge: decide whether your bins are inclusive of the upper bound, as FREQUENCY is, and be consistent. The chart mislabel: if you create a bar chart of frequencies but label the y-axis 'Relative Frequency' without dividing by n, the chart lies. Do not let a spreadsheet return #DIV/0! without explaining why.

When your data is already binned, like '10-20' or '20-30', you cannot use the raw data list methods directly; you must expand the bins back into individual values or use the midpoint as a proxy. The failure case: if you have a frequency table with bins and counts, and you want a relative frequency table, you do not need the original data at all. The empty bin error occurs when a bin has zero observations; the FREQUENCY function still returns a zero, which is fine, but the cumulative relative frequency stays flat, and a reader may think the data is missing. The rule of thumb for decimal places: use one more decimal place than the raw data, or match the precision of the original measurements. If the raw data is whole numbers, one decimal place is enough. Do not round intermediate cumulative values; round only the final displayed number, or the cumulative column will drift.

For a two-way table, the pivot table method is the fastest way to get all three types of relative frequency, but you must choose the right 'Show Values As' option. The failure case: if you use the default 'Count' setting, you get frequencies, not relative frequencies, and a reader scanning the table will mistake raw counts for proportions. Always decide which variable is the denominator first. For example, if you want to know what proportion of women prefer tea, the denominator is the number of women, so use % of Row Total if women are in rows, or % of Column Total if women are in columns. If you need the cumulative relative frequency in a two-way table, it only makes sense along one dimension, usually the ordinal or numeric variable, and it is not something a pivot table gives you directly; build it manually.

The cumulative relative frequency column answers 'what proportion of the data is at or below this value?', and it is the most misused column in the table. If the cumulative column still does not end at 1.0, you have a missing value or a duplicated bin edge. The off-by-one bin edge is the usual culprit: a value exactly equal to a bin boundary is counted in two bins if you are not careful about whether the upper bound is inclusive. FREQUENCY is inclusive of the upper bound, so '10-20' counts 20 in that bin, not in '20-30'. Proportion confusion: a relative frequency of 0.25 is 25%, and the two are interchangeable, but a cumulative relative frequency of 0.75 means 75% of the data is at or below that value, not that the value itself is 75% of anything.