Calculate Percentage Change in Excel (% Increase and Decrease Formula)Explained with Screenshots and More
Excel is one of the most frequently used programs for performing calculations on data.
From performing accounting calculations to tracking changes in sales and inventory between periods.
No matter the purpose, Excel provides a valuable tool for analyzing and tracking changes in data.
Many of these important calculations can be performed through Excel’s built-in functions, and others can be performed through user-inputted formulas.
Both of these tools can be incredibly powerful for accountants and business owners.
In order to calculate percentage change, we will need to make use of the latter tools and enter our own formula to determine the change between two cells.
Let’s get started and learn how we can do this in Excel.
What Is Percentage Change (Increase & Decrease)
Percentage change refers to the proportional change between two distinct values, including an original value and a second value.
A percentage change formula is a tool that is used to determine how much change occurred between these two values in percent.
For example, a business owner may want to calculate the difference in value between sales during the last accounting period and sales in the current period.
If last year the sales were $10,000 and in the current period they were $11,000, this means an increase in value, but by what percent?
We could calculate this manually by dividing the new value by the old one and multiplying it by 100, but this could prove cumbersome when performing this action for many categories or periods of sales. Instead, we can use Excel formulas to simplify the process.
How To Calculate Percentage Increase or Decrease in Excel
The formulas used for calculating percentage change in Excel are similar to those we would use for doing the job manually.
For example:
=(New Value-Old Value)/Old Value
As you can see, this is similar to how we would calculate percentage change manually.
To use this formula, you would enter the cell references for the cells containing your old and new values in the formula and hit “Enter.”
This would likely provide a decimal value that is positive if the change was an increase or negative if it was a decrease.
This will also likely be a decimal value which we would need to change into a percentage.
Fortunately, Excel provides us a way to apply this to a larger number of cells and change it into a percentage value easily.
Simply follow these steps.
- In an empty cell next to your two columns of values, enter the formula “=(New Value-Old Value)/Old Value” with the appropriate cell references.
- Press “Enter,” and Excel should display the result in a decimal value.
- Ensure the cell containing the decimal value is active and select “%” from the “Numbers” group under the “Home” tab. This will convert the decimal value into a percentage. You can add or remove decimals by using the increase and decrease decimal icons in the same group to the right of the percentage icon.
- Now drag the “Fill Handle” on the bottom right corner of the cell containing the calculated value and drag it down to instruct Excel to repeat the same steps for our remaining values. This will generate the percentage change for all of the cells.
This will work in most circumstances to give an accurate percentage change and is easy to apply.
However, there is one exception.
In cases where the old value is negative, and the new value is positive, we would be given a negative percentage change because the denominator in our formula is negative even though the change has been positive.
Fortunately, this is easily solved by making one change to our formula and making the denominator positive.
Here is the formula to use in cases where negative values may be involved.
=(New Value-Old Value)/ ABS(Old Value)
By entering ABS before the cell reference for our old value, it will convert it into an absolute value changing any negative values into positive ones.
This will ensure that our denominator is positive, preventing misleading results.
Like before, we can simply drag the fill handle down to extend it to the remaining rows.
Calculating the Result of a Percentage Change in Excel
In some cases, you may have a value and a percentage change and want to know what the resulting sum would be.
Fortunately, Excel makes this easy to calculate.
Simply enter the following formula with the appropriate cell references.
=Old Value*(1+Cell Containing % Change)
By entering this into an empty cell next to our column with the appropriate cell references, we can easily calculate the results of the first row.
Then like above, we can use the “Fill Handle” on the bottom-right corner of the active cell to drag down and calculate the same value for the remaining rows in our columns.
This will work for both percentage increases and decreases.
Similarly, if you wish to increase the old values by a specific percentage instead, we can easily substitute this into our formula.
The formula for this would be:
=Old Value*(1+Specific %)
After calculating this for the first value, we can extend this to an entire column of cells simply by dragging the “Fill Handle” from the cell containing our first result.
This same formula can also be used to decrease a value by a particular percent as well.
To do this, we would use the formula:
=Old Value*(1-Specific %)
After hitting “Enter,” you can extend it to the entire column by dragging down the “Fill Handle.”
Conclusion
Now you know how to calculate percentage change in Excel, no matter whether the values are positive or negative.
You have also seen how you can take a value and multiply it by a percentage change to find the result.
Armed with this knowledge, you can easily use Excel to perform a wide range of operations for business analytics and many other purposes.