Excel is known for its multiple uses in the field of all computational tasks. When you need to calculate the percentage of some value, you can do it manually as well. However, with Excel, this simple task becomes even simplest. The percentage formula in Excel appears as a value out of 100 such as: if your value is 80% it means 80 out of 100 and 45% is 45 out of 100.
A person dealing all-time with numbers may even sometime have to go through percentages. We deal with multiple functions daily where percentages are calculated. For instance, if a crockery store has to offer a discount, it would have to use a percentage formula in Excel. Or else we have some other conditions in which the percentage formula is used. Well, not to mention, a percentage is not something grueling whether it is manually or with Excel.
In the case of Excel, you can avail yourself plenty of ways to calculate percentages just like other functions. In your worksheet, you can manage to apply the percentage formula easily and it does not matter whether your data is lengthy or short.
Basics of Percentage
Everyone knows about percentage but how did this term come up is a mystery for many people. The term “Per cent” is originated from the Latin per centum, which means “by the hundred”. You must have an idea that a percentage is a fraction of 100 that divides with the numerator by the denominator and the outcome value is multiplied by 100.
Below you will find the basic percentage formula in Excel:
(Part/Whole) *100 = Percentage
Suppose you have 20 books in the rack and one of your friends borrowed 5 books. How would you calculate the percentage in this condition?
You will simply apply a calculation as 5/20*100 and the answer will be 25%. Unfortunately, you will not find a comprehensively general formula that can cover all the conditions for percentages. So, let’s have a look at some formulas used for percentage calculations in Excel.
Basic Percentage Formula in Excel
The basic percentage formula is already mentioned above:
Part/Whole = Percentage
While analyzing this formula, you will notice the “(*100)” portion is missing as it is in the math formula for percentage. In Excel, you will see a bit different calculation procedure used for percentages, because Excel by default applies the percentage format to a cell while cutting off the multiplying part.
Add a Percentage
To calculate a percentage in Excel, you will have to follow the steps given below:
- At first, you will have to enter a decimal value.
- From the Number group given on the Home tab, choose the percentage symbol to apply the format of percentage.
- Finally, you will get the desired results.
Calculating Percentage of Total in Excel
Let’s find out with an example how you can use a percentage formula in Excel using a certain condition. How you would calculate a percent of a total in Excel?
Example 1: In a particular cell of the table, you will see the total value.
Generally, the most common condition is when the total appears at the end of the table. Here, in this case, we will use the same formula for percentage as we did with a cell reference in the denominator is an absolute reference. Also, bear in mind that the dollar sign operates the reference to a given cell.
For instance, suppose you are having values in column B and the total is placed in cell B10, you will have to apply the below-given formula to calculate percentages of the total:
=B2/$B$10
To cell B2, you ought to use a relative cell reference, as you needed it to make alterations while copying the formula to other cells of column B. however, $B$10 is an absolute cell reference value just in case you want the denominator to disappear that is fixed on B10 while auto-filling the formula to row 9.
Bonus Tip: When you want to turn the denominator into an absolute reference, you either have to enter the dollar sign $ manually, or else, you can click the cell reference in the formula bar while pressing F4. In the image given below, you will witness the outcome returned by the formula, the Percentages of Total column is appeared as a percentage with 2 decimal places showing.
Calculating Percent Change in Excel
Amongst all the formulas for calculating percentages in Excel, this percent change formula is most likely the widely used formula.
Excel Formula for Percentage Change (percent increase/decrease)
Suppose you have Values A and B to calculate percent change, you will have to use the following formula:
Percent Change = (B – A) / A
In real-life situations, when you are using this formula, ensure that you correctly enter the value A and B. Suppose, the day before yesterday you had 80 extra chairs in the office and now you have 100. It simply means that the value 20 is added to the earlier value of 80 and that is a 25% increase.
On the other hand, if you had 100 chairs before and now you have 80 chairs in total, the number of chairs is reduced by 20 and that is a 20% decrease.
Talking about the above example, the percentage formula change will come up as:
(New Value – Old Value) / Old Value
Things to Consider When Formatting Percentages
Below are some important points you need to take seriously while formatting percentages:
Format Existing Values
Excel will multiply the number that is already in the cell while in the process of applying percentage formatting. The multiplication will be done by 100 while adding the % sign at the end. Suppose you have the value 10 in cell A1 and the percentage number format is applied, and that number is multiplied by 100, which clearly shows it as a percentage.
If you enter the formula =10/100 in cell A2, in Excel you will see the outcome as 0.1. Later on, if you format it as a percentage, the number will be 10%.
Rounding Issues
Suppose that the value in the cell (e.g., 10%) does not match what you need (e.g., 9.75%). When you are having a rounded version rather than the true percentage in the cell, you will have to increase the decimal places.
Format Empty Cells
You will find Excel acts in a different way when empty cells are pre-formatted with percentage formatting. By default, you will see the percentage conversion of the numbers equal to and larger than 1. On the other hand, numbers smaller than 1 are multiplied by 100 to turn them into percentages. For instance, in a pre-formatted cell, if you enter 10 or .1, you will get 10% in the cell. Alternatively, when you put 0.1 in the cell, Excel will return a 0% or 0.10% value.
Negative Percentages
When you need to treat negative percentages in a different manner, suppose you want to appear within parenthesis, you need to make a custom number format such as 0.00%;[Red]-0.00% or 0.00%_);(0.00%).
Enjoy applying percentage formulas in Excel throughout in a different manner.
