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.

How to Calculate Percentage for Multiple Rows in Excel: 2 Easy Ways

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.

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 the 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.

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

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.

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)

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.

Download Practice Workbook

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

Multiple Row Percentage.xlsx

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.

<<Go Back to Calculating Percentages in Excel | How to Calculate in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!