Skip to main content

Calculate percentage change between 2 numbers in Excel

Author: Siluvia Last Modified: 2023-12-29

Whether it's for business analysis, academic research, or personal finance management, mastering percentage change calculations is essential for effective data analysis. This article will start with the essential formulas for calculating percentage increases and decreases in Excel, and then delve into more advanced operations, including handling negative numbers, dealing with zero values, and reverse-calculating original or new values based on given percentage changes.


Basic Formulas for Calculating Percentage Change

There are two basic formulas to calculate percentage change between two numbers:

Formula 1:

=(new_value - old_value) / old_value

Formula 2:

=new_value / old_value – 1

These formulas can help you determine how much a certain number has increased or decreased in percentage terms. Below you will find two examples of how these formulas can be used to calculate the percentage change between two numbers.


Calculate Percentage Change Between Two Numbers

This section will show you step by step how to calculate percentage increase and decrease between two numbers.


Calculate percentage increase

If the new value is greater than the old value, the result is a percentage increase. As shown in the screenshot below, suppose you have a website where the registered users are growing every year. To calculate the percentage increase of users per year, you can do as follows.

Step 1: Apply the formula to get the result

Select a blank cell (D2 in this case), enter one of the following formulas and press the Enter key to get the result. Select this cell and drag its Fill Handle down to get the rest of the results. And the result is displayed as general numbers as following screenshot shown:

=(C2-B2)/B2
=C2/B2-1

Step 2: Format the result as percentage

To format the numbers as percentage, keep the result cells selected, go to select the Percent Style button in the Number group under the Home tab.

Result

The percentage increase of users per year has now been calculated. See screenshot below:

Note: In the formula, C2 is the cell containing the new value and B2 is the cell containing the old value.

Calculate percentage decrease

If the new value is less than the old value, it's a decrease. The screenshot below shows a drop in product prices. To compute the percentage decrease of these prices, proceed as follows.

Step 1: Apply the formula to get the result

Select a blank cell (D2 in this case), enter one of the following formulas and press the Enter key to get the result. Select this cell and drag its Fill Handle down to get the rest of the results. And the result is displayed as general numbers as following screenshot shown:

=(C2-B2)/B2
=C2/B2-1

Step 2: Format the result as percentage

To format the numbers as percentage, keep the result cells selected, go to select the Percent Style button in the Number group under the Home tab.

Result

The percentage decrease of these prices has now been calculated. See screenshot below:

Note: In the formula, C2 is the cell containing the new price and B2 is the cell containing the old price.

Advanced operations

This section demonstrates different situations you may encounter when calculating percentage change between two numbers.


Calculate percentage change with negative numbers

When calculating percentage changes, you may encounter negative number as follows:

let's illustrate each of these scenarios with specific examples:

Both the two numbers are negative

The standard formula for percentage change is effective even when both values are negative.
For instance, consider a company whose losses increased from $10,000 last quarter (old value is -10,000) to $50,000 this quarter (new value is -50,000). As illustrated below, using the standard formula (=(C2-B2)/B2) yields a 400% increase, indicating the losses have quadrupled from an absolute value of $10,000 to $50,000.

One of the numbers is negative

In this case, you may encounter situations where the old value is negative, or the new value is negative.

Old value is positive, new value is negative

The standard percentage change formula also works fine when the new value is negative.

For example, a stock’s value shifted from positive to negative. It was valued at $10 (old value is 10) last month. This month, due to market downturns, its value dropped to -$10 (new value is -10). To calculate the percentage change between these two numbers, I apply the standard formula (=(C2-B2)/B2) to get the result of -200%. This represents a significant decrease, indicating a shift from a gain to a loss.

Old value is negative, new value is positive

For example, a business moves from loss to profit. Last year, this small business had a loss of $10000 (old value is -10000) and made a profit of $20000 (new value is 20000) this year. If you apply the standard percentage formula to calculate the percentage change, the result will be incorrect.

Here the result -300 is misleading in the actual scenario. It suggests a negative change, implying that things have worsened, but in reality, the situation has improved from a loss to a profit.

In this case, you need to use the ABS function to make the denominator positive ensures that the percentage change result accurately reflects the shift from negative to positive. Here is the new formula:

=(C2-B2)/ABS(B2)

