How to Create Frequency Relative Frequency on Excel Using a Pivot Table

1. Gather all of your data and compile it into a list on Microsoft Excel. Select and highlight the entire list of data points. Right click it and choose the option 'Sort from smallest to largest.' Now that the data points are in numerical order, the task of creating a frequency distribution table will be much easier.
2. Determine the interval size and the number of classes that will be used for your distribution table. For example you may have something like 5 classes of intervals: 1-5, 5-10, 10-15, 15-20, and 20-25. These classes will be used to organize the data points.
3. Start setting up the table on Microsoft Excel. In column A, set up the different classes and label the column as 'Class.' Column B will be for the 'Frequency.' Count up the number of data points that fall in each class interval, and state the frequency in column B. Select the empty cell below the list of frequencies in Column B and use the sum function to add up the values. This result will give you the total number of data points. In column C, set up the 'Relative Frequency.' Relative Frequency is calculated simply by dividing the individual frequencies by the total number of data values. Select an empty cell at the end of the 'Relative Frequency' column and perform the 'sum' function. The sum for the 'Relative Frequency' column should be 1.00.
4. Select the 'Insert' tab on Microsoft Excel, and select the PivotTable button. Use the selection icon and select the entire table. Place a check next to 'New Worksheet,' so that the table appears on a separate sheet. Click 'OK.' A column in the right side of the screen will appear. Place a check next to the following fields that you'd like to add to your report: 'Class,' 'Frequency,' and 'Relative Frequency.'
5. Use the 'Options' and 'Design' tabs to edit and format the PivotTable.