How to Apply Percentage Formula in Excel for Marksheet (7 Applications)

In this tutorial, we will show you how to apply the percentage formula in excel for marksheet. It’s very easy to create a mark sheet in Microsoft Excel. We will apply multiple formulas to create a simple and dynamic mark sheet. To illustrate the working process of this tutorial we will go over several examples with different types of datasets.

Download Practice Workbook

You can download the practice workbook from here.

7 Applications of Percentage Formula in Excel for Marksheet

There are different ways to apply the percentage formula in excel to create a mark sheet. In this article, we will demonstrate 7 different applications of the percentage formula in excel to create a mark sheet.

1. Fundamental Method of Percentage Formula in Excel for Marksheet

First and foremost, we will apply the basic percentage formula in excel to create a mark sheet. To calculate what percentage of a number is another number we just have to divide one number with another number and then multiply with 100. If we want to determine what percentage of A is B we will use the following formula.

In the following dataset, we have marks of 3 subjects for five students and their total obtained marks. Here, the total mark for 3 subjects is 300. We will calculate what percentage of total marks each student gets.

So, let’s take a look at the steps to calculate the percentage of obtained marks for each student.

STEPS:

• Firstly, select cell G5. Insert the following formula in that cell:

• Now press Enter. This action returns the percentage for the first student.

• Secondly, select cell G5. Move the mouse cursor to the bottom right corner of the selected cell so that it turns into a plus (+) sign like the following image.
• Thirdly, click on the plus(+) sign and drag the Fill Handle down to cell C9 to copy the formula of cell G5 in other cells. We can also double-click on the plus (+) sign to get the same result.

• After that, let go of the mouse click.
• Finally, the above command copies the formula of cell G5 to the other cells. So, we can see the percentage of marks for all students.

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

2. Apply Percentage Formula from Excel Ribbon

Applying the percentage formula from the excel ribbon is another fast and convenient way. To demonstrate this to you we will calculate the percentage of our previous dataset again. But this time we will do it from the excel ribbon. The following image is an overview of our previous dataset.

Let’s see the steps regarding this method.

STEPS:

• First, select cell G5. Insert the following formula in that cell:

• If you notice you will see we have not multiplied the value by 100 here like the previous example.

• Next, press Enter. This returns the value in decimals.

• Then go to the Home tab.
• Select the option Percent Style(%) or we can do the same action by pressing Ctrl + Shift + % after selecting the cell G5.

• So, the above action converts the decimal value into a percentage.

• After that, select cell Drag the Fill Handle tool to cell G9 and release the mouse click.

• Finally, the above command returns all the values in percentage.

Related Content: How to Apply Percentage Formula for Multiple Cells in Excel (5 Methods)

3. Extract Particular Value Using Percentage Formula in Excel

In the previous two examples, we have used the percentage formula to calculate the percentage. Now, in this example, we will use the percentage value to extract amounts and totals.

3.1 Evaluate Individual Marks by Total and Percentage

In the following dataset, we have total marks and percentages gained by all students. From these two values, we will calculate the Obtained Marks for each student.

So, let’s see the steps to do this method.

STEPS:

• In the beginning, select cell E5. Insert the following formula in that cell:

• Now, hit Enter. This action returns the obtained mark for the first student.

• After that, select cell Drag the Fill Handle tool to cell E9 and release the mouse click.

• Finally, The above action return obtained marks for all students in cells (G5:G9).

3.2 Use Individual Marks and Percentage to Evaluate Total Marks

For this method, we have the value of Obtained Marks & Percentage in our following dataset. We will calculate the total marks by using these two values.

Let’s look at the steps to perform this action.

STEPS:

• Firstly, select cell E5. Insert the following formula in that cell:

• Now, press Enter. This action returns the value of the total mark.

• Secondly, select cell Drag the Fill Handle tool to cell E9 and release the mouse click.

• Lastly, the above command returns the value of total marks in cell (E5:E9).

Read More: How to Calculate Total Percentage in Excel (5 Ways)

4. Use Percentage Formula in Excel to Modify Existing Marks by a Percentage

Sometimes we need to modify the values of the mark sheet by a fixed percentage. So, in this section, we will explain how to modify an existing value of a mark sheet by a specific percentage.

4.1 Increase Marks by a Percentage

We have the following dataset, which contains the obtained marks for six students in the subject of English. Here, we will increase the marks of each student by different percentages.

So, let’s see the step to how to perform this method.

STEPS:

• First, select cell E5. Insert the following formula in that cell:

• Now, hit Enter. We get the increased value in cell E5.

