Open In App

4 Methods to Calculate Percent Change in Excel

Last Updated : 18 Nov, 2024
Comments
Improve
Suggest changes
Like Article
Like
Report

Calculating percent change in Excel is a valuable skill for analyzing data trends and measuring growth or decline. This guide will show you four easy methods to calculate percent change, whether you’re comparing sales data, financial metrics, or other datasets. You’ll learn to use simple formulas such as (New Value - Old Value) / Old Value and how to format results as percentages. Additionally, we’ll cover using percent change with absolute references, handling negative changes, and leveraging built-in Excel functions to automate calculations.

By mastering these techniques, you can quickly gain insights and make data-driven decisions with confidence. Follow along to explore each method step-by-step and see how it applies to real-world scenarios.

How-to-Calculate-Percent-Change-in-Excel
Calculate Percentage Change in Excel

What is Percent Change in Excel

Percentage change in Excel lets you figure out how much a value has gone up or down compared to another value. It's like a percentage report card for your data and in this article we will calculate percentage increase and decrease in Excel. For example,

  • You have two numbers, like your sales this month and last month.
  • Excel helps you calculate the difference between those numbers.
  • Then it divides that difference by the original number (usually the older one).
  • Finally, it shows you this difference as a percentage.

So, if your sales were higher this month, you'll see a positive percentage change. If they were lower, you'd see a negative percentage change.

Percent Change in Excel is a handy tool for tracking changes in things like:

  • Sales figures
  • Inventory levels
  • Website Traffic
  • Stock prices

Excel Percent Change Formula

As percent increase or decrease is just a particular case of percentage variance, it is calculated with the same formula: the percentage of change formula in Excel. By using Excel formulas for percentage change we can accurately determine the variation.

Both formulas you provided are correct ways to calculate percent change in Excel, though they are slightly different in their representation.

1. Percent Change = (New Value – Old Value) / Old Value

This formula calculates the absolute change between the new and old values and then divides it by the old value. It provides the relative change as a proportion of the old value.

2. Percent Change = New Value / Old Value – 1

This formula first calculates the ratio of the new value to the old value and then subtracts 1. It represents the relative change as a multiplier of the old value.

Both formulas yield the same result; they just represent the calculation differently. Users can choose the one that is most intuitive or convenient for their purposes.

Percentage Change Equation in Excel

Another way to calculate the percentage change is by using this formula:

Percentage Change (%) = (Ending Value ÷ Beginning Value) – 1

If you're calculating it by hand, remember to multiply the result by 100 to get the percentage.

Using this method makes it easier to compare figures over time or with industry benchmarks.

Percent Change Calculator

A Percent Change Calculator is a handy tool that calculates the percentage increase or decrease between two values. This tool can help you understand how much a value has changed over time, making it useful for tracking changes in sales, financial metrics, and other data points.

How to Calculate Percent Change in Excel

This section provides a clear, practical approach to calculating percent change in Excel, ensuring you can apply these methods to your data effectively.

Step 1: Open Excel and Enter Data

First, enter the initial and final values in two adjacent cells. Let's say the initial value is in cell A1 and the final value is in cell B1.

Excel formulas for percentage change
Enter Data in Spreadsheet

Step 2: Calculate Change

In a new cell, subtract the initial value from the final value. You can do this by typing "=B1-A1" in an empty cell. This will give you the difference between the final and initial values.

Calculate Percent Change in Excel
Calculate change

Step 3: Calculate Percent Change

In another new cell, divide the change value by the initial value. You can do this by typing "=C1/A1" where C1 is the cell containing the change value and A1 is the initial value.

Calculate Percent Change in Excel
Enter Formula for Percent Change

Step 4: Convert to Percentage

By default, Excel will display this as a decimal. To convert it to a percentage, click on the cell containing the percent change value, then click on the "Format" tab in the Excel ribbon. In the "Number" group, click on the dropdown menu next to the percentage symbol (%) and select "Percentage".

Calculate Percent Change in Excel
Change numbers to Percent>>

Step 5: Format(Optional)

You may also want to adjust the formatting of the cell to make it more visually appealing. You can do this by right-clicking on the cell, selecting "Format Cells", and then choosing your desired formatting options.

How to Change the Number of Decimal Places

To change the number of decimal places in Excel, follow these steps:

Step 1: Select the Cells

Highlight the cells where you want to adjust the decimal places.

Step 2: Go to the Home Tab

Click on the "Home" tab in the Excel ribbon.

Step 3: Adjust Decimal Places

  • In the "Number" group, look for the Increase Decimal (⯅) and Decrease Decimal (⯆) buttons.
  • Click Increase Decimal to show more decimal places or Decrease Decimal to show fewer.
Click-on-Decimal-Place
Home Tab>>Increase the Decimal Place

To quickly change the number of decimal places displayed in Excel, you can use these shortcuts:

To increase decimal places: Press Alt + H + 0

To decrease decimal places: Press Alt + H + 9

Calculating Percent Increase and Decrease in Excel

As percent increase or decrease is just a particular case of percentage variance, it is calculated with the same formula:

