Excel Percentage Wrong? Why 5 Becomes 500% and the Fix

If your Excel percentage looks wrong, the cause is almost always the same: Excel stores percentages as decimals, so 5% is really 0.05 and 100% is really 1. When a cell that holds 5 gets the Percent format, Excel shows 500%. This guide explains that rule, then fixes the five most common wrong-percentage problems, from 500% values to formulas that return 0%, 100% or 2500%.

Why does 5 become 500% in Excel?#

Excel shows 500% because the Percent format multiplies the stored value by 100 for display. It doesn't change the number. It only changes how the number looks.

So if a column of rates was typed as 5, 12 and 20, and you then apply the Percent style, you get 500%, 1200% and 2000%. The numbers were never percentages to Excel. They were whole numbers.

There is one twist. In Windows Excel, if you format empty cells as Percent first and then type 5, Excel stores 0.05 and shows 5%. Microsoft calls this automatic percent entry, and you can switch it under File > Options > Advanced. That is why the same number can behave differently depending on whether you typed it before or after formatting. The guide to formatting numbers as currency and percent covers the formats in more detail.

How to fix percentages that show 500% instead of 5%#

To fix whole numbers that should be percentages, divide them by 100. You have three ways to do it.

1. Retype with the % sign. Typing 5% stores 0.05 and applies the Percent format at once. This is fine for a few cells.
2. Use a helper formula. If the rates are in B2:B9, type =B2/100 in an empty column, fill it down, then copy and paste the results back as values. The article on pasting values only shows that last step.
3. Divide in place with Paste Special. Type 100 in a spare cell and copy it. Select the rates, open Paste Special (Windows: Ctrl+Alt+V; Mac: ⌃⌘V), choose Divide and click OK. Every selected number is divided by 100. Delete the spare cell afterwards.

Then apply the Percent style. The shortcut is Ctrl+Shift+% on Windows and ⌃⇧% on a Mac.

If you prefer to keep the whole numbers, you can leave them as they are and divide inside each formula instead: =B2*(1-C2/100) takes a discount typed as 15 off the price in B2. That is the task in the exercise on this page.

Why does my percentage formula show 2500%?#

A formula shows 2500% when it multiplies by 100 and the cell uses the Percent format. The 100 is counted twice.

Say B2 holds 50 tasks done and C2 holds 200 tasks in total. These two formulas look similar but give different results:

Pick one approach. Either use =B2/C2 and the Percent format (the normal Excel way), or use =B2/C2*100 with a General format and add the word "percent" to the header. Don't mix them. The percentage formula guide and the article on the percentage of a total both use the first approach.

Why does my percentage show 0% or 100%?#

Often the formula is right and the display is rounding. The Percent style shows no decimal places, so:

• 0.004 shows as 0% (it is really 0.4%).
• 0.996 shows as 100% (it is really 99.6%).

Click the cell and look at the formula bar, or add decimal places with Home > Increase Decimal. If the value really is 0 or 1, check the formula:

• 0% often means the top of the division points at an empty cell or a number stored as text.
• 100% often means the formula divides a cell by itself, for example =B2/B2 after a fill went wrong.
• A #DIV/0! error means the bottom of the division is 0 or empty.

Why is my percent change wrong?#

The percent change formula is =(new-old)/old, and two small slips make it wrong. The first is missing brackets. Excel divides before it subtracts, so =C2-B2/B2 works out B2/B2 first and returns the wrong number. Write:

=(C2-B2)/B2

The second slip is dividing by the new value instead of the old one. If sales went from 80 in B2 to 100 in C2:

The full guide to the percent change formula also covers negative starting values and how to show increases and decreases clearly.

Why does adding 10% to a number give the wrong result?#

=A2+10% adds 0.1, not 10% of A2. Excel reads 10% as the number 0.1, so if A2 holds 200, the result is 200.1. To increase or decrease a number by a percentage, multiply:

=A2*(1+10%)
=A2*(1-10%)

With 200 in A2, the first formula returns 220 and the second returns 180. If the rate sits in a cell such as D1, write =A2*(1+D1), and lock it as $D$1 before you fill down.

A related trap: increasing by 10% and then decreasing by 10% does not get you back to the start. 200 becomes 220, and 220 minus 10% is 198.

What if my percentages are stored as text?#

Percentages imported from another system can arrive as text, such as "5%" or "0.05". Text sits on the left of the cell by default, and SUM and AVERAGE skip it, so totals come out too low or as 0.

• Check with =ISNUMBER(B2). FALSE means the cell is text.
• Convert one cell with =VALUE(B2). VALUE understands the % sign, so =VALUE("5%") returns 0.05.
• Convert a whole column with the green error triangle and Convert to Number, or with Data > Text to Columns > Finish.

The guide to numbers stored as text explains each method and why it happens.

Practise it#

The fastest way to stop percentage mistakes is to work a few by hand. Try the exercise on this page, then practise percent change in a live spreadsheet: you type the formula and it checks the result instantly. To get the Percent style and decimal shortcuts into your fingers, work through the formatting shortcuts lesson.