ExcelInsider Team
In any dataset calculating the percentage increase or decrease helps us understand how a value has changed over time, if it’s revenue growth, cost savings, or inventory reduction. This is mostly useful for performance tracking, trends analyzing, or making data based decisions. This is applicable when we are comparing two values, such as monthly sales or yearly expenses.
To Calculate Percentage Increase or Decrease in Excel, follow these steps:
➤ Identify the old value (original number) and the new value (updated number)
➤ Subtract the old value from the new value.
➤ Divide the result by the old value.
➤ Format the result as a percentage.
In this article, we’ll guide you through 5 methods on how to calculate percentage increase and decrease using basic formulas, Excel functions along with best practices for handling zero or negative values.
What Is Percentage Change?
Percentage change (it may be an increase in value or decrease in value) is the ratio of the difference between two values to the original value which is expressed as a percentage. It basically shows how much a value has increased or decreased relative to its starting point. For example, If sales increased from $1,000 to $1,200, Percentage Change will be = (1,200 – 1,000) / 1,000 = 0.20 or 20%
Using Basic Percentage Change Formula
The Basic Percentage Change method will calculate how much a value has increased or decreased in percentage terms. It’s suitable for comparing two data points over time. For example, monthly sales. In this case, we’re measuring the change from Last Month Sales to This Month Sales in an Excel worksheet.
Steps:
➤ Open your own Excel worksheet with data or navigate to sheet 1 from our demo excel file.
➤ Click on cell D2, where you want the first result of percentage change (for Laptop) to appear.
➤ Enter the following formula:
=(C2 – B2) / B2
Then press Enter. This calculates the percentage change from last month to this month for the Laptop.
➤ Format the result in D2 as a percentage:
- Click on D2
- Go to the Home tab
- Click the % symbol in the Number group
➤ Drag the fill handle (small square at the bottom-right corner of D2) down to fill the formula through D11.
Getting Absolute % Change
The Absolute Percentage Change method calculates the magnitude of change between two values without considering whether it increased or decreased. This is very useful when you’re only interested in how much change occurred, not the direction. In this dataset, it shows how much the sales changed from last month to this month for each product.
Steps:
➤ Open your own Excel worksheet with data or navigate to sheet 2 from our demo excel file. Click on cell D2 to enter the formula for the Laptop’s sales change. Enter the following formula:
=ABS((C2 – B2) / B2)
➤ Then press Enter. This calculates the absolute percentage change in sales for the first row.
➤ Use the fill handle to drag the formula down from D2 to D11.
Handling Division by Zero
When calculating percentage change, division by zero errors occur if the old value is zero. This method uses a conditional formula to check for zero before performing the calculation. In this dataset, some products like “Smartphone” and “Printer” have $0 in the “Old Sales” column, so this method ensures the calculation returns “N/A” instead of an error.
Steps:
➤ Open your own Excel worksheet with data or navigate to sheet 3 from our demo excel file. Click on cell D2 to begin entering your formula for the first product (Laptop).
Enter the following formula in D2:
=IF(B2=0, “N/A”, (C2 – B2) / B2)
Then press Enter.
This formula checks if the Old Sales (B2) is zero. If it is, it returns “N/A“; otherwise, it calculates the percentage change.
➤ Use the fill handle to drag the formula from D2 to D11. It will apply the formula across the column.
Dealing with Negative Values in Base
When calculating percentage change, negative starting values (like losses or refunds) can distort results. This method adjusts the formula to use the absolute value of the base (Old) when both Old and New values are negative, providing more intuitive results in financial or operational datasets such as sales, profits, and refunds.
Steps:
➤ Open your own Excel worksheet with data or navigate to sheet 7 from our demo excel file. Click on cell D2 to start entering the custom formula that handles negative base values correctly.
➤ Enter the formula:
=IF(AND(B2<0,C2<0), (C2-B2)/ABS(B2), (C2-B2)/B2)
➤ Press Enter to apply the formula.
➤ Use the fill handle to copy the formula from D2 down to D11.
Getting Percentage Difference from Average
Percentage Difference from Average can calculate how much each item’s value deviates from the average of all items. It is then expressed as a percentage. It’s useful for identifying outliers or gauging performance that is relative to the average. In this dataset, we’re measuring how each product’s value compares to the average of all values in column B.
Steps:
➤ Open your own Excel worksheet with data or navigate to sheet 8 from our demo excel file. Click on cell C2 to begin entering the formula that compares each value to the column’s average.
➤ Enter the formula:
=(B2 – AVERAGE(B$2:B$11)) / AVERAGE(B$2:B$11)
➤ Then press Enter.
➤ Copy the formula from C2 down to C11 using the fill handle.
Frequently Asked Questions (FAQs)
What is the formula for percent difference in Excel?
The formula for percent difference in Excel is:
=ABS((New Value – Old Value) / ((New Value + Old Value) / 2)) * 100
This formula will calculate the absolute difference between two values. Then it will divide it by their average, and multiply by 100. This will show the result as a percentage.
What is the formula for percentage increase & Decrease?
To calculate the percentage increase in Excel you can use this general formula-
=(New Value – Old Value) / Old Value * 100
This formula finds the difference between the new and old values then divides it by the old value, and later multiplied by 100 to get the percentage increase.
The formula for percentage decrease in Excel is the same as for percentage increase:
=(New Value – Old Value) / Old Value * 100
How to calculate a percentage raise in Excel?
To calculate a percentage raise use this formula-
=Old Value * (1 + Percentage Raise)
For example, a 10% raise on a $1,000 salary would be:
=1000 * (1 + 10%)
This formula adds the raise percentage to 1, then multiplies the old value to find the new value.
Concluding Words
We’ve covered 8 methods on how to calculate percentage increase and decrease in Excel. We have shown methods on how to handle increases, decreases, and more complex scenarios like negative values and average comparisons.
You can use these formulas and functions like IF, AVERAGE, and ABS to easily calculate percentage increase and decrease for financial analysis, sales tracking, or performance metrics etc.
You should keep experimenting with these formulas, and soon you’ll be calculating percentage changes like a pro. Let me know your feedback in the comment section.
