Javatpoint Logo
Javatpoint Logo

How to calculate percentages in Microsoft Excel - formula examples

Calculation of the Percentage in Microsoft Excel is very useful as percentage operation is used in various fields of our (individual) life, whether it could be in a restaurant for the tipping purpose, for the reseller commission, or for income tax or interest rate.

In this tutorial, we are going to cover few of the techniques which will help us to calculate Percentages in Microsoft Excel:

  1. What is Percentage in Microsoft Excel.
  2. Percentage formula in Excel.
  3. Steps to calculate the Percentage of the total.
  4. Calculating the percentage difference between the two numbers.
  5. Calculating the amount as well as the total by Percentage.

Percentage in Microsoft Excel

"The word "per cent" is primarily derived from the Latin word per centum that means "by the hundred". And the Percentage in Excel of any respective number is the value out of 100."

If we want to calculate the Percentage in Microsoft excel, we need to divide the smaller numerical value by a larger numerical value and then will click on Percentage style, that are present on the Home menu ribbon.

The mathematical formula of Percentage used in Excel, is: (Numerator / Denominator) * 100

Now let us understand the formula as mentioned earlier of percentage calculation with the help of the example,

  • Example: If we have 40 Kit Kat chocolates and we have given 10 to our friends from them, how much did we give Percentage wise?
    And we can achieve this by performing the calculation that is =10/40*100; and then we will get the answer that is: 25%.

This is the way how we normally calculate percentages in school as well as in our everyday life.

Furthermore, no universal Excel formula for Percentages can cover all possible scenarios; now let us understand some of the simple as well as the basic formulas which can be efficiently used for the purpose of calculating a per cent in Microsoft Excel.

Basic Excel percentage formula

In Microsoft Excel the basic formula which can be effectively used to calculate the Percentage is as follows:

If we compare it to the basic mathematics formula for percentage calculations, we will notice that Excel's percentage formula will lack the *100 part. If we are moving on calculating a percentage, we do not need to multiply the resulting fraction by 100. This is because Excel performs this task automatically whenever the Percentage Format is used for a particular cell.

Now we will see how we can use the Excel percentage formula on real-life data. Let us suppose that we have the number of "Ordered items" in column B and the "Delivered items" in column C. And we need to find out the Percentage of delivered products, we will be performing the following steps which are as follows:

Step 1: In these step we will be entering out the formula =C2/B2 in cell D2, and then copying down to as many rows as we need.

Step 2: After that, we will click on the Percent Style button, which is present under the Home tab under the Numbergroup, to display the resulting decimal fractions as percentages.

Step 3: And after that we must needs to remember that we have to increase the number of decimal places if the user needs it accordingly.

And the same sequence of steps needs to be performed when we use the other percentage formula in Microsoft Excel.

  • For example, column D can display a round per cent of the individual delivered items without showing any decimal places.
How to calculate percentages in Microsoft Excel - formula examples

How to calculate the Percentage of the total in Microsoft Excel?

Now on moving further, we will be discussing some more of the examples that will helps used to calculate the percentage of the total in Microsoft Excel very well:

# Example 1: When the total is present on the end of the table in a given particular cell.

In the Microsoft Excel, the widely used case is none other than when we are having the total at the end of the table (created) in a single cell, so in these respective case the percentage formula will be much similar to the one that we have just discussed before, with the only difference being that a cell reference in the denominator is an Absolute reference with the dollar sign ($).

The dollar sign will then responsible for effectively fixing out the given reference to a given cell so that it will never get change if we copy down the formula as well.

  • For example, let us assume that we are having some values in column B as well as their total stored in cell B10. Then, after that we will be making use of the following formula for the purpose of calculating the percentages of the total respectively.

Besides all these, we can also make use of the relative cell reference for the particular cell B2 this is because if we want it to get changed whenever we copy downs the formula to other cells of column B.

But we need to enter $B$10 as an absolute cell reference because we want to leave the denominator fixed on cell B10 when auto-filling the formula down to row 9.

The screenshot below primarily depicts the results or outcomes returned by the formula.

How to calculate percentages in Microsoft Excel - formula examples

How can we calculate the percentage difference in Microsoft Excel?

We know that most formulas can be used for various purposes, but from them, the percentage change formula is most likely the one we use most often.

  • Excel formula for per cent increase/decrease

To calculate the Percentage of difference between two values, A and B, the formulas which can be used are as follows:

When we are moving on applying the above-mentioned formula to the accurate data, we must correctly determine which value is A and which is B.

  • For example, let us suppose that on yesterday morning we had almost 80 apples, and now we are having 100, which means that on current we are having 20 apples more than the before, that is a 25% increase. And if we had 100 apples and now we have 80, that means that our number of apples has been reduced by 20, which is a 20% decrease in the quantity of our apples.

Taking the above example, our Excel formula for the percentage change takes the following shape:

And soon after that, we will see how we can make use of this formula for the purpose of calculating the percent difference in the respective used excel sheet.

# Example 1: Calculating the per cent difference between 2 columns

In this example, we assume the last month's prices in column B and this (Current) month's prices in column C. Then, in that case, our per cent change formula takes will this form:

And now, to calculate the percentage difference between the two numbers correctly, we will carry out these steps in an exact manner.

Step 1: In the very first step we will be entering the formula in any blank cell in row 2 in the cell D2.

Step 2: And in the next step, we will be moving on selecting the formula cell after selecting it will click on the Percent Style button which are present under Home tab in order to convert the decimal value (number) to Percentage.

Step 3: Now, we will drag out the formula to copy it down to the below cells respectively.

And as per the result, the respective formula will helps in calculating the Percentage of change in this month present in the column C as compared with the last month which are present in the column B.

How to calculate percentages in Microsoft Excel - formula examples

Calculating the amount as well as the total by the help of the Percentage

We know that the analysis of the percentages in Microsoft Excel is basically a straightforward one and hence we can easily move on calculating the amounts as well as the total if we all know the percentage very well.

# Example 1: Calculate the amount by total and Percentage

In this example, we assume that we will buy a new laptop for $1000, and they charge approximately 12% VAT. So the question that will be arising is none other than- what is 12% of $1000?

The below-mentioned formula helps us to solve the above question very well:

Now we will assume, that the total value is basically present in the cell A2 and the cell B2 contains the percentage, and the above formula turns in into a simple one =A2*B2 and then it will returns 120 as the result.

How to calculate percentages in Microsoft Excel - formula examples

Youtube For Videos Join Our Youtube Channel: Join Now


Help Others, Please Share

facebook twitter pinterest

Learn Latest Tutorials


Trending Technologies

B.Tech / MCA