How to Calculate Percentages in Excel

Ever Googled“how to calculate percentages in Excel”? You’re not alone. We all learned percentages in school — but Excel adds that extra layer of confusion that sends even experienced users hunting for answers.

In this post (and video), I’ll show you real-world Excel percentage tricks that are simple, practical, and ready to use at work now. Scroll down for step-by-step explanations, example formulas, and common pitfalls to avoid.

Watch the Video

Get the Practice File and Cheat Sheet

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: 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