How to Create a Formula to Calculate Percentage in Excel

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.

Formula to Calculate Percentage of Total in Excel

  • To convert it to a percentage format, click as follows: Home > Number > %

Formula to Calculate Percentage of Total in Excel

  • Finally, drag down the Fill Handle icon all the way to copy the formula.

Formula to Calculate Percentage of Total in Excel

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.

Formula to Find Percentage Increase or Decrease in Excel

  • Finally, drag down the Fill Handle icon to get the other percentages.

Formula to Find Percentage Increase or Decrease in Excel

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.

Calculate Partial and Total Amount in Excel by Using a Percentage Formula


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.

Calculate Partial and Total Amount in Excel by Using a Percentage Formula


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.

Formula to Determine Increase or Decrease of a Number by Percentage in Excel


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.

Formula to Determine Increase or Decrease of a Number by Percentage in Excel


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.

Formula to Determine Increase or Decrease of a Number by Percentage in Excel

  • After that, use the Fill Handle tool to copy the formula for the other cells.

Formula to Determine Increase or Decrease of a Number by Percentage in Excel

  • Then copy the cells C8:C12.

Formula to Determine Increase or Decrease of a Number by Percentage in Excel

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

Formula in Excel to Calculate Profit, Loss, and Discount Using Percentage


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.

Formula in Excel to Calculate Profit, Loss, and Discount Using Percentage


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.


Related Articles

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo