How to calculate the percentage amount that a value has changed in Excel. This includes quickly calculating this value for an entire list.
Compare Each Value to the First in a List
The formula to calculate the change is simple:
(Value2-Value1)/Value1
Enter the above formula in Excel:
You will get a whole number value like this:
Select the cell with the formula and go to the Home tab and click the percent % button in the Number group.
If you are in a version of Excel before 2007 or just want a quick alternative, you can use the keyboard shortcut Ctrl + Shift + % to format the cell as a percentage.
If you have a list of numbers, like in this example, you can copy the formula down to get the percentage change between each row. Just select the cell with the formula and double-click the bottom right-hand corner of the cell or click and hold that corner and drag it down.
If you want to return the percentage change between each value and the first value, we need to make the cell reference for cell A1 absolute; that means that the cell reference will not change as the formula is copied down the list.
The new formula would look like this:
=(A2-$A$1)/$A$1
Notice the dollar signs for the A1 cell references that were not there in the previous example.
Copy the formula down and format it as a percentage and we get this:
Now each percentage change is relative to the first number in the list, 10.
To learn more about cell references, check out our tutorial on absolute and relative cell references in Excel. This will help you make much more powerful formulas.
Much of what you do in Excel will be modifying real-world math formulas like in this tutorial. The trick is to get those formulas to work with and for the data that we have in the spreadsheet. This tutorial was meant to give you more of an idea of how you can do that, rather than just to teach you the formula for calculating a percentage change.
Don't forget to format the output as a percentage; Excel does not usually automatically do this for you.
Make sure to download the sample file for this tutorial so you can work with these examples in Excel.