Frequency table is one of the commonly used methods to summarize both qualitative and quantitative data. It provides frequencies and relative frequencies for data by class. Frequency table usually consists of the following three columns: 1) Class – one of the categories into which data can be classified; 2) Class frequency – the number of observations in the data set falling into a particular class; 3) Relative frequency – the class frequency divided by the total number of observations in the data set, usually expressed as a percentage. You can create an interactive frequency table in Excel with the PivotTable tool where you can interactively change the variable by which the data is summarized.

Note: This tutorial uses Excel 2013. In other Excel versions, there may be some slight differences in the described steps.

Watch the video below to see how to create an interactive frequency table in Excel. If you prefer written instructions, then continue reading.

To create an interactive frequency table in Excel, take the following steps:

1. First, have your data ready in Excel. For the purpose of this tutorial, suppose you have a small employee data set that contains 20 observations (rows) and three variables (columns): department, gender and age.

2. Next, insert a pivot table. To do that:

• Click any cell inside the data set and on the Insert tab, in the Tables group, click PivotTable.

• The Create PivotTable dialog box appears. Notice that Excel automatically selects the data for you. In the Choose where you want the PivotTable report to be placed section, there are two location options. By default, the New Worksheet option is selected, which means that Excel will create a new worksheet and place the pivot table in it. Alternatively, to place the pivot table in the active worksheet, select the Existing Worksheet option, and then in the Location box, enter the cell where you want the pivot table to start.

3. Click OK. Excel inserts an empty pivot table into the active worksheet and the PivotTable Fields pane appears on the right. From now on, when the active cell is inside the pivot table, the PivotTable Fields pane will be visible with all the variables of the data set listed as fields in it.

4. Now drag the ID field to the Values area.

5. By default, if the field placed in the Values area contains numeric data, such as our ID field, then the data is summarized by the Sum function. To change it to the Count function, click on the Sum of ID in the Values area and then click the Value Field Settings option in the menu that appears.

6. In the Value Field Settings dialog box, on the Summarize Values By tab, change the selection to Count and click OK.

7. Add the ID field again to the Values area, click on the Sum of ID and then click the Value Field Settings option.

8. In the Value Field Settings dialog box, on the Summarize Values By tab, select Count and on the Show Values As tab, select % of Grand Total in the drop-down list and click OK.

9. Drag the Department field to the Rows area.

10. Rename the column headers of the pivot table to Class, Class Frequency and Relative Frequency.

The interactive frequency table in Excel is ready. It summarizes the data by the variable “Department”. The first column shows the different classes of the variable. The second and third columns show the frequency and relative frequency, respectively for each class. Looking at the frequency table, you can see that for this data set, Production is the most represented department with 12 or 60% of employees working in this department. The other departments are represented almost equally in the data set.

11. To interactively change the variable by which the data is summarized, simply change the field in the Rows area in the PivotTable Fields pane. To do that, uncheck the field you want to remove and drag the field you want to add to the Rows area. For example, uncheck the Department field and drag the Gender field to the Rows area. The frequency table now summarizes the data by the variable “Gender”. It shows that out of 20 employees in the data set, 11 are female representing 55% and 9 are male representing 45%.

Both “Department” and “Gender” are qualitative variables. Now, what if we want to summarize the data by a quantitative variable? The answer is that if a quantitative variable takes only a few values, then the frequency table will be created the same way as for a qualitative variable. However, if a quantitative variable has a large number of values, such as “Age” in our example, then it is not practical or useful to list them all and grouping the values into class intervals is necessary. Grouping provides a more compact summary of data and makes it easier to see patterns in it. To see how the frequency table for the variable “Age” looks like without grouping, uncheck the Gender field and drag the Age field to the Rows area.

The resulted table lists the frequency for each age value, and therefore is not very useful. We don’t want to look at each age separately because this does not give us any valuable information. Instead, we want to group the ages into ranges of 10 years so that we can see any patterns that exist. To group the ages:

• Click any cell inside the Class column, right click and in the menu that appears, click on Group.

• This opens the Grouping dialog box. Here, enter 1 for Starting at, 100 for Ending at and 10 for By. Click OK.

The result is a grouped frequency table as shown below. It summarizes the data by the variable “Age”. You now know that 7 employees in the data set are between 21 to 30 years old, 8 employees are between 31 to 40 years old and 5 employees are between 41 to 50 years old.

So, as you see now, an interactive frequency table in Excel created with the PivotTable tool is an effective way to summarize data so that we can better understand the data set without having to look at every observation.

What other ways of creating a frequency table in Excel do you use? Write in the comment section below.