Excel percentage formula: percent of a total, percent change, and formatting as a percent

To find a percentage in Excel, divide the part by the total and format the result as a percent: =B2/B6 returns a decimal like 0.25, and on the Home tab the Number group’s Percent Style button turns that into 25%. Percentage change between two numbers is the same idea with a different formula, =(C2-B2)/B2, the new value minus the old over the old. Microsoft puts the arithmetic simply: “Percentages are calculated by using the equation amount / total = percentage.”

This page covers the everyday cases on one screen: a percentage of a total, percentage change, the percentage difference between two numbers, how the percent format actually works, and the trap where a cell shows 2500% instead of 25%.

How do I calculate a percentage of a total?

A percentage of a total is one number divided by the whole: part / total. Say four spending lines add up to a 1000 budget and you want each line as a share of it. Put the line amount over the total, =B2/B6, and Excel returns the fraction 0.25; format it as a percent and it reads 25%.

Notice Excel returns a decimal, not a percent, until you format it. That is deliberate: the value underneath is the fraction, and the percent format is a display layer on top, covered further down.

One thing to watch when you fill the formula down the column: the total has to stay put. Written as =B2/B6, the reference to B6 shifts as you copy, so lock it with a dollar-sign absolute reference, =B2/$B$6. The dollar sign and the F4 key that sets it are covered in cell references.

How do I calculate percentage change between two numbers?

Percentage change is how far a number moved from an old value to a new one: subtract the old from the new, then divide by the old. That is =(C2-B2)/B2, where B2 is the starting value and C2 the new one.

A rise is a positive percent and a fall is a negative one, so the drop from 400 to 300 in the second row reads as -25%. Microsoft’s own example works the same way, =(2500-2342)/2342, which comes out to about 6.75%.

The percentage difference between two numbers is that formula seen from another angle: how far one number sits from another, as a percent of the one you compare against. =(A-B)/B measures how much bigger or smaller A is than B. Whichever number you divide by is the baseline, and choosing it decides the answer.

How do I format a number as a percentage?

There are two ways, one quick and one with more control.

Quick: select the cells and, on the Home tab in the Number group, choose Percent Style, or press Ctrl+Shift+% on Windows. A value like 0.25 then shows as 25%.

For more control, open the Format Cells window (on the Home tab, the Number group’s dialog launcher, the small arrow next to Number; on a Mac, the Number Format arrow, then More Number Formats), select Percentage in the Category list, and set the Decimal places box. Two decimals show 0.2537 as 25.37%; zero decimals show it as 25%.

How many decimals you see is just formatting. The stored value never changes, only its display:

Changing the decimals changes only what you see. 0.2537 still calculates as 0.2537 while it shows 25%, so a rounded-looking figure keeps its full precision underneath. To round the stored number itself, use the ROUND function. The last row of the table is a different problem, and the next section is about it.

Why does my percentage show 2500%?

The percent format is a display layer: it shows the stored value multiplied by 100, followed by a percent sign. A cell holding 0.25 therefore shows 25%. The catch is applying it to a number that is already a whole percentage. As Microsoft’s documentation puts it: “If you apply the Percentage format to existing numbers in a workbook, Excel multiplies those numbers by 100 to convert them to percentages.” So a cell that already holds 25 displays 2500.00% the moment you format it.

The fix is to decide up front whether the cell stores a fraction or gets the format. Two clean ways:

• Compute the fraction with a formula. =B2/B6 returns 0.25, and Percent Style shows it as 25%.
• Format the empty cells as percent first, then type. Do that and Excel meets you halfway: a number of 1 or more is read as the percent you typed, and a number below 1 is multiplied by 100, so typing 25 or 0.25 both land on 25%.

A percentage can also read wrong for a reason that has nothing to do with the format: one of the numbers behind it is stored as text. A total built with SUM skips that value, so the shares stop adding up to 100, and a non-numeric text cell pulled into a formula can surface as a #VALUE! error instead. Converting those is its own task, covered in numbers stored as text.

The short version

• Percentage of a total: divide the part by the total, =B2/B6, then apply Percent Style, so 0.25 shows as 25%.
• Percentage change: =(C2-B2)/B2, the new value minus the old over the old; a fall shows as a negative percent.
• Percentage difference: the same formula, =(A-B)/B, with the baseline being whichever number you divide by.
• Format as a percent: Home > Number > Percent Style, or Ctrl+Shift+% on Windows; for decimals, Format Cells > Percentage and set Decimal places (0.00% shows 25.00%, 0% shows 25%).
• The value does not change: the percent format only affects the display, so 0.25 still calculates as 0.25.
• The 2500% trap: the format shows the stored value times 100, so store the fraction 0.25, not 25.
• To keep the total fixed when you fill the formula down, see cell references; if the shares will not total because a source number is stored as text, see numbers stored as text.