In this Excel tutorial, you will learn how to calculate the percentage of total in Excel.
Calculating the percentage of total in Excel can be found by getting the Total of some values at first and then dividing the Individual value by the Total value and then formatting the result with Percent Style (%).
Note: We have used the Microsoft 365 version in this article. But all of these methods shown here are applicable to any Excel version.
You need to calculate the percentage of total in Excel in different scenarios. Suppose, you have a list of your daily or monthly transactions. You want to know in which transaction category you are spending the most or vice versa. You can get this quickly by calculating the percentage of total of every transaction category in Excel. You may have daily or monthly sales data for some products of a store. You want to know which product’s sales are high or low. You’ll also get this from the percentage of total.
Overview of Calculating Percentage of Total in Excel using the SUM function and Excel formula
How to Calculate Percentage in Excel?
You can calculate the percentage of a Part by dividing it by the Total value and then multiplying the result by 100. We denote the percentage value with the % symbol. The general formula to calculate percentage is:
In MS Excel, you can calculate percentage very easily. For example, I have a dataset like the following image. We shall calculate the Obtained Mark in % of the Total Mark.
Follow these steps:
Step 1:
In cell C6, I want to calculate the Obtained Mark in % of the Total Mark. So, I input this formula in the cell C6:
=C4/C5
Step 2: Click on the Enter button on your keyboard and you will find the following output.
Step 3: Formatting the cell to show in Percentage
Excel automatically shows the results in decimal values. This is why cell C6 is showing value in decimal. Let’s convert the cell value of C6 to percentage.
To do this select the cell C6 => Home tab => Go to Number group of commands => You will get the Percent Style command.
Step 4: Formatting the cell to show in Percentage
Click on the Percent Style command and you will get the following result.
You can format a cell from decimal values to Percentage in these two ways:
First way:
Select cell C6 => Home tab => Go to Number group of commands => Click on the Number Format drop-down => You will get the Percentage command.
Second way: Keyboard Shortcut to change the Format of a cell from Decimal to Percentage:
Select cell C6 => Press the keyboard shortcut Ctrl + Shift + 5/% and you will get the following result.
1. Using SUM Function and Excel Formula to Calculate Percentage of Total
We shall use the SUM function to get the Total value first and then we shall divide the Individual value by the Total to get the percentage of total.
Example 1: Find Individual Category Expenses in % of Total Expenses
I have a dataset with individual Expense Category and Amount. I want to find the Individual Category Expenses in % of the Total Expenses.
Follow these steps:
Step 1: Find the Total Expenses
In cell C16, I want to calculate the total expenses using the SUM function. So, I input this formula in cell C16:
=SUM(C5:C14)
Step 2: Find the Total Expenses
Click on the Enter button on your keyboard and you will find the following output.
Step 3: In the cell D5, I want to calculate the individual expenses in % of total expenses. So, I input this formula in the cell D5:
=C5/$C$16
Step 4: Click on the Enter button on your keyboard and you will find the following output. Hover your mouse over the bottom right corner of cell D5, you will find the Fill Handle icon (Green Plus).
Step 5: Double-click on the Fill Handle icon to obtain all the individual expenses in % of total expenses across the cells D5:D14.
Step 6: Formatting the cell to show in Percentage
Excel automatically shows the results in decimal values. This is why cells D5:D14 are showing values in decimal. Let’s convert the cell’s values to percentages.
To do this select the cells D5:D14 => Home tab => Go to Number group of commands => You will get the Percent Style command.
Step 7: Formatting the cell to show in Percentage
Click on the Percent Style command and you will get the following result.
Example 2: Find Individual Student’s Obtained Mark in % Out of Total Mark
I have a dataset with the student’s marksheet. It has the student’s marks in 3 subjects and the Total Mark of the 3 subjects. I want to find the Individual Student’s Mark in % of the Total Mark.
Follow these steps:
Step 1: In cell F5, I want to calculate the obtained mark using the SUM function. So, I input this formula in the cell F5:
=SUM(C5:E5)
Step 2: Click on the Enter button on your keyboard and you will find the following output. Hover your mouse over the bottom right corner of cell F5, you will find the Fill Handle icon (Green Plus).
Step 3: Double-click on the Fill Handle icon to get the obtained marks of all students across cells F5:F14.
Step 4: In cell H5, I want to calculate the Individual Student’s Mark in % of the Total Mark. So, I input this formula in the cell H5:
=F5/G5
Step 5: Click on the Enter button on your keyboard and you will find the following output. Hover your mouse over the bottom right corner of cell H5, you will find the Fill Handle icon (Green Plus).
Step 6: Double-click on the Fill Handle icon to get the Individual Student’s Mark in % of the Total Mark across cells H5:H14.
Step 7: Formatting the cell to show in Percentage
Excel automatically shows the results in decimal values. This is why cells H5:H14 are showing values in decimal. Let’s convert the cell’s values to percentages.
To do this select the cells H5:H14 => Home tab => Go to Number group of commands => You will get the Percent Style command.
Step 8: Formatting the cell to show in Percentage
Click on the Percent Style command and you will get the following result.
2. Using SUMIF Function and Excel Formula to Calculate Percentage of Total Quantity of an Item Based on Drop-Down List
I have a dataset with Item and Quantity. I want to put a drop-down list in cell C15 and calculate the % of the Total Quantity of an Item in cell C16. When I select an Item from the list, cell C16 will show the % of the Total Quantity based on that Item.
Follow these steps:
Step 1: In cell C13, I want to calculate the Total Quantity of the items using the SUM function. So, I input this formula in the cell C13:
=SUM(C5:C11)
Step 2: Click on the Enter button on your keyboard and you will find the following output.
Step 3: Making a drop-down list
I want to make a drop-down list of the items in cell C15.
To do this: Select cell C15 => Data tab => Go to Data Tools group of commands => You will get the Data Validation command.
Step 4: Making a drop-down list
Click on the Data Validation command
=> You will get the Data Validation dialog box
=> In the Settings tab, Choose List from Allow: drop-down
=> Put this formula =$B$5:$B$11 in the Source box.
Step 5: Making a drop-down list
Click OK and you’ll get the following output.
Step 6: Making a drop-down list
Click the drop-down icon to see the list.
Step 7: I select Apple from the list.
Step 8: In cell C16, I want to calculate the % of the Total Quantity of the item from the list. For this, I’ll combine the SUMIF function and an Excel formula. So, I input this formula in the cell C16:
=SUMIF(B5:B11,C15,C5:C11)/C13
Step 9: Click on the Enter button on your keyboard and you will find the following output.
Step 10: Formatting the cell to show in Percentage
Excel automatically shows the results in decimal values. This is why cell C16 are showing value in decimal. Let’s convert the cell’s values to percentage.
To do this select the cells C16 => Home tab => Go to Number group of commands => You will get the Percent Style command.
Step 11: Formatting the cell to show in Percentage
Click on the Percent Style command and you will get the following result.
Step 12: Click on the drop-down icon and locate Orange from the drop-down list.
Step 13: Select Orange from the drop-down list to get the % of the total quantity of Orange automatically.
How to Get Total from Percentage in Excel?
You can get the total from the percentage in Excel by dividing Part with the Percentage value.
I have a dataset with some Products, their Prices after Discount, and % of Discount. We want to calculate the original Product Price from % of the Discount.
Follow these steps:
Step 1: In cell E5, I want to calculate the Product Price from % of Discount. So, I input this formula in the cell E5:
=C5/D5
Step 2: Click on the Enter button on your keyboard and you will find the following output. Hover your mouse over the bottom right corner of cell E5, you will find the Fill Handle icon (Green Plus).
Step 3: Double-click on the Fill Handle icon to get the Product Price of all Products across cells E5:E10.
Download Workbook
Conclusion
I have shown you several methods and examples to calculate the percentage of total in Excel. We have used the SUM function to calculate the Total value, and then we have divided the Individual value by the Total and formatted the result to get the value in Percentage Style. We have also shown a case, where you can select a value from a drop-down list and can get the percentage of that value of the Total. If you know any other methods or you face any problems related to calculating the percentage of total, let us know in the comment box.
Related Articles
- How to Convert Number to Percentage in Excel
- How to Divide a Value to Get a Percentage in Excel
- How to Calculate Reverse Percentage in Excel
- How to Convert Percentage to Number in Excel
- How to Convert Percentage to Whole Number in Excel
- How to Show One Number as a Percentage of Another in Excel
- How to Add a Percentage to a Number in Excel
- Make an Excel Spreadsheet Automatically Calculate Percentage
- Convert Number to Percentage Without Multiplying by 100 in Excel
I’m hoping you can help with an issue that I am having. I have Column D with a list of totals.The last cell in column D is D13 with the sum(D3:D12). Now I have column E for percent of total volume, this formula =D3/$D$13, =D4/$D$13,=D5/$D$13 and so on (D13 is where I have the Total for column D). To double check that my percent totals add up to 100, the last cell in E =sum(E3:E12) – this is the range that contains the percentages. It does add up to 100% but if I delete a value in column D, the total percent remains at 100% even though the percent for the individual value is now 0% (because I deleted the total for that item). Why isn’t my sum of percent cell updating? 🙁 I can’t figure this out.
Greetings Debbie,
First of all,we really appreciate for your question. Now if I am not afraid, you asked about why your total percentage value not changed despite deleting one sample value. If this is the case, the reason is pretty simple. as you delete one value, your total value also change. Which in turns also change the percentage value of all the other values. and this change will happen in a way that the total percentage values are always sums up to 100, following the basic arithmatic rule.
If this is not the case,please provide your Excel worksheet containing your data and we will have a look.
Thanks and Regards
Rubayed Razib