How Do You Make a Frequency Table in Excel?

Finding the Number of Observations

1.

Enter the grades of the students in the class. This is done in the gray area of the spreadsheet (see picture below).

2.

Make the “skeleton” for the frequency table of the grades in the class. Your spreadsheet should now look like this:

3.

Write the formula to find the number of students with the grade "A" in cell D7. To highlight a reference cell range in a formula Excel, write:

<the coordinates of the top left cell>

:

<the coordinates of the bottom right cell>

You’ll want to refer to this cell range to find the frequency of each of the grades.

To copy the formula without causing the cell range to shift you need to write a $-sign before each letter and number coordinate. These are called absolute cell references. If you don’t add $ before each cell’s letter and number coordinate, Excel will consider your references to those cells relative, meaning it will shift the references if you copy the formula to a new place.

The formula is then

=COUNTIF($B$1:$H$3;C7)

With this formula, Excel will count the number of A’s in the chosen area.

4.

Then you highlight cell D7 and pull the little square down to the row with the grade "A". Finally you sum the frequencies and find the number of students in the class. The final frequency table will look like this:

Here are the formulas for each cell: