Percentage Change Formula in Excel

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.

Percentage-Change-Formula-in-Excel.png

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Percentage Change Formula in Excel (wallstreetmojo.com)

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.

(Current Value – Previous Value) / Previous Value

Or else we can also use the below formula as well.

Current Value / Previous Value – 1

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

= 50000/400000

= 12.5 % increase in Revenue.

Examples

Now we will see how we can apply the same formula in excel.

You can download this Percentage Change Formula Excel Template here – Percentage Change Formula Excel Template

Example #1

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.read more instead of entering the numbers directly into the formula itself.

Copy the above table to an excel sheet.

percentage change formula example 1

Now put an equal sign in the cell D2 because all the formulas in excel must start with an equal sign only.

percentage change formula example 1-1

Now open the bracket and our first formula should be Current Value, and in this example, the current value is C2 cell value.

percentage change formula example 1-2

Then put minus (-) sign and choose Previous Year Value, i.e., B2 cell.

percentage change formula example 1-3

Close the bracket and put it divided by a symbol by entering a forward slash.

percentage change formula example 1-4

Now again, select Previous Year Value.

percentage change formula example 1-5

Hit the enter key to get the result of percentage change from the previous year to this year.

percentage change formula example 1-6

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.

percentage change formula example 1-7

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.

percentage change formula example 1-8

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.

0.00%;[Red]-0.00%

percentage change formula example 1-9

Example #2

Below are the salary details of employees when they joined and current salary.

percentage change formula example 2

To find out the variance percent change between these two numbers, let’s use the same formula as above.

Example 2-1

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.read more.

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.read more.

Example 2-2

The first argument of this function is Value, so for this, apply the percent change formula as usual.

Example 2-3

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%.

Example 2-4

Now see, we got rid of the error value because of the IFERROR function.

Example #3

Below is the sales employee’s revenue generation comparison from last month to this month.

Example 3

This time apply the second formula we have learned, i.e., Current Value / Previous Value – 1

Example 3-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 –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>