Excel Percentage Hacks: Calculate Percentages Like a Pro

Excel Percentage Fundamentals

• 💡 Percentage change is calculated as (New Number / Old Number) - 1, comparing the most recent year to the previous.
• ⚠️ Be cautious with this formula if values cross zero, as it can yield misleading results.

Increasing and Decreasing Values

• 🚀 To increase a price by a percentage, use the formula: Original Price * (1 + Percentage Increase).
• 📉 To decrease a price by a percentage, use: Current Price * (1 - Percentage Decrease).
• 🎯 Remember that decreasing a price by 10% is not the same as reducing the original base price by 10%.

Reverse Percentage Calculations

• 💰 To find the original price before a decrease, use: New Price / (1 - Decrease Percentage).
• 📈 To find the pre-tax price, use: Gross Price / (1 + Tax Rate).
• 🧾 To calculate the tax amount directly from the gross price, use: (Gross Price * Tax Rate) / (1 + Tax Rate) or Gross Price / ((1 + Tax Rate) / Tax Rate).
• 🧩 A shortcut for fixed tax rates is to pre-calculate the divisor: (1 + Tax Rate) / Tax Rate.

Common Percentage Mistakes

• ❓ Markup vs. Margin: Markup is calculated on cost (Selling Price - Cost) / Cost, while margin is on sales (Selling Price - Cost) / Selling Price.
• 📊 Variance to Budget: For revenue, variance is Actual - Budget; for expenses, it's Budget - Actual. Wrap the denominator in the absolute function to avoid misleading results with negative budgets.
• 🚫 Ignoring Zero Values: When calculating percentage growth from zero, use the IFERROR function to display a clear explanation instead of a DIV/0 error.
• 🔄 Year-on-Year Change Crossing Zero: To correctly calculate changes from loss to profit, divide the absolute value of the base year: (This Year - Last Year) / ABS(Last Year). Combine with IFERROR for robustness.