Eshrak Kader
While constructing a mark sheet in Excel, you may need to calculate the percentage of the marks. In this article, you’ll learn how to calculate the percentage of marks in Excel with appropriate illustrations.
Download Practice Workbook
You can download the practice workbook from the link below.
5 Ways to Calculate Percentage of Marks in Excel
As with Excel, there are multiple ways of computing percentages with functions and Excel tools. So, without further delay, let’s dive in!
Now, consider the Marks Distribution dataset shown in the B4:F11 cells. Here, we have the Names of the students and their scores in Physics, Chemistry, Biology, and Math out of a Total Mark of 100 in each subject.
Here, we have used Microsoft Excel 365 version, you may use any other version according to your convenience.
Method-1: Using Arithmetic Formula to Calculate Percentage of Marks
Let’s start with the simplest and most obvious way of calculating the percentage of marks in Excel. Simply put, let’s use the Arithmetic Formula.
📌 Steps:
- At the very beginning, go to the G5 cell and insert the expression below.
=C5+D5+E5+F5
Here, the C5, D5, E5, and F5 indicate the marks obtained by Zoe in Physics, Chemistry, Biology, and Math, respectively.
- Next, copy the formula to the cells below using the Fill Handle Tool.
- Then, compute the percentage of marks using the formula given below.
=G5/$D$13
In this formula, the G5 cell refers to the Marks Scored while the D13 cell points to the Total Mark of 400.
📃 Note: Please make sure to use Absolute Cell Reference by pressing the F4 key on your keyboard.
- Finally, press CTRL + SHIFT + % on your keyboard to change the number format to percentage. Thus, your result should look like the image given below.
Read More: How to Apply Percentage Formula in Excel for Marksheet (7 Applications)
Method-2: Calculating Percentage of Marks with SUM Function
Another convenient way to compute percentages involves using the SUM function. So, let’s see it in action.
📌 Steps:
- First of all, go to the G5 cell and insert the expression below.
=SUM(C5:F5)
Here, the C5:F5 range of cells points to the marks obtained by Zoe in the 4 subjects respectively.
- Next, move to the H5 cell and type in the formula below.
=G5/$D$13
Eventually, your output should look like the picture shown below.
Read More: Calculate Grade Using IF function in Excel (with Easy Steps)
Similar Readings
- Excel Formula for Pass or Fail with Color (5 Suitable Examples)
- How to Calculate Letter Grades in Excel (6 Simple Ways)
- Calculate Average Percentage Increase for Marks in Excel Formula
- How to Calculate Average Percentage of Marks in Excel (Top 4 Methods)
Method-3: Utilizing AutoSum Feature to Calculate Percentage of Marks
If using formulas doesn’t suit you, then our next method is for you. Here, we’ll utilize Excel’s AutoSum feature to calculate the summation of the marks. Now, allow me to demonstrate the process in the steps below.
📌 Steps:
- Firstly, select the C5:F5 range of cells >> in the Editing section, and click the AutoSum button.
Eventually, the result should look like the screenshot shown below.
- Secondly, navigate to the H5 cell and insert the expression below.
=G5/$D$13
Subsequently, your output should look like the image shown below.
Read More: How to Make a Grade Calculator in Excel (2 Suitable Ways)
Method-4: Applying the SUBTOTAL Function to Calculate Percentage of Marks
If you’re one of those people who enjoy using Excel formulas, then the following method has you covered. In this method, we’ll apply the SUBTOTAL function to calculate the summation of the marks. It’s simple and easy. Just follow these steps.
📌 Steps:
- First and foremost, go to the G5 cell and type in the expression below.
=SUBTOTAL(109,C5:F5)
In the above formula, 109 (function_num argument) refers to the SUM function, and the C5:F5 (ref1 argument) range of cells indicates the marks scored by Zoe in the 4 subjects.
- Secondly, go to the H5 cell and enter the expression below.
=G5/$D$13
Consequently, your table should appear as in the screenshot given below.
Read More: How to Calculate Grade Percentage in Excel (3 Easy Ways)
Method-5: Employing the SUMIF Function to Calculate Percentage of Marks
Sometimes, you may need to calculate the percentage of marks for a specific student from a given dataset. In such a situation, we’ll utilize the SUMIF function to compute the percentage of marks in Excel. So let’s begin.
Here, let’s consider the Marks Distribution of French dataset in the B4:D14 cells. In this dataset, we have the student Names and their scores in French for two semesters.
📌 Steps:
- Firstly, go to the G5 cell and type in the expression below.
=SUMIF(C5:C14,G4,D5:D14)/$D$16
Here, the C5:C14 and the D5:D14 range of cells refer to the Name and the French columns respectively. In contrast, the G4 and the D16 cells point to the Name Michell and the Total Mark of 200.
📃 Note: Please make sure to use Absolute Cell Reference by pressing the F4 key on your keyboard.
âš¡ Formula Breakdown:
- =SUMIF(C5:C14,G4,D5:D14) → adds the cells specified by a given criteria or condition. Here, C5:C14 is the range argument that refers to the Name column. Then, G4 represents the criteria argument to apply within the given range. Lastly, D5:D14 is the optional sum_range argument which indicates the values to sum within the range.
- Output → 152
- =SUMIF(C5:C14,G4,D5:D14)/$D$16 → becomes
- =152/$D$16 → In this expression, the value 152 is divided by the D16 cell which is the Total Mark of 200.
- 152/200 → 76%
Subsequently, your output should look like the picture shown below.
Read More: How to Make Result Sheet in Excel (with Easy Steps)
Practice Section
We have provided a Practice section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.
Conclusion
I hope this article helps you understand how to calculate the percentage of marks in Excel. If you have any queries, please leave a comment below. Also, if you want to read more articles like this, you can visit our website ExcelDemy.
