Excel Percent Change ( Table of Contents)
Introduction to Percent Change in Excel
Calculating Percentage seems to be a simple task for anyone who knows how to calculate percentages. You just need to divide the number value by total and multiply it by 100 to convert it into a percentage. However, when it comes to calculating the change in percentage, it is not the task as simple as it looks to calculate the percentage. Have you ever come up with a situation where you have an old value for some variable and a new value for the same variable over a period of time? Sometimes calculating the change in percentage is the need of a business hour since that may be something that affects your running business in a hard way. However, sometimes this calculation is way beyond the scope of the things which we have learned during our high schools. Therefore, in this article, we will teach you how to calculate the change in percentage through Excel. Well, Excel is easy to understand as well as formulate the tasks, and that is what makes it a great tool over the years and forth. Let’s see how percentage change works in Excel.
The formula for Percentage Change in Excel
The generic formula for capturing percentage change in Excel is as follows:
=(current_value – previous_value)/previous_value
- current_value – is a new value of the variable which is under consideration
- previous_value – is an old value of the variable which is under consideration for calculating percentage change.
Well, the formula itself is obvious explanatory. Since we are trying to figure out what the change is, we need to find a difference between the current and the previous value of that variable. Moreover, we need to check what is the percentage of change that happens; we need to divide the difference between new and old value by old value (Imagine dividing the difference by a new value. How will that look alike?).
Examples of Percent Change in Excel
Let us understand the examples of Percent Change in Excel.
Example #1 – Change in Percentage
Suppose we have two numbers like 1230 and 1180 as current and previous numbers, respectively. We want to check what is the difference in the percentage of these two numbers under Excel. Follow the steps below to get the same.
Step 1: Open a new Excel file, and in cell A1, type equal (=) sign to initiate a formula.
Step 2: Use the opening round bracket to start the formula. Inside the bracket, type 1230-1180 will give us the difference between two numbers, current and previous.
Step 3: Now, close the formula using the closing round bracket so the difference between two numbers will have a preference first in the calculation (you know the BODMAS rule, right?).
Step 4: Now, use 1180 as a value to be used to divide this difference in values. See the screenshot given below.
Now, our formula is complete. Press Enter key to get the result for this formula. It should look like the one in the screenshot below.
This result seems to be in decimals. We wanted a result in percentages. For that, we need to change the cell numbering property to a percentage.
Step 5: Select cell A1 to change the number formatting for the same. Under the Home tab, navigate to the Number Formatting group and click on the Percentage symbol placed in that group to change the number format of cell A1 as a percentage.
This is something that you can achieve with a keyboard shortcut as well. Use Ctrl+Shift+% on your keyboard, and you can change the cell formatting into percentage style.
You should see a result as shown in the screenshot below:
This means the percent change for the two given values is 4%.
Example #2 – Change in Percentage when Values are Stored in Cells
Now, in the previous example, we have seen that the numbers are used as a reference under the formula. However, what if we have thousands of numbers and wanted to check the percent change for those? Will it be feasible to type the formula for thousands of time with changing numbers for every cell? Definitely not, right? Suppose we have data as shown in the screenshot below. Where column A contains Current Value, column B contains Previous Value, and column C is something where we want a generic formula that captures the percent change. Follow the steps below to get the result as per our expectations:
Step 1: Inside Cell C2, start formula using equals to sign and opening bracket.
Step 2: Use the formula as A2-B2 inside the bracket. Since we get a difference between the current and previous value, close the formula using the close bracket.
Step 3: Now, we need to divide this change in numbers by the previous value to get the result converted into a percentage value. Divide the bracketed quantity by B2 in cell C2.
Step 4: Now, if you press Enter key, you can see a percentage change value under cell C2, as shown below:
This is what a generic formula looks like. Even if we change the values for cells A2 and B2, respectively, this formula will still give the results, and you don’t need to input it again.
Step 5: Drag this formula across C3 to C6 in order to get the desired percent change values associated with respective column A and column B values. You can use the keyboard shortcut as Ctrl+D as well for this task. Just select all the cells from C2 to C6 and then press Ctrl+D on your keyboard.
Step 6: Now select cells from C2 to C6 again and navigate towards the Number Formatting group under the Home tab to change the number type for these cells. We ideally wanted the formatting to be in percentage. Within that group, click on Percentage Style, and it will change the number formatting from C2 to C6 as a percentage. This can also be achieved using the keyboard shortcut Ctrl+Shift+%.
The final output should look like the one in the screenshot below:
This is how we can find the percentage change in Excel. That is the end of this article. Let’s wrap the things up with some points to be remembered:
Things to Remember
- Just make sure you are using previous_value as a divisor in the entire process of calculating the percent change. Otherwise, the results produced will be wrong.
- #DIV/0! Error will occur If you try to divide the change in numbers by zero.
- Format the result cells as Percentage Style in order to get a result visible in a percentage format. Excel does not automatically format the cells for you.
This is a guide to Percent Change in Excel. Here we discuss How to use Percent Change in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –