Percentage Calculation Formula in Excel
Percentage Basics | Calculating Percent Change | Increase/Decrease Number by Percentage | Calculating Amount by Price and Percentage
Suppose you're sitting at your desk, and you have to calculate an unknown percentage in Excel. You do not have time to work on it manually.
It's times like this that you're desperate for percentage calculation formula in Excel that can be used in your spreadsheet.
But before you tear your hair out, here are a few super-fast formulas to calculate percentages in Excel.
Formulas in Excel are the backbone of any spreadsheet work. It can help enhance productivity by eliminating time-consuming manual calculations.
Let us explore a few techniques that will help you efficiently calculate percent in Excel and learn the basic percentage formulas that will make calculating it a breeze.
Percentage Basics
"Percentage" is derived from the Latin word per centum, which means by the hundred.
A percentage is a fraction of a hundred that is calculated by dividing the numerator by the denominator and multiplying the result by 100.
Percentage = (Part/Whole)*100
Here is an example to make this easier: John has 50 bananas. He gives 10 bananas to his friends. What percentage of apples did John give away? (10/50)*100 = 20%
Well, you learned this in school. Calculating percentages in Excel is much simpler since the process is automated.
Unfortunately, there is no single Excel formula that covers all scenarios. Every individual uses different approaches based on their needs.
Here, we will look at several Excel formulas for computing percentages.
1. Basic Excel Percentage Formula
The basic percentage calculation formula in excel is:
Part/Total = Percentage
In Excel, you need not multiple the factor with 100 since Excel does the job.
For the example shown below, we first calculate the percentage.
You must follow the same steps when using any other percentage formula in Excel.
Our example displays a rounded percent of delivered items, without any decimal places.
2. Calculating percent change in Excel
A percent change formula is probably the most commonly used percentage calculation formula in Excel.
The following formula can be used to calculate a percent change between values A and B: Change in percentage = (B - A) / A
The key to applying this formula to real data is to determine which value represents A and which represents B.
For example, yesterday you sold 80 shirts and today you sold 100 of them, meaning that today you have sold 20 more shirts than the previous day, which is a 25% increase. If you had sold 100 shirts yesterday and today you sold 20, your sales have declined by 20, which is a 20% decrease.
Taking all of the above into consideration, we arrive at the following Excel formula:
(New Value - Old Value) / Old Value
Let's see how you can use this percentage change formula in spreadsheets.
Example: Calculating percentage changes in sales between 2 months.
Let's say you have the previous month's sales in column B and this month's sales in column C. To calculate percentage change in your Excel sheet, enter the following formula in cell D2:
=(C2-B2)/B2
Remember to click the Percent Style button on the ribbon to display decimals as percentages, and this should result in the same effect as the screenshot below.
3. Increase/Decrease a Number by Percentage
Let's say you are running an ad campaign and you need to increase your budget by a certain amount and vice versa. Here's a percentage calculation formula in excel to help you with that.
Use this formula to increase an amount by a percentage:
= Amount * (1 + %)
Cell A1 is increased by 20% by using the formula =A1*(1+20%).
And this formula to decrease it by a percentage:
= Amount * (1 - %)
By using the formula =A1*(1-20%), for example, you can reduce the value of cell A1 by 20%.
Here are the formulas you would enter in cell C2 if A2 is your current budget and B2 is the percentage you want to increase or decrease it by:
Increase by percentage: =A2*(1+B2)
Reduce by percentage: =A2*(1-B2)
4. Calculating amount by price and percentage
Imagine you are buying a new smartphone for Rs.30.000 and they charge you a VAT of 15%. How much will you have to pay on top of the net price?
You can use the following formula:
Total * Percentage = Amount
When the net price is in cell A2 and the percent in cell B2, the above formula becomes
=A2*B2, and the tax is Rs.4,500.
Here's how you calculate percentages in Excel. Following these basic percentage calculation formula in excel will save you a lot of painstaking percentages calculations.
