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:
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,
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.
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.
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 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.
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.
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.
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.