Create a Frequency Table in Excel

Your goal

You have a column of categorical data, and you want to create a frequency table showing the counts per category.

Step-by-step tutorial

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.

Crime in San Francisco, 2016
Crime in San Francisco, 2016

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:

Dialog box for selecting your data
Dialog box for selecting your data

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:

Select your pivot table fields
Select your pivot table fields

This will fill the pivot table with the field's values, but there won't be any counts yet:

Pivot table with the field's values
Pivot table with the field's values

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:

Select the pivot table values
Select the pivot table values

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:

The frequency table
The frequency table