• After that, select cell Drag the Fill Handle tool to cell E9 and release the mouse click.

• Lastly, we can see the increased value of marks by corresponding percentages in cells (E5:E9).

4.2 Decrease Marks by a Percentage

If we look at the following dataset this method is just the reverse of our previous method. In this method, we will decrease the value of obtained marks by a specific percentage whereas in the previous method we calculated increased marks.

So, let’s look at the steps to execute this method.

STEPS:

• In the beginning, select cell E5. Insert the following formula in that cell:

• Now, press This action returns the marks in cell E5.

• Next, select cell Drag the Fill Handle tool to cell E9 and free the mouse click.

• Finally, we can see the decreased marks for all students in cell (E5:E9).

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

Similar Readings

• How to Calculate Cumulative Percentage in Excel (6 Easy Methods)
• Find percentage between two numbers in Excel
• How to Calculate Grades with Weighted Percentages in Excel
• Calculate Letter Grades in Excel (6 Simple Ways)
• How to Make a Grade Calculator in Excel (2 Suitable Ways)

5. Modify the Entire Column of a Marksheet by Using Percentage Formula in Excel

In this example, we will modify an entire column by a single percentage. In the following dataset, we have a mark sheet of six students containing their marks in English. So, to clarify this method we will increase and decrease the marks of all students by 10%.

Let’s take a look at the steps regarding this method.

STEPS:

• Firstly, copy the same value of marks in the columns “Increased 10%” and “Decreased 10%”.

• Secondly, increasing 10% means taking multiplies as 2, and Decreasing 10% means taking multiplier as 0.9.
• Thirdly, select cell D11. Click on the Copy from ribbon or you can press Ctrl + C.

• Next, select the cells (D5:D9) and select the Paste Special.

• Now, a new dialogue box will appear. Check the option “Multiply” from that box.
• Then press OK.

• So, the above command increases all the values of cells (D5:D9) by 10%.

• Lastly, take the value of multiplier 9 and follow the same process to modify the value of the cell (E5:E9).

Related Content: How to Calculate Percentage Increase from Zero in Excel (4 Methods)

6. Create Dynamic Marksheet in Excel

In this example, we will create a dynamic mark sheet by using the percentage formula. Using the dynamic mark sheet, we will get grades automatically with the change of percentages. In the following dataset, we will input the values of grades according to their percentages.

Let’s see the steps of how we can create a dynamic mark sheet.

STEPS:

• First, select cell G5 and insert the following formula:

• Now, hit Enter. This action reciprocates the grade value A since the percentage is above 80%.

• Next, select cell Drag the Fill Handle tool to cell G9 and release the mouse click.

• So, we can see the grades for all students in the column “Grade”.

• Lastly, if we change the marks in cell E7 from 144 to 160, the grades will automatically change from B to A.

The conditions that we have used in the formula:

If the percentage is > 80% it will return A, if the percentage is < 80% it will return B, if the percentage is < 60% it will return C, if the percentage is < 40% it will return F.

Read More: How to Calculate Final Grade in Excel (with Easy Steps)

7. Error Handling While Using Percentage Formula in Excel

While applying the percentage formula to create a mark sheet in excel we may face errors sometimes. In this article, we will explain how we can handle these errors by using the IFERROR function. The IFERROR function returns another value if the formula returns an error value. In the following dataset, we face an error in the last row because cell value D9 is missing.

So, let’s see the steps of how we can handle errors in the percentage formula.

STEPS:

• Firstly, select cell Insert the following formula in that cell:

• Now, press Enter. This returns the percentage value for the marks of the first student.

• Next, select cell Drag the Fill Handle tool to cell E9 and free the mouse click.

• Finally, we can see the value 0 in place of the “#DIV/0” Here the IFERROR formula returns 0 since the original formula returns an error.

Related Content: Excel Formula to Add Percentage Markup [with Calculator]

Conclusion

At last, this post will show you how to apply the percentage formula in excel for marksheet. Download the practice workbook that comes with this article to test your skills to the test. If you have any questions, please leave a comment in the box below. We’ll do our best to respond as soon as possible. Keep an eye out for more intriguing Microsoft Excel solutions in the future.

Related Articles

• How to Calculate Average Percentage of Marks in Excel (Top 4 Methods)
• Calculate Excel percentage difference between two numbers (using formula)
• How do you Calculate Percentage Increase or Decrease in Excel
• Calculate Year over Year Percentage Change in Excel (3 Easy Techniques)
• How to Calculate Subject Wise Pass or Fail with Formula in Excel
• Make Automatic Marksheet in Excel (with Easy Steps)