Percentage Change Excel Formula
Arithmetic calculations can be done via excel formulas and one of such calculation is “Percent Change” from one value to another. For example, if you want to find the percentage change between your first drawn salary and current salary that is will be calculated in terms of percentage change, so this is possible in formulas in excel. In this article, we will discuss how to calculate the percentage change formula in excel.
What is the Percentage Change in Excel?
A Percentage Change or Percentage Variance is the changes between any two values over a period of time. A typical example of a percentage change example is “variance between last year sales and this year sales”.
For example, last year’s sale was USD 400000 and this year sales are 450000, so we can tell the difference is USD 50000 increase in revenue but we can tell this in percentage-wise as well.
So to calculate the percentage change between two numbers is below.
Or else we can also use the below formula as well.
In the above example, previous year sales were USD 400000 and current year sales are USD 450000 so let’s apply these numbers to the formula.
(Current Value – Previous Value) / Previous Value
= (450000 – 400000) / 400000
= 12.5 % increase in Revenue.
Now we will see how we can apply the same formula in excel.
Below is the Quarterly Sales data of the previous year and current year of an ABC Company Pvt Ltd.,
From this data, we need to find out what is the variance % from the previous year quarter to this year quarter.
Let’s use our formula as we have learned above. This time we are going to use cell references instead of entering the numbers directly to the formula itself.
Copy the above table to excel sheet.
Now put an equal sign in the cell D2, because all the formulas in excel must start with an equal sign only.
Now open bracket and our first formula should be Current Value and in this example, the current value is C2 cell value.
Then put minus (-) sign and choose Previous Year Value i.e. B2 cell.
Close the bracket and put it divided by a symbol by entering forward slash.
Now again select Previous Year Value.
Hit enter key to get the result of percentage change from the previous year to this year.
We got only decimal values, for this we need to apply the percentage format. To apply the percentage format select the cell and hit percentage (%) symbol.
You can also press Ctrl + Shift + % as a shortcut key to apply percentage change. As soon as we apply percentage format we can see the percent change values.
First, the percent change is 33.59% i.e. current revenue is 21611 and the previous revenue is 16177, so revenue has been increased from previous to this current year so it is increased by 33.59%.
Similarly, the second value percent change is -1.25% i.e. revenue is decreasing.
To apply the red color format to a negative percent change apply below formatting code as shown below.
Below are the salary details of employees when they joined and current salary.
To find out the variance percent change between these two numbers lets use the same formula as above.
As we can see in cell D6 we got the error value this is because whenever we divide the number by zero we get division error value as #DIV/0!.
So to get rid of this we need to use the IFERROR function in excel.
The first argument of this function is Value so for this apply percent change formula as usual.
The second argument is value if error, this says what should be the value if the supplied calculation returns as the error value, so we need the result to be 0%.
Now see we got rid of error value because of the IFERROR function.
Below is the sales employee’s revenue generation comparison from last month to this month.
This time apply the second formula we have learned i.e. Current Value / Previous Value – 1
So only two employees have better performance than the previous month i.e. D3 & D5 cell.
Things to Remember
- Always use cell references to make the formula dynamic.
- When you divide the value by zero we get division error of #DIV/0!.
- Use the IFERROR function to get rid of error values.
This has been a guide to Percentage Change Formula in Excel. Here we learn how to calculate percentage change formula in excel along with examples and downloadable excel template. You may learn more about excel from the following articles –