How to Make a Frequency Table in Excel and R

Q: Should a frequency table include missing values?

Show them as their own row (in R, useNA = "ifany" ) so the table accounts for every observation, or state clearly how many were excluded.

A frequency table lists each value in a data set next to the number of times it occurs. To make one, list the distinct values, count how often each appears, and check that the counts add up to the number of observations. This guide builds the same table three ways from one raw list: by hand, in Excel (PivotTable and the FREQUENCY function) and in R (table()), then adds relative and cumulative columns and a total check. It also covers three common traps: the extra row FREQUENCY returns, missing values that drop out of the count, and numbers stored as text.

What a frequency table is used for in research

OpenStax defines a frequency as “the number of times a value of the data occurs,” and its frequency tables list the different data values in ascending order together with their frequencies (OpenStax, Introductory Statistics 2e, section 1.3, checked 2026-10-29). A frequency table is a simple first look at a variable: it shows the spread of responses, flags impossible or unexpected codes, and gives the counts that later steps (percentages, a histogram, a mean) are built from. Three situations call for slightly different builds:

Categorical data (for example survey answers): one row per category. See levels of measurement if you are unsure which type your variable is.
Discrete numeric data (counts, scores): one row per distinct value, as in the worked example below.
Continuous numeric data: values are first grouped into class intervals, then counted per interval.

Install or access (official source only)

You need only one of the following. Excel’s PivotTable and FREQUENCY function are documented by Microsoft Support; R’s table() is part of the base package, so no add-on package is required (R documentation, base::table, checked 2026-10-29). R is available from the R Project (r-project.org) and Excel from Microsoft. No further software is needed for the by-hand method.

Step-by-step: build a frequency table

All four methods below use the same illustrative raw list of 20 observations. It was constructed so that its counts match OpenStax Table 1.12 (student work hours: values 2 to 7 with frequencies 3, 5, 3, 6, 2, 1), so you can check your result against a published table.

2, 2, 2, 3, 3, 3, 3, 3, 4, 4, 4, 5, 5, 5, 5, 5, 5, 6, 6, 7

Method 1: by hand

List every distinct value once, in ascending order (or, for categories, in a sensible fixed order): 2, 3, 4, 5, 6, 7.
Go through the raw list once and tally each observation against its value. Cross each item off as you tally it so none is counted twice.
Write the tally totals in a Frequency column: 3, 5, 3, 6, 2, 1.
Add the Frequency column. It must equal the number of observations, n = 20. If it does not, recount before going further.
For grouped continuous data, first choose class intervals that do not overlap and cover every observation. OpenStax Table 1.13 (heights of 100 soccer players) uses boundaries such as 59.95–61.95, 61.95–63.95 and so on, with one more decimal place than the data so that no value can fall on a boundary. Tally into those intervals instead of single values.

Method 2: Excel PivotTable

Put the data in a column with a single header row (for example, header “Hours” in A1 and the 20 values below it).
Select a cell in the data, then choose Insert > PivotTable. Microsoft Support describes the dialog: the range is filled in for you, you pick New Worksheet or Existing Worksheet as the destination, and you select OK.
In the field list, drag the Hours field to Rows. Each distinct value now appears once.
Add the same field to Values. If Excel shows a Sum, open the field’s arrow, choose Value Field Settings, and under Summarize Values By select Count. Microsoft notes that text fields display as a count by default; a numeric field needs the setting changed.
Compare the Grand Total with n.

Method 3: Excel FREQUENCY function

Use this when you want to control the intervals yourself, which is what grouped numeric data needs. The syntax is FREQUENCY(data_array, bins_array).

Type the interval upper limits (the bins) in a column, for example 2, 3, 4, 5, 6, 7 in D2:D7.
Select an output range one cell taller than the bins: seven cells, E2:E8.
Enter =FREQUENCY(A2:A21, D2:D7). In Microsoft 365, enter it in the top-left cell of the output range and it fills the range (dynamic array). In older versions, enter it as a legacy array formula with Ctrl+Shift+Enter.
Read the result: the first six cells are the counts for each bin; the seventh is the count of values above the highest bin, which is 0 here.

