Method 1: Using “Countif” in Excel
Note, this is a tedious method and therefore you would want to move on to the next methods that are more efficient. However, using Countif is a straightforward method and therefore useful for discussing the frequency table.
Countif counts the number of cells that meet a certain criterion (e.g., count the cell values are “A” or “1”). That is, it will find the frequency of the value that you are interested in. As this is the core process of creating a frequency table, you just need to repeat this process across all the values of a variable to make a frequency table.
For example, as shown below, let’ say we want to make a frequency table based on the example dataset (Grade dataset) from my earlier posting Frequency Analysis & Frequency Table (6 min.).
As instructed in the posting, the first two steps of creating a frequency table are to create an empty frequency table and fill in the first column with all the possible values, as shown below.
Then, using Excel’s Countif function, you can find the A’s frequency. Specifically, you will do:
=COUNTIF(B2:B13,”A”)
See also the image below (the yellow cell). Make sure to put quotes around the criteria (i.e., “A” instead of A).
Once you hit enter after the command, you will see 2, which is the frequency of the value A.
You can repeat the same process for B, C, and D.
For the last value Drop/F, you need to add the two frequencies of “Drop” and “F”, which you can do by
=COUNTIF(B2:B13,”Drop”)+COUNTIF(B2:B13,”F”)
In case you wonder what the “Count” function does: The Count function is for counting “Number” cells. That is, how many entries (cells) in a given array (range of cells) have numbers. In the above example, the grade column (Column B) has no numbers. Therefore, =count(B1:B13) would give you 0. But, there are 12 numbers in the first column (Column A), therefore, =count(A1:A13) will give you 12.
Method 2: Using the “Histogram” function of “Data Analysis” Toolpak in Excel
During the discussion of Method 1, you probably hoped that Excel can automatically fill in the “Frequency” column next to the Value column. Method 2 does that.
Step 1: First, we need to activate the “Data Analysis ToolPak.“
Step 2: Replace the letters with numbers using the Ctrl+F (after Ctrl+F, you would need to click the “Replace” tab next to the “Find” tab). You can use any numbers but here’s an example.
A → 1,
B → 2,
C → 3,
D → 4,
F → 5 (in this example, I excluded “Drop” as there are no “Drop” in the sample dataset).
Step 3: Then, next to the raw dataset, you need to create a column that shows all the possible values of the dataset. Make sure to use the numbers that replaced the raw values (i.e., instead of the letter grades, list the numbers. Finally, at the top of the number list (that replaced all the possible values), give appropriate labels, such as Grades.
Step 4: Select the Histogram function of “Data Analysis” menu under the Data tab.
Step 5: Select Histogram
Step 6: Click the up-arrow icon next to the “Input Range:” and select the range with your input data (but do not use the raw letter grades – instead, use the numbers that replaced the letter grades). That is, select the cells B2:B13. FYI, Excel will add $ and make the ranges look like ($B$2:$B$13) to “lock” the ranges. See also the image below.
Similarly, define the “Bin Range.” That is, click the up-arrow icon next to the “Bin Range:” and select the range with the numbers that show all the possible values in your data (i.e., 1, 2, 3, 4, 5). Notably, the Bin Range should include “D1” that shows the label of the values (i.e., Grades). Then, make sure to check the “Labels” box below the Bin Range.
Finally, check the “Chart Output” at the bottom of the window and click OK.
Then, you would get the frequency table as shown below:
Method 3: Using Pivot Table Function of Excel (The Best Method)
In step 3 of the second method discussed above (Using the “Histogram” function of “Data Analysis” Toolpak), you needed to create a column that shows all the possible values of the dataset (along with an appropriate label). In case of the grade example, you need to know all the possible grades in the dataset.
That may make you wonder “what if I do not know all the possible values in a given dataset?” or “Can Excel find all the possible values?”, which correspond to the first column of a frequency table. The method described here, using the Pivot Table, will make Excel find all the possible values and then find each value’s frequency automatically.
Note, this method works for both numbers and letters (but the Histogram function in Analysis Toolpak works for numbers only). Therefore, you don’t have to convert the letter grades into numbers as you did in step 2 in Method 2 above.
Step 1: Click “Insert” and Click the “Pivot Table” function.
Step 2: Once a new window shows up as below, click the up-arrow icon of the “Table/Range” and select the rage (in our example’s case: column B) in the raw-data table that shows the students’ grades, including the label (see the selected rage in the image below). Note, you can simply click the heading of the column (again, in our example, column B) to select the whole column with student grades.
For “Choose where you want the PivotTable to be placed,” simply click a cell near the dataset. Note the place that you designate now will be the upper left corner of the pivot table (and the pivot table is the tool with which you can create the frequency table).
If you click OK, then, you would see a pivot table, which is initially empty.
Step 3: To make a frequency table out of this pivot table, you need to first create the value column of the frequency table that shows all the possible values in the dataset. Move the variable (called field) shown at the top of the right-hand panel to the “Rows” areas (lower left box at the bottom) as shown below. Then, the pivot table will show the values column of the frequency table.
Then, move the same variable (called field) shown at the top of the right-hand panel to the “Values” areas (lower right box at the bottom) as shown below. Then, the pivot table will also show the frequency of each value. Then, your frequency table is completed.
Hope this helps.