How to Make a Relative Frequency Table in Excel

From the table you can see that there’s six students who got a C. Relative frequency means that you are interested in finding the share of students in the whole class that got a given grade.

This is how you proceed:

1.

Set up the table in Excel. In addition to the original frequency table, you’ll need a column for relative frequency as well. Enter the frequency in the frequency column.

2.

Find the sum of all students. Use the command:

=SUM(B2:B6)

3.

Now you’re ready to find the relative frequencies of the different categories. Remember that

$$\text{Relative frequency}=\frac{\text{frequency}}{\text{total number of observations}}$$

Therefore, the formula in cell C2 is

=B2/$B$7

Note! Remember that $-signs are used to prevent Excel from shifting the cell references when you copy formulas. The $ denotes an absolute cell reference.

Highlight cell C2 and copy the formula down to C6. The final result should look like this:

With formulas:

4.

(Optional) Notice that the numbers in the relative frequency column are decimal numbers between 0 and 1. If you prefer percentages instead, you can highlight the numbers in the column and choose Percent from the menu in the picture shown below:

Then the table will look like this: