How to Calculate Percentage for Multiple Rows in Excel

Sometimes, Excel users need to calculate percentages for various working purposes. They may need to find out efficiency or contribution towards total productivity or sales or showing progress. But, in the case of a large data set, it is tedious and time-consuming to calculate the percentage for every participant one by one. So, in this article, I will show you how to calculate percentage in Excel for Multiple Rows.


Download Practice Workbook

You can download the free Excel workbook here and practice on your own.


2 Easy Ways to Calculate Percentage for Multiple Rows  in Excel

In this article, you will see two easy ways to calculate percentage in Excel for multiple rows. Firstly, I will use the AutoFill feature of Excel to calculate percentage for multiple rows. In the second procedure, I will utilize the Quick Analysis Tool for doing the same.

To illustrate my article further, I will use the following sample data set. Here, I have the marks of four subjects of the final examination of five random people.

2 Easy Ways to Calculate Percentage for Multiple Rows  in Excel


1. Use AutoFill Feature to Calculate Percentage for Multiple Rows

In the first method, I will use the AutoFill feature of Excel to calculate percentage for multiple rows. For that, I will need to find out the total marks of each examinee and then divide it by the total marks of the examination. To learn more about this procedure, follow the following steps.

Steps:

  • First of all, to calculate the total marks of Nathan, use the following SUM function formula in cell G5.
=SUM(C5:F5)

  • Secondly, press Enter to see the result and drag the formula to the lower cells using Fill Handle.

  • Thirdly, to calculate the percentage of marks for Nathan, insert the following formula in cell H5.
=G5/$D$11

  • Fourthly, after pressing Enter you will get the decimal value from the above formula.

  • Fifthly, to convert it into percent, select cell H5 and go to the Home tab of the ribbon.
  • Then, from the Number group, select the Percent Style (%) icon to format the selected cell as a percent.
  • After that, you will find the value of cell H5 in the percent format.

  • Then, bring the cursor of the mouse to the lower right corner of cell H5 and you will see the AutoFill sign.
  • Consequently, drag the sign to the lower cells to apply the same formula and the cell format to those cells.

 Using AutoFill Feature as An Easy Way to Calculate Percentage for Multiple Rows  in Excel

  • Finally, you will get your desired result which will look like the following image.

Showing Final Result for Using AutoFill Feature as An Easy Way to Calculate Percentage for Multiple Rows  in Excel

Read More: Calculate Percentage Using Absolute Cell Reference in Excel (4 Methods)


2. Utilize Quick Analysis Tool to Calculate Percentage for Multiple Rows

In the previous method, I calculated the percentage based on the total marks of the entire exam. But if you want to compare the marks among the examinees without using the total marks, then you can use the Quick Analysis Tool of Excel. By using this method you can also calculate the percentage for the entire column. Go through the following steps for a better understanding.

Steps:

  • First of all, type the following SUM function formula in cell G5 to calculate the total marks of Nathan.
=SUM(C5:F5)

  • Secondly, to see the value press Enter and drag the formula to the lower cells using AutoFill for the same purpose.

  • Thirdly, to compare their marks and see the percentages without using the total marks, select the cell range G5:H5.
  • Then, click on the Quick Analysis Tool that appears below the selected cell range after selecting them.

  • Fourthly, from the analysis tool, click on the Totals tab after the previous step.
  • Then, from all of the options, select % Total.

Utilizing Quick Analysis Tool as An Easy Way to Calculate Percentage for Multiple Rows  in Excel

  • Fifthly, you will find the percentages of the respective person besides their acquired marks after following the previous instruction.

  • Consequently, double-click on cell H5, and you will find the procedure or formula used by Quick Analysis Tool to calculate percentages in this regard.
=SUM(G5)/SUM($G$5:$G$9)

Read More: How to Apply Percentage Formula for Multiple Cells in Excel (5 Methods)


Things to Remember

  • While calculating the percentage in the first method, remember to give absolute cell reference where needed, otherwise, you will not get the desired value.
  • There are two % Total options in the Quick Analysis Tool. Remember to select the second one, which will calculate the percentage for rows.

Conclusion

That’s the end of this article. I hope you find this article helpful. After reading the above description, you will be able to calculate percentage in Excel for multiple rows.. Please share any further queries or recommendations with us in the comments section below.

The ExcelDemy team is always concerned about your preferences. Therefore, after commenting, please give us some moments to solve your issues, and we will reply to your queries with the best possible solutions ever.


Related Articles

Araf

Araf

I am Araf. I have completed my B.Sc in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Currently I am working as an Excel & VBA Content Developer in Softeko. With proper guideline and aid of Softeko I want to be a flexible data analyst. With my acquired knowledge and hard work, I want to contribute to the overall growth of this organization.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo