Excel Percentage Formula: Calculate Increase, Change and Difference

How percentages work in Excel

Quick answer: Excel stores a percentage as a decimal, so 19% is 0.19. Divide to get the decimal, then apply the Percentage format: =A2/B2 for a share of a total, =(B2-A2)/A2 for a change. Do not also multiply by 100.

Excel percentage formulas at a glance

Format the result cells as Percentage where the answer is a percentage. In the add, subtract and amount rows B2 holds the rate, such as 15%.

How Excel stores percentages

A cell that shows 19% holds 0.19, and typing 19% into a cell stores 0.19 as well. The Percentage format changes how the number looks, not what it is. So when =(B2-A2)/A2 returns 0.19, the formula is correct and the fix is formatting, not another formula.

Two behaviours cause most of the confusion. Applying the Percentage format to numbers already in a cell multiplies them by 100, so 10 becomes 1000%. In cells that are already formatted as Percentage, Microsoft documents that typing 10 or 0.1 both give 10%.

Percentage of a total

Divide the part by the whole: =A2/B2. For a column of parts, lock the total so it survives fill-down: =A2/SUM(A$2:A$4), or =A2/$A$5 when A5 holds the total. Parts of 12, 18 and 30 against a total of 60 return 20%, 30% and 50%.

Microsoft 365 also has PERCENTOF(data_subset, data_all), which is equivalent to SUM(subset)/SUM(all). Microsoft documents it for Excel for Microsoft 365 only, so use the division form if the file will be opened in older Excel.

Find an amount or a total from a percentage

To find the amount, multiply: =A2*B2 with 120 and 35% returns 42. To find the total, divide the amount by the percentage: =A2/B2 with 42 and 35% returns 120. The second form is the reverse percentage problem, where the part and the rate are known and the whole is missing.

Format cells as percentage

Select the cells and press Ctrl+Shift+% on Windows, or use Home, then the Percent Style button. For control over decimal places, open Format Cells, choose Number, then Percentage. Formatting changes the display only. To round the stored value as well, wrap the formula: =ROUND((B2-A2)/A2,4) stores 0.1900 for a 19% change.

Percentage increase formula in Excel

To calculate percentage change in Excel, subtract the old value from the new one, then divide by the old value: =(B2-A2)/A2. This is the percentage increase formula Excel users reach for first. For 1,800 rising to 2,142 the result is 0.19, shown as 19%.

The same percentage change formula Excel uses for a rise is the percentage decrease formula when the result is negative. 640 falling to 512 returns −20%. One formula covers both, so label the column "Change" rather than "Increase".

A shorter equivalent is =B2/A2-1. It gives the same answer for positive starting values, but it reverses the sign when the starting value is negative, which the next section covers. For a column of periods, month over month is =B3/B2-1 filled down from the second row. Values of 100, 110 and 99 return 10% and −10%.

Percent change with negative numbers and zero

The percent change formula Excel users type first, =(B2-A2)/A2, breaks on two inputs.

A zero starting value. =(B2-A2)/A2 returns #DIV/0!, because a change from zero has no percentage. Show a flag, not an empty cell: =IF(A2=0,"n/a",(B2-A2)/A2). IFERROR also works, but it blanks every error, including a #VALUE! from a number stored as text that you would want to see.

A negative starting value. A loss shrinking from −50 to −25 is an improvement, yet =(B2-A2)/A2 returns −50%, which reads as a decline. Dividing by the absolute starting value keeps the sign of the movement: =(B2-A2)/ABS(A2) returns +50%. Combined with the zero test: =IF(A2=0,"n/a",(B2-A2)/ABS(A2)). This is the same convention the percentage change calculator uses.

When the sign flips, as from −50 to 25, the absolute-start formula returns +150%, which is the movement (75) over the starting size (50). Many finance teams report "n/m" for not meaningful instead: =IF(A2=0,"n/a",IF(SIGN(A2)<>SIGN(B2),"n/m",(B2-A2)/ABS(A2))). Pick one rule and apply it to the whole sheet.

Percentage difference formula in Excel

How to calculate percentage difference Excel-style, when neither value is the baseline: divide the gap by the average of the two, =ABS(A2-B2)/AVERAGE(A2,B2). For 80 and 120 the gap is 40 and the average is 100, so the difference is 40%. The percentage difference formula Excel needs is the same for any pair, and the percentage difference calculator shows the same working.

Swapping the cells gives the same answer, which is the point. Percent change is not symmetrical: 80 to 120 is +50%, while 120 to 80 is −33.33%. Use change when there is a before and an after, and difference when the two values are peers, such as two suppliers' prices. For the percentage difference between two numbers Excel has no built-in function, so this formula is the standard route.

Add or subtract a percentage in Excel

Multiply by one plus or minus the rate. With the rate in B2 as a percentage, =A2*(1+B2) adds it and =A2*(1-B2) subtracts it. 80 with a 15% rate returns 92 and 68. You can type the rate into the formula, as in =A2*(1+15%), but a rate in its own cell turns a price change into one edit instead of a formula hunt.

Difference between two percentages in Excel

Two percentages are compared by subtraction, not by a percent change formula: =B2-A2. A rate moving from 4.0% to 4.5% returns 0.5%, which is 0.5 percentage points. The relative change in the same cells is =(B2-A2)/A2, which is 12.5%. They answer different questions, and mixing them up is the commonest reporting error.

To show the gap as a plain number of points, use =ROUND((B2-A2)*100,2) and label the column "pts". The percentage points guide explains when to quote each figure.

Percent error in Excel

Percent error compares a measured value with an accepted one: =ABS(A2-B2)/ABS(B2), with the measurement in A2 and the accepted value in B2. A reading of 9.6 against an accepted 9.8 returns 2.04%. The percent error calculator shows the working and handles several readings at once.

Percentages in Google Sheets

Every division and subtraction formula above works unchanged in Google Sheets, and how to calculate difference of two percentages in Sheets is the same =B2-A2. The differences are small. Format a cell with Format, Number, Percent, or press Ctrl+Shift+5 on Windows. To fill a whole column from one cell, wrap the change formula in ARRAYFORMULA, with a guard for blank rows, as shown in the code section. The division form of percent of total works in both tools, so it is the safer choice for a file that moves between them.

Excel percentage formula examples

All five on one sheet

Type each row into row 2 of a blank sheet and apply the Percentage format to column C.

Excel percentage formula code

Safe change formula, with the zero test and the absolute starting value:

=IF(A2=0,"n/a",(B2-A2)/ABS(A2))

The same rule with "n/m" for a sign flip:

=IF(A2=0,"n/a",IF(SIGN(A2)<>SIGN(B2),"n/m",(B2-A2)/ABS(A2)))

Google Sheets, filling a column from one cell and skipping blank rows:

=ARRAYFORMULA(IF(A2:A="","",IF(A2:A=0,"n/a",(B2:B-A2:A)/ABS(A2:A))))

The same maths outside a spreadsheet, in pandas:

1import pandas as pd2​3​4def pct_change_abs(s: pd.Series) -> pd.Series:5    change = s.diff() / s.shift().abs()6    return change.where(s.shift() != 0)

Series.pct_change() gives −50% for a move from −50 to −25, the same sign problem as the plain Excel formula, and returns inf when the previous value is zero. pct_change_abs returns +50% and leaves the zero case empty.

Spreadsheet safety

The formulas on this page are plain arithmetic. None uses macros, external workbook links or add-ins, and you can copy them as text. Values you type into your own sheet stay in your file.

Three safety points matter when percentages come from other people's data:

• CSV and formula injection. A cell that starts with =, +, - or @ is read as a formula when a CSV opens in Excel. A genuine percentage such as −5% starts with a minus sign, so a naive filter either blocks real data or lets a crafted cell through. Sanitise by prefixing the cell with a single quote or a tab when you generate files for others, and open untrusted CSVs through the import wizard with the column set to Text.
• Numbers stored as text. An imported "19%" that is left-aligned is text and does not calculate. =VALUE("19%") returns 0.19, or use Text to Columns.
• External links. A formula pasted from an unknown source that contains a workbook path in square brackets reads from another file. Excel prompts before updating links, so decline unless you trust the source.

Common Excel percentage mistakes

Percentage change vs difference vs points vs percent of total

Change needs a before and an after. Difference treats two values as equals. Points compare two percentages directly, and percent of total compares a part with its whole.