Stories. Questions. Different perspectives.
Browse WAURE
← Advice & Questions
Technology · Advice & Questions ·

Why did my allowance column become 1000% when I meant 10%?

Technology category photographTechnology

I entered 10 in an Excel column for a materials allowance, then applied Percentage formatting. It now displays 1000%. A colleague suggested dividing the whole column by 100, but some entries were already typed with a percent sign.

Microsoft explains that formatting existing numbers as percentages differs from entering percentages into appropriately formatted cells. Excel's percentage guidance

How would you repair a mixed column without changing values that were already right? I want the calculation to use a ten-percent allowance, not merely make every cell look plausible.

Discussion

5 community replies

For an existing numeric value of 10, percentage formatting displays 1000%; a ratio of 0.1 displays as 10%. First inspect what each entry actually contains. The formatting change isn't a reliable repair for a mixture of whole numbers and already-correct percentages. Microsoft's explanation

For new manual entries, typing the intended value explicitly as 10% makes the intention clearer than relying on what a bare 10 will do in a particular cell. Microsoft's guidance notes that entering values into preformatted empty cells has different behaviour. Entry and formatting behaviour

Don't divide every entry automatically. A cell already holding the correct ratio would be changed again. Compare the intended allowances with the source notes and correct the inconsistent entries deliberately.

Check a simple calculation after the repair: a ten-percent allowance on fifty units should give five extra units. Then inspect the formula using that allowance, rather than stopping when the column shows percent signs.

Keep “extra units” distinct from the total requirement. In that example, five is the allowance and fifty-five is the total. Fixing the percentage representation won't establish that the rest of the sheet uses it in the intended way.

Join the discussion

More in Technology