How Do You Make a Frequency Table in Excel?
Excel Instruction
Making a Frequency Table
- 1.
- Open a
New spreadsheetinExceland enter all the observations in a list. - 2.
- Make a table of the different possible observations.
- 3.
- Enter the formula to find the frequency of the first observation category in your table.
- 4.
- Copy the formula for the rest of your observations and sum all the frequencies.
In the example below you’ll see how to make a frequency table of grades in a class.
Example 1
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 formulaExcel, 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,
Excelwill count the number of A’s in the chosen area. - 4.
- Then you highlight cell
D7and 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:
