This tutorial shows how to make an Excel formula for percent increase or decrease and use it with both positive and negative numbers.
In Microsoft Excel, there are 6 different functions for calculating variance. However, none of them is suitable for calculating the percent difference between two cells. The inbuilt functions are designed to find variance in the classical sense, i.e. how far a set of values are spread out from their average. A percent variance is something different. In this article, you will find the right formula for calculating percent change in Excel.
As an Excel analyst, quickly calculating percentage changes between values is a crucial skill for insightful data analysis Whether you’re analyzing sales trends, investment returns, website traffic or any other data, percentage change reveals key insights
In this comprehensive guide, I’ll show you how to master percentage change calculations in Excel using simple formulas and functions. You’ll learn:
- The standard percentage change formula and how to apply it
- Easy shortcuts to format percentages with the ribbon
- How to calculate increases, decreases and zero/negative values
- Techniques to update columns with percentage changes
- Solutions when your values or formulas change
- Ideas to customize and format your analysis
Follow along and become an Excel percentage change pro!
Overview of Calculating Percentage Change in Excel
First let’s review the basics of percentage change
-
Percentage change compares two values, like sales in January vs February.
-
It can be an increase if the new value is higher or a decrease if lower.
-
The standard formula is (New – Original) / Original.
-
Multiply the result by 100% to convert to a percentage.
In Excel, you don’t need to actually multiply by 100. Just format the decimal result as a percentage using Excel’s Percentage Number Format button.
Now let’s look at some examples of calculating percentage changes in Excel.
Calculate Percentage Increase Between Two Values
To calculate percentage increase in Excel:
-
Enter the original and new values in cells A1 and B1.
-
In C1, use the formula =(B1-A1)/A1.
-
Select C1, click the Percentage Number Format button.
That’s it! This displays the percentage change from the original to new value. Easy as that.
Calculate Percentage Decrease Between Two Values
Calculating percentage decrease works the same:
-
Enter original and new values in A1 and B1.
-
In C1, use the formula =(B1-A1)/A1.
-
Format as Percentage.
Since the new value is lower, it will show the percentage decrease. No extra steps needed.
Calculate Value After a Percentage Change
You can also calculate the new value after applying a percentage change:
-
Enter original value in A1 and percentage change in B1.
-
In C1, use the formula =A1*(1+B1).
-
Copy down column C for multiple values.
The 1+ in the formula accounts for the percentage change. This works for both increases and decreases.
Increase or Decrease a Column by Percentage
To quickly increase or decrease an entire column by a set percentage:
-
Enter your starting values in column A.
-
In B1, enter the percentage amount (like 5% for 5%).
-
In B2, use the formula =A2*(1+B1).
-
Copy down column B.
Now you can simply change the percentage in B1 to update the entire column.
Calculate Percentage Change with Zero Values
When either the original or new value is zero, regular percent change formulas won’t work.
The common solution is to assume the percentage change is 100% in these cases.
So if your original value is 0 and new is 100, the percentage change is 100%.
Calculate Percent Change with Negative Values
To calculate percent change between negative values:
-
Use the regular formula if both values are negative.
-
If the new value is negative, also use the regular formula.
-
If original is negative but new is positive, use =(B1-A1)/ABS(A1) to make the denominator positive.
This works for all negative value scenarios.
Customize and Format Percentage Changes
To customize your Excel percentage change analysis:
-
Add decimal places with Ribbon buttons
-
Hide % symbol with custom number format
-
Color code increases/decreases with conditional formatting
-
Insert charts to visualize the changes
How to calculate percent change in Excel
To find the percentage difference between two numbers in Excel, you can use either of the above formulas. Lets say you have the estimated sales in column B and actual sales in column C. Assuming the estimated number is the “baseline” value and actual is the “new” value, the formulas take this shape:
The above formulas compare the numbers in row 3. To calculate the percent of change in the entire column, this is what you need to do:
- Enter the percent difference formula in any blank cell in row 3, say in D3 or E3.
- With the formula cell selected, click the Percent Style button on the ribbon or press the Ctrl + Shift + % shortcut. This will convert the returned decimal number to percentage.
- Drag the formula down across as many rows as needed.
After copying down the formula, youll get a percent change column from your data. 
How Excel percent change formula works
When doing the calculations manually, you would take an old (original) value and a new value, find the difference between them and divide it by the original value. To get the result as percentage, you would multiply it by 100.
For example, if the initial value is 120 and the new value is 150, the percent difference can be calculated in this way:
Applying the Percentage number format in Excel displays a decimal number as percentage automatically, therefore the *100 part is omitted.
Excel percent change divide by zero error (#DIV/0)
If your data set contains zero values, you are likely to run into a divide by zero error (#DIV/0!) when calculating percentage change in Excel because you cannot divide a number by zero in math. The IFERROR function can help to overcome this problem. Depending on your expectations for the final result, use one of the following solutions.
How to Calculate Percent Change in Excel
How to calculate percentage change in Excel?
Percentage change is usually calculated between two values, where one value is the old or initial one and the other is the new or final value. There is one general formula to calculate the percentage change: However, you don’t need to multiply by 100% in Excel, as you can use the Percentage number format. Another simpler version of the formula is:
How do I calculate percentage increase in Excel?
To calculate the percentage increase: Select a blank cell. D7 is the sales for Apple in February and C7 is sales in January. Use AutoFill to copy the formula to the cells below. Select the formula output cell range. Go to the Home tab > Number group > Percent Style. Percentage change outputs will appear accordingly.
How to calculate percentage decrease in Excel?
Use AutoFill to copy the formula to the cells below. Select the formula output cell range. Go to the Home tab > Number group > Percent Style. Percentage change outputs will appear accordingly. To calculate the percentage decrease: Select a blank cell. Here, D7 is the sales for Apple in February and C7 is the sales in January.
What is a percentage change formula?
Percent change formula calculates how much something changes between two periods percentage-wise. For example, you can calculate variance between sales in this year and last year, between a forecast and observed temperature, between a budgeted cost and the real one.
