In this tutorial, you'll learn how to quickly calculate percentages in Excel, find the basic percentage formula, and a few more formulas to calculate percentage increase, percentage of total, and more.
Calculating percentages is useful in many areas of life, whether tipping restaurants, dealer commission, income tax, or interest rate. Suppose you were lucky enough to get a 25% promo code on a new plasma TV. Is this a good deal? And how much do you end up paying?
In this tutorial, we'll explore some techniques to help you calculate percentages efficiently in Excel and learn basic percentage formulas that will take the guesswork out of your calculations.
- basic concepts of percentage
- Excel percentage formula
- Calculate percentage of grand total
- Calculate the percentage difference between two numbers
- Calculate the amount and total as a percentage
- Increase/decrease a number by percentage
basic concepts of percentage
The term "percent" comes from the Latinfor a hundred, which means "in the hundreds". As you probably remember from high school math class, a percentage is a fraction of 100, calculated by dividing the numerator by the denominator and multiplying the result by 100.
The basic percentage formula is as follows:
(part/whole)*100 = percentage
For example, if you have 20 apples and you give 5 to your friends, what percentage do you give? By a simple calculation=5/20*100You will get the answer - 25%.
This is how you normally calculate percentages in school and in everyday life. Calculating percentage in Microsoft Excel is even easier as Excel automatically performs some operations for you in the background.
Unfortunately, there is no universal Excel percentage formula that covers all possible scenarios. If you ask someone "What percentage formula should I use to get the result you want?" you'll probably get a response like "Well, that depends on the exact result you want to get".
So let me show you some simple formulas to calculate a percentage in Excel for example
Excel simple percentage formula
The basic formula for calculating the percentage in Excel is as follows:
Part/Total = Percentage
If you compare with the basemath formula for percentage, you will notice that Excel's percentage formula does not contain the *100 part. When calculating a percentage in Excel, you don't need to multiply the resulting fraction by 100 because Excel does this automatically when thepercentage formatis applied to a cell.
Now let's see how to apply the Excel percentage formula to real data. Suppose you have the number of "Ordered Items" column B "Delivered Items" in column C. To determine the percentage of products shipped, follow these steps:
- enter the formula
=C2/B2in cell D2 and copy it to as many rows as needed. - tightenpercentage styleKnopf (get marriedguide >numbergroup) to display the resulting decimal fractions as a percentage.
- Remember to increase the number of decimal places if necessary, as explained inpercentage tips.
- Concluded! 🇧🇷
The same sequence of steps should be followed when using a different percentage formula in Excel.
In the example below, column D shows a rounded percentage of items sent with no decimal places shown.
Calculating percentage of grand total in Excel
In fact, the example above is a special case of calculating percentages of a grand total. Now let's look at a few more examples that will help you quickly calculate a percentage of a grand total in Excel for different sets of data.
Example 1. The total is at the bottom of the table in a specific cell
A very common scenario is when you have a total in a single cell at the bottom of a worksheet. In this case, the percentage formula is similar to the one just discussed, with the only difference being that a cell reference in the denominator is aabsolute reference(with $). The dollar sign fixes the reference to a specific cell so that it never changes no matter where the formula is copied.
For example, if you have some values in column B and your grand total in cell B10, use the following formula to calculate percentages of the grand total:
=B2/$B$10
You use a cell reference relative to cell B2 because you want it to change when you copy the formula to other cells in column B. However, you enter $B$10 as the absolute cell reference because you want to keep the denominator fixed in B10 when autocomplete the formula up to line 9.
Tip.To make the denominator an absolute reference, type the dollar sign ($) manually or click the cell reference in the formula bar and pressF4.
The following screenshot shows the results returned by the formula, thePercentages of TotalThe column is formatted as a percentage with 2 decimal places.
Example 2. Parts of the total are on multiple lines
In the example above, suppose you have multiple lines for the same product and you want to know what proportion of the total is made up of all orders for that particular product.
In that case, you can use the SUMIF function to first add up all the numbers for a given product and then divide that number by the sum, like this:
=SUMIF(range, criteria, sum_range) / total
Since column A contains all the product names, column B lists the corresponding quantities, cell E1 is the name of the product you are interested in, and the total is in cell B10, your real-world formula might look something like this :
=SUMMEWENN(A2:A9 ,E1, B2:B9) / $B$10
Of course, you can put the product name directly into the formula, like this:
=SUMMEWENN(A2:A9, "Kirschen", B2:B9) / $B$10
If you want to find out what percentage of the total total some different products represent, add the results returned by various SUMIFS functions and divide that number by the total. For example, the following formula calculates the percentage of cherries and apples:
=(SUMMEWENN(A2:A9, "Kirschen", B2:B9) + SUMMEWENN(A2:A9, "Äpfel", B2:B9)) / $B$10
For more information about the SUM function, see the following tutorials:
- How to use the SUMIF function in Excel
- Excel SUMIFS and SUMIF with multiple criteria
How to Calculate Percent Difference in Excel
Of all the formulas for calculating percentages in Excel, a percentage change formula is probably the one you would use the most.
Excel Formula for Percent Increase/Decrease
To calculate the percentage difference between two A and B values, the general formula is:
Percentage Change = (B - A) / A
When applying this formula to real-world data, it's important to correctly determine which value is A and which is B. For example, yesterday you had 80 apples and since you have 100, that means you now have 20 more apples than you have. before, which represents an increase of 25%. If you had 100 apples and now have 80, your number of apples has decreased by 20, which is a 20% decrease.
With the above, our Excel formula for percentage change takes the following form:
(New Value - Old Value) / Old Value
Now let's see how you can use this formula to calculate the percentage difference in your spreadsheets.
Example 1. Calculating the percentage difference between 2 columns
Suppose you have last month's prices in column B and this month's prices in column C. Then your formula for percentage change will look like this:
= (C2-B2)/B2
Follow the steps below to correctly calculate the percentage difference between two numbers.
- Enter the formula in any blank cell in row 2, say D2. This will output the result as a decimal number.
- Select the formula cell and clickpercentage stylebutton onget marriedGuide to Convert Decimal to Percent.
- Drag the formula down to copy it to the lower cells.
As a result, the formula calculates the percentage change for this month (column C) compared to the previous month (column B). Positive percentages, indicating a percentage increase, are usually formatted in black, while negative percentages (percentage decrease) are formatted in red. To make this happen automatically, set up a custom negative percentage format as explained inthis tip.
Example 2. Calculating the percentage difference between two numbers
If you have a column of numbers, say column C, listing weekly or monthly sales, you can calculate the percentage change between the previous and current week/month using this formula:
= (C3-C2)/C2
Where C2 and C3 are the numbers you are comparing.
Observation.Be sure to skip the first row of data and put your percentage difference formula on the 2nd rowndcell, in this example D3.
forDisplay decimal numbers as a percentage, apply the percentage format to the cells containing your formula, and you'll get the following result:
To calculate the percentage change between a certain number and all other numbers, fix the address of that cell using the $ sign, for example $ € 2.
For example, to calculate each month's percentage increase/decrease compared to January (C2), the formula in D3 is:
=(C3-$C$2)/$C$2
When copying the formula to subsequent cells, the absolute reference ($C$2) remains the same while the relative reference (C3) changes to C4, C5, etc. based on a relative position of the line where the formula will be copied.
For more example formulas, seeHow to Calculate Percent Change in Excel.
Calculate amount and total amount in percentage
As you've just seen, calculating percentages in Excel is easy, as is calculating amounts and totals once you know the percentage.
Example 1. Calculating Amount by Grand Total and Percentage
Let's say you buy a new laptop for $950 and you charge 11% sales tax on that purchase. The question is, how much do you have to pay on top of the net price? In other words, what is 11% of $950?
The following formula helps:
Total * Percentage = Amount
Assuming the total is in cell A2 and the percentage is in B2, the above formula becomes simple=A2*B2and returns 104.50.
Remember, when you type a number followed by the percent sign (%) in Excel, the number is interpreted as hundredths of its value. For example, 11% is actually stored as 0.11 and Excel uses this underlying value in all formulas and calculations.
In other words, the formula=A2*11%it's equivalent to=A2*0,11🇧🇷 Of course, you can use the decimal equivalent of the percentage directly in a formula if that works better for your spreadsheets.
Example 2. Calculating the total by amount and percentage
Example: Your friend offered you his old computer for $400, which is 30% off the original price. They want to know what the original price was.
Since 30% is the discount, first subtract it from 100% to find out what percentage you actually have to pay (100% - 30% = 70%). Now you need the formula to calculate the original price, i.e. to find the number whose 70% is 400.
The formula is as follows:
Value / Percentage = Total
Applied to real data, it can take one of the following forms:
=A2/B2
or
=A2/0,7
or
=A2/70%
Tip.For an answer to a more difficult question - how to calculate the interest amount on a loan payment when you know an interest rate - read theIPMT function.
How to increase/decrease a number by percentage
The holiday season is upon us, and this indicates a change in your usual weekly spending. You may want to consider different options to determine your ideal weekly dose.
forincreasean amount as a percentage, use this formula:
= amount * (1 + %)
For example, to increase the value in cell A1 by 20%, the formula is:
=A1*(1+20%)
forpsychiatrista percentage amount:
= quantity * (1 - %)
For example, to reduce the value in cell A1 by 20%, the formula is:
=A1*(1-20%)
In our example, if A2 is your current expenses and B2 is the percentage by which you want to increase or decrease that amount, here are the formulas you would enter into cell C2:
Increase percent:
=A2*(1+B2)
Reduce percentage:
=A2*(1-B2)
How to increase/decrease an entire column by a percentage
Suppose you have a column of numbers that you want to increase or decrease by a certain percentage, and you want the updated numbers in the same column instead of adding a new column with the formula.
Here are 5 quick steps to accomplish this task:
- Enter all the numbers you want to increase or decrease in one column, in this example column B.
- In a blank cell, enter one of the following formulas:
Increase percent:
=1+20%Reduce percentage:
= 1-20%
Of course, you can replace 20% in the above formulas with any percentage you like.
- Highlight the cell with the formula (C2 in our case) and copy it by pressingStrength + C.
- Select the range of cells you want to change, right-click the selection, and clickInsert special...
- I'minsert specialSelect dialog windowvaluesin betweenNecklace,Multiplyin betweenOperationand clickOK.
And here is the result - all numbers in column B are increased by 20%:
In the same way you canmultiplyorsharea column of numbers in a certain percentage. Just type the percentage you want into a blank cell and follow the steps above.
How to calculate percentage in excel. And even if working with percentages has never been your favorite way to do math, you can have Excel do the work for you using these basic percentage formulas. That's it for today, thanks for reading!
You might also be interested in
- Compound Interest Formula for Excel
- How to Multiply Cells in Excel with Formula Examples
- Calculate CAGR (Compound Annual Growth Rate) in Excel