Percentage formulas
Percent of total, percent change, adding and removing a percentage, and margins. 60 answers, each checked by running it, with a live example and an exercise.
How to calculate percentage change between two numbers in Excel
=(C2-B2)/B2Subtract the old value from the new one and divide by the old: =(C2-B2)/B2, formatted as a percentage.
How to calculate the percentage of a total in Excel
=B2/SUM($B$2:$B$7)Divide each value by the total, with the total locked: =B2/SUM($B$2:$B$7), or =B2/$B$8 if the total is in a cell.
How to add a percentage to a number in Excel
=B2*(1+$F$1)Multiply by 1 plus the percentage: =B2*(1+$E$1). To increase by 8%, multiply by 1.08.
How to subtract a percentage from a number in Excel
=B2*(1-$F$1)Multiply by 1 minus the percentage: =B2*(1-$F$1). A 15% discount is 85% of the price.
How to calculate what percentage one number is of another in Excel
=B2/C2Divide the part by the whole: =B2/C2, and format the result as a percentage.
How to calculate profit margin percentage in Excel
=(B2-C2)/B2Divide profit by revenue: =(B2-C2)/B2, where B is the price or revenue and C the cost.
How to calculate percentage change with negative numbers in Excel
=(C2-B2)/ABS(B2)Divide the change by the absolute value of the old number:
=(C2-B2)/ABS(B2), so an improvement from a loss shows as positive.How to cap a percentage at 100% in Excel
=MIN(B2/C2,1)Wrap the ratio in MIN with 1, since 100% is 1:
=MIN(B2/C2,1).How to calculate percent complete in Excel
=COUNTIF(B2:B11,"Done")/COUNTA(B2:B11)Count the finished tasks and divide by all tasks:
=COUNTIF(B2:B11,"Done")/COUNTA(B2:B11), formatted as a percentage.How to calculate the percentage of Yes responses in Excel
=COUNTIF(B2:B13,"Yes")/COUNTA(B2:B13)Count the Yes answers and divide by the number of answers:
=COUNTIF(B2:B13,"Yes")/COUNTA(B2:B13).How to calculate each category's share of the total in Excel
=SUMIF($A$2:$A$11,E2,$B$2:$B$11)/SUM($B$2:$B$11)Sum the category with SUMIF and divide by the grand total:
=SUMIF($A$2:$A$11,E2,$B$2:$B$11)/SUM($B$2:$B$11).How to calculate the discount percentage between two prices in Excel
=1-C2/B2Divide the sale price by the original and subtract from 1:
=1-C2/B2, formatted as a percentage.How to find the original price before a discount in Excel
=B2/(1-C2)Divide the sale price by 1 minus the discount:
=B2/(1-C2). A $60 price after 25% off was $80.How to remove VAT from a price in Excel
=B2/(1+$F$1)Divide the gross price by 1 plus the VAT rate:
=B2/(1+$F$1). With 20% VAT, 120 becomes 100.How to calculate the tax included in a total price in Excel
=B2*$F$1/(1+$F$1)Multiply the total by the rate over 1 plus the rate:
=B2*$F$1/(1+$F$1). With 10% tax, a 110 total includes 10 of tax.How to calculate markup percentage in Excel
=(C2-B2)/B2Divide the profit by the cost:
=(C2-B2)/B2, where B is the cost and C the selling price.How to calculate selling price from cost and a target margin in Excel
=B2/(1-$F$1)Divide the cost by 1 minus the margin:
=B2/(1-$F$1). A $30 item at a 40% margin sells for $50.How to convert markup to margin and back in Excel
=B2/(1+B2)Margin is markup divided by 1 plus the markup:
=B2/(1+B2). To go back, markup is=margin/(1-margin).How to calculate a tip and split the bill in Excel
=ROUNDUP(B2*(1+$G$1)/C2,2)Add the tip to the bill and divide by the number of people, rounded up to the cent:
=ROUNDUP(B2*(1+$G$1)/C2,2).How to calculate the percentage difference between two numbers in Excel
=ABS(B2-C2)/AVERAGE(B2,C2)Divide the absolute difference by the average of the two:
=ABS(B2-C2)/AVERAGE(B2,C2).How to calculate the change in percentage points in Excel
=(C2-B2)*100Subtract one percentage from the other and multiply by 100:
=(C2-B2)*100. A rise from 2.5% to 3.0% is 0.5 points.How to calculate percent of row total in Excel
=B2/SUM($B2:$E2)Divide each cell by its row's total with the columns locked:
=B2/SUM($B2:$E2), filled across and down.How to calculate percent of grand total in a table in Excel
=B2/SUM($B$2:$D$5)Divide each cell by the sum of the whole table, fully locked:
=B2/SUM($B$2:$D$5), filled across and down.How to combine text with a percentage in Excel
=A2&" margin: "&TEXT(B2,"0.0%")Format the number inside the formula with TEXT:
=A2&" margin: "&TEXT(B2,"0.0%").How to convert percentage text to a number in Excel
=VALUE(A2)VALUE understands the percent sign:
=VALUE(A2)turns the text "45%" into the number 0.45.How to calculate percent rank in Excel
=COUNTIF($B$2:$B$11,"<"&B2)/(COUNT($B$2:$B$11)-1)Count the values below each one and divide by the count minus 1:
=COUNTIF($B$2:$B$11,"<"&B2)/(COUNT($B$2:$B$11)-1), the same as PERCENTRANK.INC.How to calculate budget variance percentage in Excel
=(C2-B2)/B2Divide actual minus budget by the budget:
=(C2-B2)/B2. Positive means over budget.How to calculate the percentage of cells that meet a condition in Excel
=COUNTIF(B2:B13,">="&E1)/COUNT(B2:B13)Count the cells that meet it and divide by the count of all cells:
=COUNTIF(B2:B13,">="&E1)/COUNT(B2:B13).How to calculate a weighted grade percentage in Excel
=SUMPRODUCT(B2:B6,C2:C6)/SUM(B2:B6)Multiply each score by its weight and divide by the total weight:
=SUMPRODUCT(B2:B6,C2:C6)/SUM(B2:B6).How to average percentages correctly in Excel
=SUM(C2:C6)/SUM(B2:B6)Don't average the rates; divide the totals:
=SUM(C2:C6)/SUM(B2:B6)gives the true overall rate.How to calculate a percent of a number in Excel
=B2*$F$1Multiply the number by the percentage:
=B2*$F$1, where F1 holds 30%. 30% of 12,500 is 3,750.How to find the total when you know the part and the percentage in Excel
=B2/C2Divide the part by the percentage:
=B2/C2. If 45 is 18% of the total, the total is 250.How to calculate cumulative percentage in Excel
=SUM($B$2:B2)/SUM($B$2:$B$8)Divide a running total by the grand total:
=SUM($B$2:B2)/SUM($B$2:$B$8), filled down.How to calculate attendance percentage in Excel
=COUNTIF(B2:G2,"P")/COUNTA(B2:G2)Divide the days present by the days recorded:
=COUNTIF(B2:G2,"P")/COUNTA(B2:G2), filled down.How to calculate each value as a percent of the maximum in Excel
=B2/MAX($B$2:$B$6)Divide each value by the largest one, locked:
=B2/MAX($B$2:$B$6). The top value becomes 100%.How to calculate commission with tiered percentage rates in Excel
=B2*XLOOKUP(B2,$E$2:$E$5,$F$2:$F$5,,-1)Look up the rate for the sales amount in a tier table, then multiply:
=B2*XLOOKUP(B2,$E$2:$E$5,$F$2:$F$5,,-1).How to show percentage change with up and down arrows in Excel
=TEXT(C2/B2-1,"▲0.0%;▼0.0%;0.0%")Give TEXT a format with an arrow for each sign:
=TEXT(C2/B2-1,"▲0.0%;▼0.0%;0.0%").How to calculate the top 3 share of a total in Excel
=SUMPRODUCT(LARGE(B2:B11,{1,2,3}))/SUM(B2:B11)Add the three largest values with LARGE and divide by the total:
=SUMPRODUCT(LARGE(B2:B11,{1,2,3}))/SUM(B2:B11).How to calculate percent of subtotal for each group in Excel
=C2/SUMIF($A$2:$A$10,A2,$C$2:$C$10)Divide each row by its group's total from SUMIF:
=C2/SUMIF($A$2:$A$10,A2,$C$2:$C$10).How to round percentages in Excel
=ROUND(B2,3)A percentage is a decimal, so round one more place than you want to show:
=ROUND(B2,3)keeps one decimal place of a percent (45.7%).How to calculate the percentage of each answer in a survey in Excel
=COUNTIF($B$2:$B$16,E2)/COUNTA($B$2:$B$16)Count each answer and divide by all answers:
=COUNTIF($B$2:$B$16,E2)/COUNTA($B$2:$B$16), filled down the list of answers.Why percentages show 100 times too big in Excel
=B2/100The cells hold 15 instead of 0.15. Divide by 100 before formatting as a percentage:
=B2/100.How to split an amount by percentages in Excel
=$F$1*B2Multiply the locked total by each row's percentage:
=$F$1*B2, filled down.How to calculate the percentage gain needed to recover a loss in Excel
=1/(1-B2)-1Divide 1 by what is left after the drop and subtract 1:
=1/(1-B2)-1. After a 20% drop you need a 25% rise.How to apply two discounts in a row in Excel
=B2*(1-C2)*(1-D2)Multiply by 1 minus each discount:
=B2*(1-C2)*(1-D2). 20% off then 10% off is 28% off, not 30%.How to calculate percent change from the first to the last value in Excel
=XLOOKUP(TRUE,B2:B13<>"",B2:B13,,0,-1)/B2-1Find the last filled value with XLOOKUP from the bottom and compare it with the first:
=XLOOKUP(TRUE,B2:B13<>"",B2:B13,,0,-1)/B2-1.How to calculate percent of total with ARRAYFORMULA in Google Sheets
=ARRAYFORMULA(IF(B2:B10="","",B2:B10/SUM(B2:B10)))One formula at the top fills the column:
=ARRAYFORMULA(IF(B2:B10="","",B2:B10/SUM(B2:B10))).How to calculate win percentage in Excel
=(B2+0.5*D2)/(B2+C2+D2)Count ties as half a win:
=(B2+0.5*D2)/(B2+C2+D2), with wins in B, losses in C and ties in D.How to calculate month-over-month growth in Excel
=B3/B2-1Divide each month by the month before and subtract 1, starting in the second row:
=B3/B2-1, filled down.How to check if a value is within a percentage of a target in Excel
=IF(ABS(C2-B2)/B2<=$G$1,"OK","Check")Compare the relative difference with the tolerance:
=IF(ABS(C2-B2)/B2<=$F$1,"OK","Check").How to scale values to 0-100% in Excel
=(B2-MIN($B$2:$B$9))/(MAX($B$2:$B$9)-MIN($B$2:$B$9))Use min-max scaling:
=(B2-MIN($B$2:$B$9))/(MAX($B$2:$B$9)-MIN($B$2:$B$9)). The lowest value becomes 0% and the highest 100%.How to calculate employee turnover rate in Excel
=C2/AVERAGE(B2,D2)Divide leavers by the average headcount:
=C2/AVERAGE(B2,D2), where B is the starting and D the ending headcount.How to calculate the percentage of projects within budget in Excel
=SUMPRODUCT(--(C2:C11<=B2:B11))/ROWS(B2:B11)Count the rows where actual is at most budget, then divide by the number of projects:
=SUMPRODUCT(--(C2:C11<=B2:B11))/ROWS(B2:B11).How to calculate percent of goal when lower is better in Excel
=B2/C2Divide the target by the actual instead:
=B2/C2. Beating a 4-hour target with 5 hours scores 80%.How to calculate pacing to a monthly goal in Excel
=(C2/B2)/(DAY($G$1)/DAY(EOMONTH($G$1,0)))Compare progress with the share of the month gone:
=(C2/B2)/(DAY($G$1)/DAY(EOMONTH($G$1,0))). Above 100% means ahead of pace.How to index values to 100 from a base year in Excel
=B2/$B$2*100Divide each value by the locked base value and multiply by 100:
=B2/$B$2*100.How to gross up an amount for tax in Excel
=B2/(1-$F$1)Divide the net amount by 1 minus the tax rate:
=B2/(1-$F$1). To pay someone 1,000 after 22% tax, the gross is 1,282.05.How to calculate the percentage within each group using COUNTIFS in Excel
=COUNTIFS($B$2:$B$13,E2,$C$2:$C$13,"Remote")/COUNTIF($B$2:$B$13,E2)Count the group's rows that meet the condition and divide by all the group's rows:
=COUNTIFS($B$2:$B$13,E2,$C$2:$C$13,"Remote")/COUNTIF($B$2:$B$13,E2).How to convert a score like 18/20 to a percentage in Excel
=TEXTBEFORE(B2,"/")/TEXTAFTER(B2,"/")Split the text at the slash and divide:
=TEXTBEFORE(B2,"/")/TEXTAFTER(B2,"/"), formatted as a percentage.How to find the items that make up 80% of the total in Excel
=IF(SUM($B$2:B2)-B2<0.8*SUM($B$2:$B$8),"Top 80%","")Sort largest first, then flag a row while the total of the rows above it is still under 80% of the grand total: =IF(SUM($B$2:B2)-B2<0.8*SUM($B$2:$B$8),"Top 80%","").