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 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’s quarter.
Let’s use our formula, as we have learned above. This time we are going to use cell referencesUse Cell ReferencesCell reference in excel is referring the other cells to a cell to use its values or properties. For instance, if we have data in cell A2 and want to use that in cell A1, use =A2 in cell A1, and this will copy the A2 value in A1. instead of entering the numbers directly into the formula itself.
Copy the above table to an 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 the 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 a forward slash.
Now again, select Previous Year Value.
Hit the 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 the percentage (%) symbol.
You can also press Ctrl + Shift + % as a shortcut key to apply percentage change. As soon as we apply the 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, let’s 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!Error Value As #DIV/0!#DIV/0! is the division error in Excel which occurs every time a number is divided by zero. Simply put, we get this error when we divide any number by an empty or zero-value cell..
So to get rid of this, we need to use the IFERROR function in excelUse The IFERROR Function In ExcelWhen an error occurs in any formula or argument, the IFERROR excel function is used to display the result. This function can be used in conjunction with other functions to alert the user of any errors in the formula..
The first argument of this function is Value, so for this, apply the 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 the 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 a 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 –