Method 4: R `table()`

hours <- c(2,2,2,3,3,3,3,3,4,4,4,5,5,5,5,5,5,6,6,7)
tab <- table(hours)
tab
sum(tab)

R prints each distinct value with its count: 2 appears 3 times, 3 appears 5 times, 4 appears 3 times, 5 appears 6 times, 6 appears 2 times and 7 appears once, and sum(tab) returns 20. The R documentation describes the return value as a contingency table: an object of class “table”, an array of integer values. For two variables at once, see crosstabs in R.

To show missing values as their own row, set the useNA argument. Its values are “no” (the default), “ifany” (add an NA row only if there are NAs) and “always” (add it even when the count is zero):

x <- c(2, 3, NA, 3)
table(x)                  # NA is left out: counts sum to 3
table(x, useNA = "ifany") # NA row appears: counts sum to 4

Output and how to read it

Add a relative frequency column (each count divided by n) and a cumulative relative frequency column (the running total of the relative frequencies). OpenStax defines relative frequency as the ratio of the number of times a value occurs to the total, and cumulative relative frequency as the accumulation of the previous relative frequencies. For the example list:

Value	Frequency	Relative frequency	Cumulative relative frequency
2	3	0.15	0.15
3	5	0.25	0.40
4	3	0.15	0.55
5	6	0.30	0.85
6	2	0.10	0.95
7	1	0.05	1.00
Total	20	1.00

In R, prop.table(tab) gives the relative frequencies and cumsum(prop.table(tab)) the cumulative column. In Excel, divide each count by the total cell and add each result to the cell above.

Two checks catch most mistakes. First, the Frequency total must equal n. Second, the relative frequencies should sum to 1; OpenStax notes that because of rounding the column may not sum exactly to one, but each total should be close to one. Report n alongside any percentage. To read the finished table, see interpreting a frequency distribution table; for definitions and formulas, relative frequency and frequency distributions; and to get a mean from it, the mean of a frequency distribution table. To turn grouped counts into a chart, see histograms in Excel.

Troubleshooting

FREQUENCY returns one more row than I have bins

This is by design. Microsoft documents that the returned array has one more element than bins_array; the extra element is the count of values above the highest interval. If you meant to include every value, make the top bin at least as large as your maximum, and the extra cell will be 0.

Only one number appears instead of a column of counts

Microsoft documents two entry modes. In Microsoft 365, enter the formula in the top-left cell of the output range and it fills the range. In other versions, select the whole output range first and enter it as a legacy array formula with Ctrl+Shift+Enter.

Blank or text cells are missing from the counts

FREQUENCY ignores blank cells and text. If your total is below n, check for numbers stored as text and convert them. If data_array contains no values at all, FREQUENCY returns an array of zeros.

My R table does not add up to the number of rows

By default table() excludes NA. Use useNA = "ifany" so missing values appear as a row, then compare sum() of the table with length() of the vector.

The PivotTable shows Sum or Count of the wrong thing

Open Value Field Settings and set Summarize Values By to Count. A Sum of a numeric field is the total of the values, not the number of times each occurs.

Free alternatives

If you do not use Excel, these functions produce the same frequency column.

Tool	Function	What it returns	Missing values
R (base)	`table(x)`	Counts of each distinct value as a “table” object	Left out unless `useNA = "ifany"` or `"always"`
Python (pandas)	`Series.value_counts()`	A Series of counts of unique values; `normalize=True` returns relative frequencies instead	Not counted by default (`dropna=True`)
By hand	Tally and add	Counts per value or class interval	Count them as their own line

pandas sorts by frequency by default (sort=True); pass sort=False to keep the order of the data, or sort the index afterwards if you want values in ascending order.

Frequently asked questions

How do you make a frequency table from raw data?

List the distinct values, count how many times each occurs, and write the counts beside the values. Check that the counts sum to the number of observations.

What is the difference between frequency and relative frequency?

Frequency is the number of times a value occurs; relative frequency is that number divided by the total. See relative frequency and frequency distributions.