Use Excel to figure out the percentage discount or increase, for example the difference between the original and sale prices. We’ll show you the Excel formula and some practical tricks beyond the official answer.
Percentage discount formula
The basic formula is:
(Original – Sale) – to figure out the difference between old and new amount
(Original – Sale)/Original – divide by the original amount to get the percentage.
We’ll use prices for our examples, but your values can be for any number or quantity change.
In Excel it looks like this:
B1 – Original Price
B2 – Sale Price
The Excel formula is =(B1-B2)/B1
The brackets are important, otherwise Excel will do the division first and give a meaningless result. See Excel’s order of calculation is not BODMAS.
Here’s some sample discount calculations but, as you can see, there’s a problem. The discounts are fractions, not percentages.
And in Column F we put in a price increase which is a negative number, we’ll get back to that in a moment.
Fractions to Percentage in Excel
Excel makes it easy to convert a fraction into a percentage, just go to Home | Number | and click the % button. Or choose ‘Percentage’ from the pull-down menu.
(Another way is multiplying the result by 100 =((B1-B2)/B1)*100 then adding a % sign with some custom Excel formatting. Trust us, using Excel’s inbuilt Percentage format is LOT easier)
Change the decimal places
Change the number of decimal places with the Increase or Decrease Decimals buttons just to the right of the % button. We’ve decreased the decimal places to none – whole numbers only.
Mixed increase and decrease percentages
What we’ve told you so far is the standard answer to the ‘percentage discount’ question but it’s not always the right or complete solution.
Have a look at the % Discount row – the discounted prices are positive numbers but Col F has a price increase that shows a negative percentage. That’s correct arithmetic but doesn’t make sense when there’s a mix of both value increases or decreases. You want discounts to be negative numbers and increases to be positive numbers.
Fix that with a tiny change in the formula, add a minus sign.
-(Original – Sale)/Original
Or
=-(B1-B2)/B1
Now the lower prices have negative numbers and the price rise is a positive percentage.
We’ve made that very clear, using Excel’s Conditional Formatting to change the text color depending on the value.
Three ways to understand part of an Excel formula
Excel’s order of calculation is not BODMAS
Better Excel formula converts days into Years, Months, Days
Convert Excel formula into fixed values or text
