Excel Office

Excel How Tos, Tutorials, Tips & Tricks, Shortcuts

Tag: Percentage

How to calculate percent variance in Excel

You can calculate a percent variance by subtracting the original number from the new number, then dividing that result by the original. For example, if the baseline number is 100, and the new number is 110: =(110-100)/100 This formula can be used to calculate things like variance between this year and last year, variance between a budgeted and actual values,…

How to calculate decrease by percentage in Excel

If you need to decrease a number by a certain percentage, you can use a simple formula that multiplies the number times the percent – 1. Formula =number*(1-percent) Explanation In the example, the active cell contains this formula: =C6*(1-D6) In this case, Excel first calculates the result of 1 – the value in D6 (.2) to get 0.8 which it…

How to get amount with percentage in Excel

If you have a total and a percentage that presents some amount of the total, you can calculate the amount represented by the percentage with a simple formula that multiples the total by the percentage. Formula = total * percent Explanation In the example, the active cell contains this formula: =C6*D6 To calculate the amount, Excel simply multiples the value…

How to get original number from percent change in Excel

To calculate the original number based on current value and known percentage change, you can use a simple formula that divides the current value by the percent plus 1. Formula =current/(percent+1) Explanation In the example, the active cell contains this formula: =C6/(B6+1) In this case, Excel first calculates the result of B6 + 1, then divides C6 by the result: =C6/(B6+1) =200/1.15 =173.913 Rounded result…

How to get original price from percentage discount in Excel

If you have a discounted price and know the discount percentage, you can calculate the original price with a simple formula that divides the discounted price by the result of 1 minus the discount percentage. See example below: Formula =price/(1-discount) Explanation How the formula works In the example, the active cell contains this formula: =C6/(1-D6) In this case, Excel first…

How to calculate percent change in Excel

If you need to calculate the percentage change between two values in Excel, you can use a formula that divides the change itself by the “old” value. A classic example would be to calculate the percentage between sales last month and sales this month. Formula =(new_value-old_value)/old_value Explanation In the example, the active cell contains this formula: =(D6-C6)/C6 In this case,…

How to calculate percentage discount in Excel

If you have a discounted price and an original price, and you want to know the discount as a percentage, you can calculate the percentage discount using a formula that divides the discounted price by the original price and then subtracts the result from one. Formula = 1-(discount_price/original_price) Explanation In the example, the active cell contains this formula: =1-(D5/C5) In…

How to calculate percentage of total in Excel

To calculate percent of a total (i.e. calculate a percent distribution), you can use a formula that simply divides an amount by the total. Formula =amount/total Explanation In the example shown, the formula in D6 is: =C6/$C$11 Note: the result is formatted with Percentage number format to show 25%, 10%, etc. How the formula works In the example, D6 contains the formula…

How to calculate profit margin percentage in Excel

If you need to calculate a profit margin, you can easily do so with a simple formula that uses the sale price and the cost. Formula =(price-cost)/price Explanation In the example shown, the first formula looks like this: =(B4-C4)/B4 Make sure you use parentheses to control the order of operations. Note that the result will be a decimal like 0.40,…

How to calculate total from percentage in Excel

If you have an amount and the percentage that the amount represents of a total, you can calculate the total with a formula that simply divides the amount by the percentage. Formula = amount / percent Explanation In the example, the active cell contains this formula: =C6/D6 Excel simply divides the value in cell C6 by the percentage value in…

How to increase by percentage in Excel

If you need to increase a number by a certain percentage, you can use a simple formula that multiplies the number times the percent + 1. Formula =number*(1+percent) Explanation How this formula works In the example, the active cell contains this formula: =C6*(1+D6) In this case, Excel first calculates the result of 1 + the value in D6 (.2) to…

How to calculate percent of goal in Excel

If you need to calculate percent of goal, you can do so with a simple formula that divides the actual by the goal amount, with the result formatted using the percentage number format. This same formula can be used to calculate things like percent of target, percent of budget, percent of forecast, and so on. Formula =actual/goal Explanation In the…

How to calculate percent of students absent in Excel

To calculate percent of students attending a class, given a total class size and the number of students attending, you can use a simple formula that divides students absent (calculated by subtracting attending from total) by the total. The result must be formatted using the percentage number format. Formula =(total-attended)/total Note: when working manually, percentage results are calculated by dividing one…

How to calculate percent sold in Excel

To calculate percentage sold, you can use a simple formula that divides sold amount by the total amount. Formula =sold/total Note: when working manually, percentage results are calculated by dividing one number by another, then multiplying by 100 to express as a percentage. However, because Excel can display decimal or fractional values automatically as a percentage by applying the Percentage number format, there…

How to calculate project complete percentage in Excel

To calculate the percentage complete for a project with a list of tasks, you can use a simple formula based on the COUNTA function. Formula =COUNTA(range1)/COUNTA(range2) Explanation In the example shown, the formula in F6 is: =COUNTA(C5:C11)/COUNTA(B5:B11) How this formula works At the core, this formula simply divides tasks complete by the total task count: =complete/total which is then formatted…