Here the result 300% indicates the actual positive change from loss to profit.

Note:
  • Be aware that the ABS method may produce misleading results. As illustrated in the screenshot below, all businesses move from loss to profit, with Company F being the most profitable. However, it shows a smaller percentage change compared to others, which makes no sense. Therefore, use this method cautiously!

Calculate percentage change with zero

While calculating percentage changes in excel is very simple, you may run into some difficulties when the calculation involves zeros.

The old value is zero

When the old value is zero, the standard formula returns #DIV/0! Error value. See screenshot:

In this case, the most acceptable solution is to treat the percentage change as 100%. In basic math, changes from 0 to any positive number will be regarded as a 100% increase.

Here are the basic formulas:

Formula 1:

=IFERROR((new value - old value) / old value, 1)

Formula 2:

=IFERROR((new value / old value - 1, 1)

To display the result as 100% rather than #DIV/0! Error, you can apply one of the following formulas:

=IFERROR((C2 - B2) / B2, 1)
=IFERROR((C2 / B2) -1, 1)

The new value is zero

When the new value is 0, the result is -1, which is -100%. The result -100% represents a complete decrease. In this case, the result is reasonable because changes from a positive value to 0 will be a 100% decrease. The result is acceptable.


Calculate old value based on percentage change

Sometimes, you may encounter a situation where you know the percentage change and the latest value, now you need to determine the original value. For example, if this year's sales are $12,000, having increased by 20% from last year, how would you find last year's sales? In such cases, you would use the following formula:

The basic formula:

=New value / (1 + Percentage Change)

Select a blank cell such as C2, enter the following formula and press the Enter key to get the result.

=B2/(1+A2)

Note: In this formula, B2 is the cell containing the new value, and the cell A2 contains the percentage change.

Calculate new value based on percentage change

The opposite situation is where you know the percentage change and the original value and need to determine the new value. For instance, if this year's sales are $10,000 and you aim for next year's sales to be 20% higher than this year's, how would you calculate next year's sales?

The basic formula:

=Old value * (1 + Percentage Change)

Select a blank cell such as C2, enter the following formula and press the Enter key to get the result.

=B2*(1+A2)

Note: In this formula, B2 is the cell containing the old value, and the cell A2 contains the percentage change.

In conclusion, mastering the calculation of percentage change between two numbers in Excel is a versatile skill that can significantly enhance your data analysis capabilities. Whether dealing with straightforward increases and decreases, or more complex scenarios involving negative numbers or zero values, this tutorial provides various formulas for solving these tasks. For those eager to delve deeper into Excel's capabilities, our website boasts a wealth of tutorials. Discover more Excel tips and tricks here.

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (26)
Rated 5 out of 5 · 1 ratings
This comment was minimized by the moderator on the site
((new value/old value)-1)*100 For % change
This comment was minimized by the moderator on the site
=((new value/old value)-1)*100 For % change
This comment was minimized by the moderator on the site
doesnt work
This comment was minimized by the moderator on the site
it is possible to calculate three values with the percentage change?
Rated 5 out of 5
This comment was minimized by the moderator on the site
Hi Edi Silva,
You can try this formula: =(first value-second value)/(third value-second value)
This comment was minimized by the moderator on the site
I have 0 in A1, 5 in B1, increase in percentage should be 500%. However with the given formula (B1-A1)/A1 = (0-500)/0 gives me a #DIV/0! error.In case if i apply =IF(A2<>0,(A3-A2)/A2,"0"), it return s me "0". neither of them helps. Any solution to this???
This comment was minimized by the moderator on the site
In case anyone still needs an answer for this problem, use this =IF(A1<>0, (B1-A1)/A1, (B1-A1))
using the percent style format will make (B1-A1) in this case (5-0) = 5 become 500%.
This comment was minimized by the moderator on the site
Hey Stanley,

Thanks for the formula!
Would you mind explaining the formula, please?
This comment was minimized by the moderator on the site
SMH SMH SMH SMH SMH
This comment was minimized by the moderator on the site
I hope you're not that dumb, dividing by 0
This comment was minimized by the moderator on the site
Any help for picking up value only it falls between 85% to 99% else blank.
Such as A is 10 and in B if we put any number , and in C it show the value only if it is fals between 85% to 99% of A
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations