1. Percentage Change
Let’s say your boss drops a table of sales data on your desk and says, “What’s the percentage change from last year to this year?”
Formula:
= This Year / Last Year – 1
This works for any change from one number to another. A more generic way to remember it is:
= New Number / Old Number -1
Because Excel calculates division before subtraction, no parentheses are needed.
Tip to remember: The new value goes on top — it's the numerator.
Caution: If the values cross zero (e.g., from a loss one year to a profit the next), this formula may show a misleading result. We’ll cover how to handle that later.
2. Percentage Increase & Decrease
Imagine this: your manager says tariffs just kicked in, and
you need to increase all prices by 20%.
2.1 Percentage Increase
Formula:
= Original Price * (1 + % Increase)
For a 20% increase:
= Original Price * 1.2
= 100 * 1.2
= 120
That’s 100% + 20% → or 1 + 0.2.
2.2 Percentage Decrease
Now your
manager changes course and says, “we can’t pass on the full impact of the tariff to our customers. Let’s reduce prices by 10%.”
Formula:
= Current Price * (1 - % Discount)
= 120 * 0.9
= 108
A 10% discount = multiply by 0.9 (i.e.,100% - 10% = 90%).
Important: You’re reducing the new price (after the 20%
increase), not the original price.
3. Reverse Percentage Tricks
3.1 Reverse % Decrease
Scenario A: A product’s price dropped 20% and now costs $80. Your boss asks, “what was the original price?”.
Formula:
= New Price / (1 - % Decrease)
In this case:
= 80 / 0.8 = 100
3.2 Reverse % Increase
Scenario B: A product’s price increased 10% and now costs $110. What was the original price?
Formula:
= New Price / (1 + % Increase)
Same logic works for removing sales tax too.
Scenario C: Excluding Sales Tax (e.g., 10% GST)
If the gross (tax-inclusive) price is $110 and tax is 10%, to get the net price:
Formula:
= Gross Price / (1 + % Tax Rate)
= $110 / 1.1
= $100
3.3 Calculate Tax
Amount Directly
Let’s say you need to know the tax component included in the gross price. Rather than doing a two-step subtraction, you can extract the tax amount with one formula.
Option 1 - Divide by divisor:
= Gross / ((1 + Tax Rate) / Tax Rate)
Option 2 - Multiply instead:
= Gross * (Tax Rate / (1 + Tax Rate))
Bonus Shortcut: Pre-calculate the divisor
If your tax rate is fixed (say 10%), pre-calculate the divisor:
= Tax Rate / (1 + Tax Rate)
= 0.1 / 1.1
= ~0.0909
Or invert:
= (1 + Tax Rate) / Tax Rate
= 1.1 / 0.1
= 11
Then simply divide the gross by 11 to get the tax amount:
= 110 / 11
= 10
Common Percentage Mistakes (And How to Fix Them)
Let’s cover some classic Excel percentage mistakes that even experienced users fall into.
Mistake 1: Mixing Up Markup and Margin
Markup is based on cost:
= (Selling Price - Cost) / Cost
Margin is based on selling price:
= (Selling Price - Cost) / Selling Price
Remember:
Markup is on the cost. Margin is in the sale.
Markup = Profit ÷ Cost
Margin = Profit ÷ Selling Price
Another way I like to remember it is margin is short for ‘Profit Margin’, which always relates to sales. Therefore, selling price must be the denominator for Margin,
which leaves cost as the denominator for Markup.
Mistake 2: Incorrect % Variance to Budget
If you're reporting monthly accounts and need to explain variance to budget, first, you need to remember that the variances are calculated differently for Revenue vs Expenses:
- Revenue variance: =Actual - Budget
- Expense variance: =Budget - Actual
Then to calculate the % Variance to Budget:
=Variance / ABS(Budget)
Use the Absolute function (ABS) when the budget is negative (e.g., net loss, or refund on expenses), to avoid flipping the meaning. ABS simply converts negative values to positive and leaves positive values as is.
For example, the last row below would return a positive 50% variance without ABS, which would be incorrect as we budgeted to receive a $200 refund of expenses, but only received a refund of $100, i.e. 50% below budget:
Note: you can use ABS for all variance calculations to make it easy to copy down a column as required.
Mistake 3: Growth from Zero
If last year’s value was 0, you can’t calculate % change — it’s mathematically undefined.
Handle with the IFERROR function:
= IFERROR(New / Old - 1, "No Prior Year Sales")
Don’t assume it’s a 100% increase — it's infinite growth from nothing and therefore cannot be 100%.
Mistake 4: Change from Negative to Positive
Example: last year was -200 (a loss), and this year is +300 (a profit).
Formula that fails:
= (This Year - Last Year) / Last Year
= (300 - -200) / -200
Gives a negative 250% change.
Correct formula:
= (This Year - Last Year) / ABS(Last Year)
= (300 - -200) / 200
= 250%
Don’t forget to handle #DIV!/0 errors with IFERROR:
= IFERROR((New - Old)/ABS(Old), "No Prior Year Value")
Want to Take Your Excel Skills Even Further?
If you want to avoid percentage headaches altogether and level up your Excel skills, check out our
Excel Expert course. It’s perfect if you’re tired of Googling formulas every time you open a spreadsheet.
- Build
reports faster
- Avoid costly formula errors
- Automate tasks with confidence
Learn more about the Excel Expert course here
Learn With Us
