How to Make a Frequency Table for Grouped Data in Excel (Example)

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 place organized data through classes that were collected from a sample.

Elements of a frequency table

All frequency table It contains data on the collected sample, the absolute frequency, the cumulative frequency, the relative frequency or and the cumulative 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 consists of insert all data that are 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.

working laptop

The table for calculations

Just as we can create pivot tables in Excel, it is also possible to create a table for 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

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

The maximum and minimum values

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

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

The range

In order to calculate 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 apply Sturges’ 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. Be sure to round the amplitude value towards an immediate superior.

excel data sheet

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, move the obtained value downwards and thus you will obtain all the remaining lower bounds.

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 class marks.


Similar to the process of adding by categories in Excel is the case of calculation of absolute frequency where you should write + frequency (all sample data; all upper bound cells). 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 the relative frequency, write + the absolute frequency / the total of absolute frequencies. For cumulative relative frequencies, select all cells in the cumulative frequency, press “Copy”, select the cells of the accumulated relative frequencies and press “Paste”.

Source by

Deja un comentario