Creating a frequency table for grouped data can be tedious if done manually. Therefore, we prepare this article with which you will learn How to make a frequency table for grouped data in Excel

What is a frequency table for grouped data?

A frequency table for grouped data is a statistical tool that allows you to place data organized by classes that were collected from a sample.

Elements of a frequency table

Every frequency table contains data on the collected sample, the absolute frequency, the accumulated frequency, the relative frequency or and the accumulated relative frequency.

How to make a frequency table for grouped data in Excel

If you want to make a frequency table for grouped data in Excel you must follow a simple procedure shown below. In addition, you can link the Excel table with Word if you need the resource in that word processor.

The sample data

The first thing is to insert all the data that is part of the sample in an Excel sheet. Said data will be used to obtain the lower and upper limits, the class mark, and the frequencies: absolute, accumulated absolute, relative and accumulated relative.

trabajando portatil

The table for calculations

Just as we can create pivot tables in Excel, it is also possible to create a table to calculate statistical intervals . This table will have six rows and two columns. Write in each cell: Number of data, maximum value, minimum value, Range, number of intervals, amplitude.

The number of data

To get the number of data , place the cursor next to that cell and type = COUNT (the sample data) and press “Enter.”

The maximum and minimum values

To get the maximum value , place the cursor next to the cell where you typed “maximum value” and insert the function = MAX (all sample data). Hit the “Enter” key.

For the minimum value, follow the above process but instead of MAX () you will use the function = MIN (). Remember to press the “Enter” key to run the formula.

The range

To calculate the range , you must place the cursor next to the cell “Range” and write in this cell + the cell where the maximum value is – the cell with the minimum value.

The number of intervals

To calculate the number of intervals you need to apply Sturges’s rule . To do this, type in the corresponding cell: + 1 + 3.322 * log10 (the cell that contains the number of data) and press “Enter.”

Calculate the amplitude

To obtain the amplitude write in the corresponding cell: + select the cell of the range / the cell of the number of intervals. Then press the “Enter” key. Make sure to round the amplitude value to one next higher.

hoja datos excel

The lower and upper limits

Enter the value 0 for the first lower bound and the value you got in the range for the upper bound. Drag that value to all other cells in this column. You will get the second lower limit by writing: + the previous upper limit +1. Then, shift the obtained value down and you will get all the remaining lower limits .

The brand of the class

To get the class mark, you must write in the corresponding cell + (the lower limit + the upper limit) / 2 and press “Enter”. Drag the result across the entire column to get the rest of the class marks .


Similar to the process of adding by categories in Excel is the case of calculating the absolute frequency where you must write + frequency (all the sample data; all the cells of the upper limits). Select the rest of the cells in this column, press “F2” and then “Ctrl + Shift + Enter.”

For the accumulated frequency write the same value of the first absolute frequency and in the second cell write + select the cell of the first accumulated absolute frequency + the first absolute frequency) press “Enter”.

For relative frequency, write + absolute frequency / total absolute frequencies. For the accumulated relative frequencies, select all the cells of the accumulated frequency , press “Copy”, select the cells of the accumulated relative frequencies and press “Paste”.

Helpful?, please rate!
[Total: 0 Average: 0]