How to Calculate Average Percentage of Marks in Excel (Top 4 Methods)

Get FREE Advanced Excel Exercises with Solutions!

You will find that knowing how to calculate the average percentage of marks in Excel is really handy. You may use this Excel tool to handle your report card recaps easily if you’re a teacher. The procedure for calculating the average percentage of marks in Excel is much the same as for manually calculating it. The issue is that manually calculating it will take a long time. As a result, some people who need to compute the average percentage of marks now turn to Microsoft Excel for assistance.

Here we took an Excel sheet that represents the final exam report of the students against 3 different subjects where each subject carries 100 marks. To represent the information I’ve taken 4 columns these are Name, Physics, Chemistry, and Mathematics respectively.

how to calculate average percentage of marks in excel


Download Practice Book


What is Average?

Average is a mathematical operation. Suppose, we have a set of numbers. If we divide the sum of the numbers by how many numbers we have, then we will get the average of those numbers. Here the average of 5,6,3,2,9 is 5.

To express a basic Average Formula, we can write,

Average = (Sum of numbers of a set) / (Total numbers in that set)

What is Percentage?

The percentage is a ratio expressed as a fraction of 100. For example, we have a total of 21 balls in a basket and 7 are red among them. So, the percentage of red balls is 30%.


Top 4 Methods to Calculate Average Percentage of Marks in Excel

In this article, we are going to discuss the 4 easiest and most different methods to calculate the average percentage of marks in an Excel Sheet.


1. Calculate Average Percentage of Marks in Excel Using Basic Method

At the very beginning, let’s discuss the basic method to calculate the average percentage of marks in an Excel Sheet. To show examples, we have brought a simple dataset of six students and their respective scores in three tests.

  • Now select cell F5

how to calculate average percentage of marks in excel

  • Then simply put the basic formula to divide the summation of cells C5, D5 and E5 by 300 to get the average percentage of marks of Cris in fraction form in this Excel.

how to calculate average percentage of marks in excel

  • Putting all the values correctly in the relative cell reference, now click on the ENTER button and get the average percentage of marks of Cris in fraction.

how to calculate average percentage of marks in excel

  • After that, use Fill Handle to AutoFill the formula for the rest of the cells to get the average percentage of marks for remaining students in fraction form too.

  • Finally, after selecting all the cells from F5 to F10, use the following keyboard shortcut.
CTRL + SHIFT + %

Immediately after using that keyboard shortcut, you will be able to calculate all the average marks in a perfect percentage form in your Excel sheet.

Read More: How to Calculate Average Percentage Increase in Excel


2. Calculate Average Percentage of Marks in Excel Using SUM Function

You can easily calculate the average percentage of marks in an Excel Sheet by using the SUM function. To show examples, we have brought a simple dataset of six students and their respective scores in three tests.

  • Now select cell F5

  • Now put the SUM formula in cell F5, divide it 300 and click ENTER.
=SUM(C5:E5)/300
Formula Explanation

Here, C5:E5 is the range where all three marks of Cris are stored, and the average will be executed on this by dividing it by 300.

  • Now click on the ENTER button and get the average percentage of marks of Cris in fraction.

  • After that, use Fill Handle to AutoFill the formula for the rest of the cells to get the average percentage of marks for remaining students in fraction form too.

how to calculate average percentage of marks in excel

  • Open the Home tab >> go to General >> select Percentage

how to calculate average percentage of marks in excel

Immediately after clicking on the Percentage option, you will be able to calculate all the average marks in a perfect percentage form in your Excel sheet.

Read More: How to Calculate Average Percentage Increase for Marks in Excel Formula


Similar Readings


3. Calculate Average Percentage of Marks in Excel Using AVERAGE Function

The AVERAGE function is categorized under the Statistical functions in Excel. This function returns the average value of a given argument. From the description of the AVERAGE function, you might have understood that the basic use of this function is to find the average of several numbers in an Excel Sheet.

To show examples, we have brought a simple dataset of six students and their respective scores in three tests. For this, you need to insert an extra column of Average before the Average Percentage column.

  • Here, we have to select cell F5.

