Calculate Percent Change in Excel

[nerdslife_team]

If you’re crunching numbers regularly, you’ll probably need to calculate percentages. Excel makes this straightforward by offering different formulas and techniques. It’s quite similar to the way you might calculate percentages by hand, so it’s easy to grasp.

One of the most frequent calculations you might do is finding the percentage change between two values. This is handy for showing how much something has grown or declined over time. It’s useful in finance, statistics, and lots of other areas.

For instance, if you’re comparing sales from last year to this year, calculating the percent change gives you a clear picture of the difference. This guide will cover how to calculate the percentage change, increase, and decrease between two numbers in Excel.

Calculating Percentage Change

Calculating the percentage change between two numbers is pretty simple. You basically find the difference and then divide by the original number.

Here are a couple of ways to do it:

=(new_value – original_value) / original_value

or

=(new Value / original Value) – 1

• new_value: This is the current or final value.
• original_value: This is the starting value.

Example:

Let’s say we have a list of fruit orders, with column B showing last month’s orders and column C showing this month’s.

To calculate the percentage change in the number of apples ordered, enter the formula below in cell D2.

=(C2-B2)/B2

After entering the formula, hit Enter. You’ll get a decimal result:

This isn’t yet formatted as a percentage. To fix that, select the cell, and click the ‘Percentage Style’ button in the ‘Number’ group under the ‘Home’ tab.

Now, the decimal will be displayed as a percentage:

Calculate Percentage Change for Multiple Rows

Now, let’s find the percentage change for all of the fruit items.

To do this for an entire column, enter the formula in the first cell of the result column and then use AutoFill.

Click on the small green square in the lower right of the formula cell and drag it down to cover the other cells.

Now you’ve got the percentage changes for all the rows.

If the new value is higher than the original value, the percentage will be positive. If it’s lower, the percentage will be negative.

For example, ‘Apples’ increased by 50% (positive), while ‘Avocado’ decreased by 14% (negative).

You can also highlight negative percentages in red, which is handy when you want to quickly spot them.

Right-click on the cells you want to format and choose ‘Format Cells’.

In the ‘Format Cells’ window, click ‘Custom’, and enter the code below in the ‘Type’ box:

0.00%;[Red]-0.00%

Click ‘OK’ to save.

Now, negative values will be in red and more decimal places will be shown.

You can also use this alternative formula to calculate percent change.

=(C2/B2)-1

This divides the new value (C2) by the original value (B2) and subtracts ‘1’.

Calculating Percent Change Over Time

To find out the period-to-period change, like month-to-month growth or decline, use this method. This is great if you need to track changes over a specific time.

For example, let’s say you have fruit prices for March (column B) and for July (column C).

Use this generic formula for percentage change over time:

=((Current_value/Original_value)-1)/N

Here, N is the number of periods (years, months, etc.) between the two values.

To find the average monthly rate of change over 5 months, use this formula:

=((C2-B2)/B2)/5

This divides the total percentage change by the number of months (5).

Calculating Percentage Growth/Change Between Rows

If you have a single column of numbers (like monthly petrol prices), you can calculate the change between rows.

To see how the price changed from month to month, use this formula:

=(B3-B2)/B2

Since you’re finding the change from January to February, leave the first cell in the result column blank. Then, put the formula in cell C3 and press Enter.

Drag the formula down to apply it to all the rows.

You can also calculate the percentage change from each month to January’s price. To do that, use an absolute reference for January by adding the $ sign in the cell reference, like this: $B$2. Here’s the formula:

=(B3-$B$2)/$B$2

As before, put this formula into cell C3. Copy the formula to the rest of the column. The B$2 will remain constant, while B3 will change to B4, B5, etc.

This gives you the percentage change in price for each month compared to January.

Calculating Percentage Increase

Calculating percentage increase is like calculating percentage change. It’s the rate of increase compared to the starting value. You need a starting number and a new number.

Formula:

Percentage Increase = (New_number - Original_number) / Original_number

Just subtract the starting number from the new number and divide by the starting number.

For example, if you have a list of bills for April and May, and you want to know the percentage increase from April to May, use the formula below:

=(C2-B2)/B2

Here, you are subtracting the April bill (B2) from the May bill (C2) and dividing by the April bill. Then, apply this to other rows using the fill handle.

A positive result (like 24.00%) means the amount increased, a negative result (like -13.33%) means it actually decreased.

Calculating Percentage Decrease

Calculating percentage decrease is very similar to percentage increase, except that the new value is smaller than the original value.

Formula:

Percentage Decrease = (Original_number - New_number) / Original_number

Let’s say you have storage devices and their prices for 2018 and 2020.

If the price decreased from 2018 to 2020, here’s how to calculate the decrease:

=(B2-C2)/B2

You are subtracting the 2020 price (C2) from the 2018 price (B2), and dividing by the 2018 price. Apply this formula to other cells using the fill handle.

Again, a positive result (like 20.00%) means the percentage decreased. If you get a negative result (like -13.33%), it means the percentage actually increased.

Common Errors

You might run into these common errors when using these formulas:

• #DIV/0!: This appears when you divide by zero or a blank cell. For example, if B6 in the formula =(B6-C6)/B6 is zero.
  

  

• #VALUE: This happens when you have non-numeric values or blank cells in the formula.
  

  

• #NUM!: This appears when the result of a formula is too large or small.
  

  

Convert an Error to Zero

To show ‘0%’ instead of an error, use the IFERROR function.

Syntax:

=IFERROR(value, value_if_error)

• value is the formula to check.
• value_if_error is what to display if the formula returns an error.

Let’s use this with the #DIV/0! error:

=IFERROR((B6-C6)/B6,0%)

If the formula causes an error, it will display ‘0%’.