(new value - old value) / Old value

Or

new value / initial value - 1

The mathematical formula to calculate value after a percentage increase is given below,

Total value = value(1 + (percentage increase/100)

For example, a 20% increase in 100 will be calculated as:

Final value = 100(1 + (20/100))

= 100(120/100)

= 120

Calculation using Excel

We can use the following formula in Excel to calculate the final value after a percentage increase:

= cell_value1 * ( 1+ cell_value2)

cell_value1: It indicates the cell consisting of original value.

cell_value2: It indicates the cell containing percentage value.

Example:

Calculate Percent Change in Excel
Example



Output:

Calculate Percent Change in Excel
Output

How to Calculate a Percentage Increase in Excel

For calculating percentage increase in excel, we will use the following table in which the annual salary of different individuals is given along with the percentage of increment received. We will use the percentage increase formula in Excel to find out their final annual income after the increment.

The formula for percent increase in Excel can be used to determine how much their salary has increased. Using Excel formulas for percentage increase, we can accurately calculate the new salaries post-increment.

Calculate Percent Change in Excel
Enter the Data


Step 1: Use Percentage Increase Formula

Calculate Percent Change in Excel
Use the Formula

Step 2: Press Enter

Calculate Percent Change in Excel
Press Enter

Step 3: Drag the fill handle over to the data range.

Calculate Percent Change in Excel
Drag the Fill Handle

Output:

Calculate Percent Change in Excel
Output

How to Calculate a Percentage Decrease in Excel

For percentage decrease, we can use the following formula:

 = cell_value1 * ( 1- cell_value2)

cell_value1: It indicates the cell consisting of original value.

cell_value2: It indicates the cell containing percentage value.

For the purpose of demonstration, we will use the following table in which the value of the different products is given along with the percentage discount to be given on the products. We will use the percentage decrease formula to find out their final value after the discount.

Calculate Percent Change in Excel
Enter the Data


Step 1: Use the formula

Calculate Percent Change in Excel
Use the Formula

Step 2: Press Enter

Calculate Percent Change in Excel
Press Enter

Step 3: Drag the fill handle over to the data range.

Calculate Percent Change in Excel
Drag the fill Handle

Output:

Calculate Percent Change in Excel
Output

Calculating Absolute Value of Percent Change

In Excel, the usual way to calculate the percentage change returns a positive value for an increase and a negative one for a decrease. To find the absolute value of the percentage change, which disregards whether it's an increase or decrease, you can use the ABS function to enclose the formula like this:

ABS((new_value - old_value) / old_value)

Calculate Percent Change in Excel
Enter the Formula

Tips and Tricks for Percent Change in Excel

  • To format the results as percentages, select the cells and apply the percentage format from the number format options in the Home tab.
  • Keep in mind the use of absolute and relative cell references when using formulas and or copying cells for calculations.
  • Use Conditional Formatting to visually highlight the adjusted values based on specific criteria.

What is the Percent Change Keyboard Shortcut in Excel

Utilize these shortcuts and simple formulas to quickly calculate percentages and save time on your data analysis tasks.

By default, Excel shows the calculated fraction as a decimal. To convert this decimal to a percentage, you have four options:

Step 1: Click the "%" symbol in the "Numbers" group on the "Home" tab.

Step 2: Choose "Percentage" from the dropdown in the "Numbers" group.

Step 3: Press Ctrl + Shift + 5 on your keyboard.

Step 4: Use the shortcut Alt + H + P.

Basic Percentage Calculation:

  • Use the formula: =(part/total)*100
  • For example, if you have 50 in cell A1 and 200 in cell B1, the formula in C1 would be =(A1/B1)*100.

Percentage Increase/Decrease:

  • Use the formula: =((new value - old value)/old value)*100
  • For example, if A1 is the old value and B1 is the new value, the formula in C1 would be =((B1-A1)/A1)*100.

Percentage of a Total:

  • Use the formula: =part/total
  • Format the cell as a percentage: Select the cell, press Ctrl+Shift+%.

Quick Percentage Formatting:

  • Enter the number you want to format.
  • Select the cell and press Ctrl+Shift+% to quickly format it as a percentage.

Using Excel Functions:

  • =PERCENTILE(array, k): Returns the k-th percentile of values in a range.
  • =PERCENTRANK(array, x): Returns the percentage rank of a value in a data set

Also Read

Conclusion

Learning how to calculate percent change in Excel is a powerful way to quickly understand how data shifts over time, whether you're determining percent increase, figuring out an Excel percentage difference, or calculating a salary increase percentage. By using Excel’s formulas, you can calculate percent change, easily see percentage increases, and make sense of complex data patterns. With just a few steps, Excel becomes your go-to tool for calculating an increase by percent, acting as a reliable percentage increase calculator. This skill helps you spot trends, compare values, and make informed decisions. Whether you’re working with budgets, salaries, or performance metrics, mastering these formulas empowers you to interpret and respond to data changes confidently and accurately.


Next Article
Article Tags :

Similar Reads