Create a Frequency Table in Excel
You have a column of categorical data, and you want to create a frequency table showing the counts per category.
For a small dataset you could of course simply count the values, but for anything but a tiny dataset, that won't be a very satisfactory approach. Fortunately, Excel provides a useful feature called pivot tables that make it easy to generate a frequency table.
For this tutorial, we'll use the San Francisco Crime Dataset from Kaggle.
Here's a partial view of the dataset we'll use for this example.
Let's generate a frequency table for crime category. To do this, go to Insert > Pivot Table. Select the data you want to analyze. For example:
Now your pivot table will appear, either on a new worksheet or else the current one, depending on what you chose in the previous step. You will see a widget called PivotTable Fields. Choose the field corresponding to the categorical variable for which you want counts. Here I'll choose Category:
This will fill the pivot table with the field's values, but there won't be any counts yet:
To add the counts, use the PivotTable Fields widget again. Simply drag-and-drop the field's name from the Field Name box into the Values box. (Note: be sure to drag the field's name from the Field Name box and not from the Rows box.) You should see Count of [field name] appear in the Values box:
And if you look over at the pivot table itself, you'll see that it contains the counts for each field value. That's your frequency table: