Excel percentage format wrong? How to fix it

Why 15 becomes 1500%

Percent format does not add a percent sign to the number you see. It multiplies the stored value by 100 for display and then adds the sign. That is correct for 0.15, which shows as 15%, and wrong for 15, which shows as 1500%.

It happens when numbers are typed or imported as whole percents and formatted afterwards. In this price list the discounts in B2, B3, B5 and B7 were typed as 15, 20, 25 and 30, then the column was formatted as Percent. The socks and the headlamp were pasted from a supplier's file as 0.1 and 0.05, so they show correctly as 10% and 5%. One column, two kinds of value, and that mix is what makes the fix tricky.

Click the cell and look at the formula bar: it shows 1500% for a cell that holds 15, and 15% for one that holds 0.15. The format is never the problem. The stored number is.

Fix the values: divide by 100

If every value in the column is a whole percent, Paste Special can divide them all in place, with no helper column. Format the result as Percent afterwards and 15 becomes 0.15, shown as 15%.

If the column is mixed, as here, dividing everything breaks the rows that were already right: 0.1 would become 0.001, or 0.1%. Use a helper column that only divides values above 1, then paste it back as values. That test is safe for discounts, tax rates and completion rates, which are never above 100%. Don't use it on growth rates, where 150% is a real value.

1. Type 100 in an empty cell and copy it (Ctrl+C).
2. Select only the cells that hold whole percents, such as B2:B3, B5 and B7 (hold Ctrl to pick separate cells).
3. Go to Home > Paste > Paste Special, choose Divide under Operation and click OK.
4. Format the column as Percent (Ctrl+Shift+%) and delete the 100.

Format first, then type (and the under-1 trap)

When a cell is already formatted as Percent, Excel assumes you are typing a percent: type 15 and it stores 0.15. This is the automatic percent entry option (File > Options > Advanced > Enable automatic percent entry), and it is on by default in Excel for Windows.

The trap is with small numbers. In a cell formatted as Percent before you type, numbers below 1 are also multiplied by 100, so typing 0.5 to mean half a percent gives 50%, and typing 0.1 gives 10%. Microsoft's own example is that typing 10 or 0.1 both give 10%.

The safest habit, whatever the format: type the percent sign. 15% and 0.5% are read correctly in any cell and are stored as 0.15 and 0.005. Typing the sign into an unformatted cell also applies Percent format for you.

Percent math that goes wrong

A cell that holds 15 instead of 0.15 doesn't just look wrong; every formula using it is off by a factor of 100. The sale price formula =C2*(1-B2) means price times 85% when B2 holds 0.15. With 15 in B2 it means price times minus 14.

A second classic is typing a percent straight into a subtraction. =C2-15% does not take 15% off the price. Excel reads 15% as 0.15, so it subtracts 15 cents. To take a percentage off, multiply: =C2*(1-15%), which gives $76.49 for the $89.99 shoes. To add one, =C2*(1+15%).

Multiplied by 100 and formatted as a percent

The opposite mistake is common in formulas copied from a calculator habit: part divided by whole, times 100. That gives 42.2 for the shoes' margin, and Percent format then shows 4222%.

Pick one or the other. Either drop the *100 and format as Percent (the better choice, because the cell then works in other formulas as a real percentage), or keep *100 and leave the cell as a plain number with a "%" in the column header.

Rounded to 0% or 100%

Percent Style (Ctrl+Shift+%) shows no decimal places, so the display rounds. 249 of 250 tasks done is 99.6%, which shows as 100%; a 0.4% error rate shows as 0%. The value underneath is exact, but on a report "100%" can be a lie.

Add decimals with Home > Increase Decimal, or use a format like 0.0%. Formatting only changes the display; if the rounded figure must also be used in later math, wrap the formula in ROUND, as in =ROUND(249/250,3), which stores 0.996.

Percentages stored as text are the other display problem: they sit left-aligned, often with a green triangle, and SUM skips them. Converting with =VALUE(A2) or Data > Text to Columns turns "15%" text into the number 0.15.