how to calculate average percentage of marks in excel

  • Then simply type the formula given below:
=AVERAGE(C5:E5)
Formula Explanation

Here, C5:E5 is the range where all three marks of Cris are stored, and the average will be executed on this.

how to calculate average percentage of marks in excel

  • Now click on the ENTER button and get the average percentage of marks of Cris in fraction.

how to calculate average percentage of marks in excel

  • After that, use Fill Handle to AutoFill the formula for the rest of the cells to get the average percentage of marks for remaining students in fraction form too.
  • Next, select cell G5 and simply type the formula given below:
=F5/100
Formula Explanation

Here, F5/100 will show the average percentage of marks in fraction form.

how to calculate average percentage of marks in excel

  • Now click on the ENTER button and get the average percentage of marks of Cris in fraction.

  • After that, use Fill Handle to AutoFill the formula for the rest of the cells to get the average percentage of marks for remaining students in fraction form too.

how to calculate average percentage of marks in excel

  • Open the Home tab >> go to General >> select Percentage

how to calculate average percentage of marks in excel

Immediately after clicking on the Percentage option, you will be able to calculate all the average marks in a perfect percentage form in your Excel sheet.

how to calculate average percentage of marks in excel

Read More: How to Calculate Average Percentage Change in Excel (3 Simple Ways)


4. Calculate Average Percentage of Marks in Excel Using Ribbon

Using AutoSum feature from Ribbon is another convenient method to calculate the average percentage of marks in Excel. For this, you need to insert an extra column of Total before the Average Percentage column.

  • Now, select a cell from our dataset Here, we have selected cell F5.

how to calculate average percentage of marks in excel

Next, you need to follow the following steps.

  • Open the Home tab >> go to Formulas >> select AutoSum

how to calculate average percentage of marks in excel

  • Then you can see that cells C5, D5 and E5 are automatically taken to calculate the total marks of Cris in those three subjects.how to calculate average percentage of marks in excel
  • Now clicking on the ENTER button, you can see that the total marks of Cris in those subjects appear there.

  • Then, use Fill Handle to AutoFill the formula for the rest of the cells to get the total marks for the remaining students in fraction form too.
  • Next, select cell G5 and simply type the formula given below:
=F5/100
Formula Explanation

Here, F5/100 will show the average percentage of marks in fraction form.

how to calculate average percentage of marks in excel

  • Now click on the ENTER button and get the average percentage of marks of Cris in fraction.

  • After that, use Fill Handle to AutoFill the formula for the rest of the cells to get the average percentage of marks for remaining students in fraction form too.

Next, you need to select all the cells from G5 to G10 and follow the following steps:

  • Open the Home tab >> select % (Percentage Icon)

how to calculate average percentage of marks in excel

  • Immediately after clicking on the % (Percentage Icon), we can see that the average percentage of marks for all the students appears in this Excel

Read More: How to Calculate Grade Percentage in Excel (3 Easy Ways)


Things to Remember

To calculate the average percentage of marks in Excel, you need to calculate the total marks or the average marks first. By then, you can easily calculate the average percentage o marks in Excel.


Practice Yourself

I’ve given a practice sheet in the workbook to practice these explained ways to calculate the average percentage of marks in Excel. You can download it from the link given above.

how to calculate average percentage of marks in excel


Conclusion

In this article, I have tried to explain 4 different methods to calculate the average percentage of marks in Excel. You can also hide multiple columns using the same methods. Last but not least, I will be extremely grateful if you comment down below any of your suggestions, ideas, or feedback.


Related Articles

Foyez Alam

Foyez Alam

Hello everyone, This is Foyez Alam. I have recently completed my MBA from Institute of Business Administration (IBA), University of Dhaka. Earlier I have completed my B.Sc in Leather Engineering from University of Dhaka. Now I am working as an Excel and VBA Content Developer in SOFTEKO. Through patience and hard work, I have a passion for learning new things and applying my knowledge to new situations. That's why I am here. Besides this, I love to interact with different people and I love to spend my spare time reading, watching movies, dancing, etc.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo