Joined: 25 Sep 2007
|Posted: Fri Jan 13, 2017 5:46 pm Post subject: Calculate percentage of change between two values in Excel
|In the following table, I have sales report from 2010 till 2017.
I want to calculate the percentage of change in sales in 2011 from the previous year 2010, then percent change in 2012 from the previous year 2011 and so on.
I want to make my calculation in Column C. Here I need to change the cell format. Select Column C > right mouse click > Format Cells... > from Category select Percentage. Leave 2 on the Decimal places. Click OK.
In cell C3 enter =(B3-B2)/B2. Copy the formula down the column. Now you have it.
There is another easy way to calculate the percentage of change. I am doing it in Column D, so you see the results are the same. Remember, it's a fun to work with Excel
Like Column C , select Column D and change its cell format to percentage (as you did for Column C).
In cell D3 enter =(B3/B2)-1. Now copy the formula down the column. Results are the same. You just saved few seconds
By the way, if you would like to do the calculation with reference to a particular year, let's say, 2010, how would you do this. I mean, you want to see the percent changes in your sales from 2011 till 2017 and you always want it to compare with the sales in the year 2010.
In this case, you make the sales for year 2010 as your absolute reference, that means cell $B$2 is fixed.
I will do it in Column E. So like Column C and D, you will need to change the cell format for Column E to percentage.
Now in cell E3 you can apply any of the above two formulas with cell B2 as absolute reference. I mean in cell E3 enter either =(B3-$B$2)/$B$2 or =(B3/$B$2)-1.
Remember, here you see percent change with reference to the sales in year 2010. That's why the results are different than the Columns C or D.
Have fun my good friends
Read wonderful articles (English & German) on Science & Tech, Environment, Health and many other topics only on BlogArena.
For comments of suggestions, please contact us: firstname.lastname@example.org