Are you searching for an article that covers all percentage-related calculations? Then you have come to the right place! Hope this article will be a proper guideline for you to create a formula in Excel to calculate percentage.
Download Practice Workbook
You can download the free Excel template from here and practice on your own.
What is Percentage
A percentage is a ratio that expresses a fraction of 100. To calculate the percent, we divide a partial amount by the whole and multiply the result by 100. So, the percentage represents a part per hundred. The term percent means per 100. The symbol for the percentage is “%”.
General Percentage Formula:
Percentage = (Part / Whole) Ă— 100
Formula to Calculate Percentage of Total in Excel
The dataset represents some students’ obtained marks of an exam of a total of 80 marks. Now let’s find their percentage of total using a formula.
Steps:
- Type the following formula in Cell D5–
=C5/$D$11
- Then press the Enter button and you will get the output as a fractional value.
- To convert it to a percentage format, click as follows: Home > Number > %
- Finally, drag down the Fill Handle icon all the way to copy the formula.
Here’s all the percentage of the total.
Note:
Use Absolute Cell Reference using the F4 functional key for the denominator in the formula as it is a fixed value.
Read More: How to Create a Formula in Excel (5 Ways)
Formula to Find Percentage Increase or Decrease in Excel
Let’s find the percentage increase or decrease of some fruits’ price compared between two months.
Steps:
- In Cell E5, write the following formula-
=(D5-C5)/C5
- Later, hit the Enter button to get the output.
Don’t forget to convert the number format to percentage. Excel produces only fractions by default.
- Finally, drag down the Fill Handle icon to get the other percentages.
Here’s all the output. The positive values indicate an increase and the negative values indicate a decrease.
Read More: How to Create an Excel Formula to Subtract (10 Examples)
Calculate Partial and Total Amount in Excel by Using a Percentage Formula
We can calculate an amount or total based on a percentage with a formula.
1. Calculate Partial Amount
Let’s find an amount of 30% of total sales in the Canadian region using the following dataset. We’ll have to multiply the total by the respective percentages.
Steps:
- Write the following formula in Cell E5–
=C5*D5
- Press the Enter button to finish.
2. Calculate Total Amount
To find a total, we’ll have to divide the partial amount by the respective percentage. Let’s find it using the following dataset.
Steps:
- Type the following formula in Cell E5–
=C5/D5
- Finally, just hit the Enter button to get the output.
Formula to Determine Increase or Decrease of a Number by Percentage in Excel
Now we’ll learn how to calculate the increase or decrease of a number by using percentage. For that, I have used a new dataset that will represent the increase or decrease of house rent.
1. Increase a Number by a Percent
We’ll calculate for a 10% increase in the house rent.
Steps:
- In Cell D5, write the following formula-
=B5*(1+C5)
- Hit the Enter button for the result.
2. Decrease a Number by a Percent
Now we’ll calculate for a 12% decrease in the house rent.
Steps:
- Type the following formula in Cell D5–
=B5*(1-C5)
- Finally, press the Enter button to get the decreased amount.
Find Increase or Decrease of an Entire Column by a Percentage Using Paste Special
Assume that, the fruits’ prices increased by 20% so the price will be increased to 120%. We’ll multiply this percentage with the prices using Paste Special.
Steps:
- Type the following formula in Cell D8–
=$D$5
- Press the Enter button.
- After that, use the Fill Handle tool to copy the formula for the other cells.
- Then copy the cells C8:C12.
- Later, select Cell D8 and right-click your mouse.
- Select Paste Special from the Context menu.
- After the Paste Special dialog box appears, mark All from the Paste section and Multiply from the Operation section.
- Finally, just press OK.
Then you will get all the prices increased by 20%.
To get the decreased price by 20%, use 80% to the above operation.
Read More: How to Create a Formula in Excel without Using a Function (6 Approaches)
Formula to Calculate Profit, Loss, and Discount Using Percentage in Excel
Finding profit, loss, and discount is the basic application of percentage. We’ll learn that in this section.
Note:
To get the results in %, convert the number format to percentage.
1. Calculate Profit Using Percentage
The common formula to find the profit is = (Selling Price – Buying Price) / Buying Price
Steps:
- Place the following formula in Cell E5–
=(D5-C5)/C5
- Then hit the Enter button to get the percentage of profit.
2. Calculate Loss Using Percentage
The common formula to calculate the loss is:
Loss = (Buying Price – Selling Price) / Buying Price
Steps:
- In Cell E5, place the following formula-
=(C5-D5)/C5
- Finally, just press the Enter button.
3. Find Discount Using Percentage
Here, we’ll calculate the discounted amount of some fruits based on the given discount in percentage.
Steps:
- Place the formula in Cell E5–
=C5*D5
- Then press the Enter button.
- Finally, use the Fill Handle tool to copy the formula for the rest of the cells.
Now we have gotten all the discounted prices.
Create Formula to Calculate Percentile Using Percentage in Excel
Here, we’ll calculate the 70th percentile for the given dataset which has some students’ scores in a class test. For that, we’ll use the PERCENTILE.INC function.
Steps:
- Type the following formula in Cell C11–
=PERCENTILE.INC(C5:C9,0.7)
- Then just press the Enter button to get the 70th percentile.
Read More: How to Create a Custom Formula in Excel (A Step-by-Step Guideline)
Conclusion
I hope the procedures described above will be good enough to create a formula in Excel to calculate a percentage. Feel free to ask any question in the comment section and please give me feedback.