Percent Frequency Distribution – Excel & Google Sheets
Written by
Reviewed by
This tutorial demonstrates how to calculate a percent frequency distribution in Excel and Google Sheets.
What is Percent Frequency Distribution?
The Percent Frequency Distribution displays the frequency (%) of each value in a data set.
Calculate Percent Frequency Distribution in Excel
We will calculate the percent frequency distribution of this dataset:
The methods used will be different depending on which version of Excel you have.
Excel 365 & Other Newer Versions of Excel
List Unique Values
First, use the UNIQUE Function to display all the unique Grades from the dataset.
=UNIQUE(C2:C13)
Sort Values
Notice how the Grades are out of order? Next, add the SORT Function to sort the grades. Insert one helper column for this.
=SORT(E2:E6)
Now you can hide column E, as you don’t need it anymore. If you want to delete it, be aware to copy and paste as values grades in column F.
Note: These are Array Formulas, which means that you can enter the formula once and the values will “spill”. No need to copy + paste the formulas down the column!
Count Unique Values
Next, we will count the unique values. We can create an array formula with the FREQUENCY Function:
=FREQUENCY(C2:C13,F2:F6)
Calculate Percent Frequency Distribution
Next, we will calculate the percent frequency distribution.
=F2/SUM($G$2:$G$6)
Note: Be sure to lock cell references with $ so that you can copy + paste the formula down the column.
Last, format the data as percentages. (1) Select a range with distribution (H2:H6), and in the Ribbon, (2) go to the Home tab, and (3) click on the percentage symbol in the Number group.
As a result, you have the percent frequency distribution for grades in column H.
Older Versions of Excel
If you are using older versions of Excel, you won’t have access to the UNIQUE, SORT, or FREQUENCY Functions. Instead, you can do the following steps.
List Unique Values
You can use the advanced filter to extract unique values. To do this, follow the next steps.
1. (1) Select a range with grades in the dataset (C2:C13), and in the Ribbon, (2) go to Data > Advanced.
2. In the Advanced Filter, (1) check Copy to another location, (2) enter $C$2:$C$13 for List range, (3) enter $E$2 in the Copy to box, (4) check Unique records only, and (5) click OK.
As a result, unique values from range C2:C13 are extracted in column E.
Sort Values
Notice how the Grades are out of order? Next, we will sort the data.
To sort, (1) click anywhere in column E, and in the Ribbon, (2) go to Home > Sort & Filter > Sort Smallest to Largest.
Count Unique Values
Next, we will count the unique values. We can achieve it with the COUNTIF Function:
=COUNTIF($C$2:$C$13,E2)
And double click or drag the fill handle to expand the formula to row 7.
Note: Be sure to lock cell references for the range with $ so that formula can work properly.
Calculate Percent Frequency Distribution
Next, we will calculate the percent frequency distribution.
=F2/SUM($F$2:$F$6)
Note: Be sure to lock cell references with $ so that you can copy + paste the formula down the column.
Last, format the data as percentages. (1) Select a range with distribution (G2:G6), and in the Ribbon, (2) go to the Home tab, and (3) click on the percentage symbol in the Number group.
As a result, you have the percent frequency distribution for grades in column G.
Calculate Percent Frequency Distribution in Google Sheets
Similar to Excel, you can also get percent frequency distribution in Google Sheets. To do this, follow the next steps.
1. In Google Sheets, you can immediately extract sorted unique values. In cell E2, enter the formula:
=SORT(UNIQUE(C2:C13))
2. In cell F2, enter the formula:
=FREQUENCY(C2:C13,E2:E6)
3. In cell G2, enter the formula:
=F2/SUM($F$2:$F$6)
4. Now, format the values in column G as percentages. (1) Select values in column G (G2:G6), and in the menu, (2) click on the Percentage icon.
As a result, you now have the percent frequency distribution in column G.
