How to Calculate Percent Frequency Distribution in Excel (2 Methods)

Get FREE Advanced Excel Exercises with Solutions!

A percent frequency distribution can help you figure out what proportion of distribution is made up of specific values. By grouping values together, a frequency distribution summarizes how often each value occurs. Today, In this article, we’ll get to learn how we can calculate percent frequency distribution Excel effectively with appropriate illustrations.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


2 Suitable Ways to Calculate Percent Frequency Distribution in Excel

Let’s say, we have a dataset that contains information about 10 different cricket players. From our dataset, some Cricketer Names and their Score are given in column B and column C respectively. Here’s an overview of the dataset for our today’s task.

percent frequency distribution excel


1. Apply the UNIQUE and COUNTIF Functions to Calculate Percent Frequency Distribution in Excel

In this method, from our dataset, we want to calculate the percent frequency distribution in Excel by using the UNIQUE and COUNTIF Functions. To calculate the percent frequency distribution by using the UNIQUE and COUNTIF Functions, follow the steps below.

Step 1:

  • First, we calculate the number of unique cricketer names by applying the UNIQUE Function to calculate the percent frequency distribution. For that, select cell E5.

Apply the UNIQUE and COUNTIF Functions to Calculate Percent Frequency Distribution in Excel

  • Now, write down the UNIQUE Function in that cell. The UNIQUE function is,
=UNIQUE(B5:B14)

Apply the UNIQUE and COUNTIF Functions to Calculate Percent Frequency Distribution in Excel

  • After typing the UNIQUE Function in the Formula Bar, Press Enter on your keyboard and you will get the name of the unique Cricketer in column E.

Step 2:

  • Now, we’ll apply the COUNTIF function to find out the total number of appearances of each name. To apply the COUNTIF function select cell F5.

Apply the UNIQUE and COUNTIF Functions to Calculate Percent Frequency Distribution in Excel

  • In cell F5, type the COUNTIF function. The COUNTIF function is,
=COUNTIF(B5:B14,E5)

  • After typing the function, simply press Enter on your keyboard and you will get 4 as the return of the COUNTIF function.

Apply the UNIQUE and COUNTIF Functions to Calculate Percent Frequency Distribution in Excel

  • After that, place your cursor on the Bottom-Right side of cell F5 and an autoFill sign pops us. Now, drag the autoFill sign downward.

Apply the UNIQUE and COUNTIF Functions to Calculate Percent Frequency Distribution in Excel

  • While completing the above process, you will be able to get the output of the COUNTIF function.

Apply the UNIQUE and COUNTIF Functions to Calculate Percent Frequency Distribution in Excel

Step 3:

  • Again, select a new cell G5 to calculate the percent frequency distribution.

Apply the UNIQUE and COUNTIF Functions to Calculate Percent Frequency Distribution in Excel

  • After selecting cell G5, write down the formula that has been given below.
=F5/SUM($F$5:$F$7)

Apply the UNIQUE and COUNTIF Functions to Calculate Percent Frequency Distribution in Excel

  • Again, press Enter on your keyboard and you will get4 as the output of the function.

  • After that, place your cursor on the Bottom-Right side of cell F5 and an autoFill sign pops us. Now, drag the autoFill sign downward to get your desired output.

Apply the UNIQUE and COUNTIF Functions to Calculate Percent Frequency Distribution in Excel

Step 4:

  • Look at column G, you will be able to see the fraction value. Now, we’ll convert these fractions into percentages. To do that, from Home Tab, go to,

Home → Number → Percentage

  • Finally, by clicking on the Percentage option, you will be able to convert fractions into percentages which have been given in the screenshot.

Apply the UNIQUE and COUNTIF Functions to Calculate Percent Frequency Distribution in Excel


Similar Readings


2. Insert the FREQUENCY Function to Calculate Percent Frequency Distribution in Excel

Here, we’ll learn how to calculate the percent frequency distribution by using the FREQUENCY function. Please follow the instructions below to learn!

Step 1:

  • First of all, select cell G5.

Insert the FREQUENCY Function to Calculate Percent Frequency Distribution in Excel

  • After selecting cell G5, type the FREQUENCY Function in the Formula Bar. The FREQUENCY Function in the Formula Bar is,
=FREQUENCY(C5:C14, F5:F7)

Insert the FREQUENCY Function to Calculate Percent Frequency Distribution in Excel

  • Now, simply press Enter on your keyboard and you will be able to get the output of the FREQUENCY function in column G.

Step 2:

  • After completing the above process, select a new cell to calculate the percent frequency distribution. From our dataset, we’ll select cell H5 and write down the below formula.
=G5/SUM($G$5:$G$8)

  • Again, press Enter on your keyboard and you will be able to get 3 as the output of the formula in cell H5.

Insert the FREQUENCY Function to Calculate Percent Frequency Distribution in Excel

  • After that, place your cursor on the Bottom-Right side of cell F5 and an autoFill sign pops us. Now, drag the autoFill sign downward to get the return of the formula that has been given in column H.

Insert the FREQUENCY Function to Calculate Percent Frequency Distribution in Excel

  • Look at column H, you will be able to see the fraction value. Now, we’ll convert these fractions into percentages. To do that, from Home Tab, go to,

Home → Number → Percentage

  • Finally, by clicking on the Percentage option, you will be able to convert fractions into percentages which have been given in the screenshot.

Insert the FREQUENCY Function to Calculate Percent Frequency Distribution in Excel


Things to Remember

👉 Frequency distributions can display the number of observations that fall into each range or the percentage of data that fall into each range.

👉 While calculating the percentage of the fractions, from your Home Tab, go to,

Home → Number → Percentage


Conclusion

I hope all of the suitable methods mentioned above to calculate percent frequency distribution will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.


Further Readings

Md. Abdur Rahim Rasel
Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo