If you’re new to Excel, it’s only a matter of time before you encounter a problem that requires the use of percentages. Using percentages in Excel formulas isn’t very intuitive for new users, which can result in a lot of confusion and frustration.
Subtracting 10% from a number
Imagine that you have the following example workbook:
You want to subtract 10% from the number in cell A2 and place the result in cell B2. To do this, you’ll need to use a very simple Excel formula.
If you’re completely new to Excel, you can learn how to create simple formulas in our completely free Basic Skills course. I’d recommend starting there if formulas are completely new to you.
Many new users would try to use the following formula:
Unfortunately, this won’t have the effect that you would expect. Instead of subtracting 10% from the value of 83,279, Excel returns a percentage!
So why did this happen? The reason is that Excel ‘secretly’ converts all percentages into decimal values.
Percentages are actually decimal values
When you type 10% into Excel, Excel sees it as the value 0.1. Excel does this with all percentage values. 50% is 0.5, 75% is 0.75, and so on.
In the previous example, you were actually asking excel to subtract 0.1 from 83,279 instead of reducing the number by 10%. Excel saw your use of the % sign and tried to be helpful by formatting the result as a percentage, but in this case that just made things more confusing!
So, knowing how Excel treats percentages, how should I subtract 10%?
One solution is to multiply the number by 90%, with one of the following formulas:
Because of the way Excel handles percentages, it sees these formulas as exactly the same thing.
An alternative, but more long-winded calculation, would be to calculate 10% of the number and then subtract it from the original number, with one of these formulas:
This will have exactly the same result and, as in the previous example, both of these formulas mean exactly the same thing to Excel.