How to Calculate Percentage Decrease in Excel (4 Suitable Ways)

Get FREE Advanced Excel Exercises with Solutions!

Looking for ways to calculate percentage decrease in Excel? Then this is the right place for you.

While working with large Microsoft Excel, sometimes we need to calculate percentages. Some percentages are positive whereas some are negative. We can denote the negative percentage as the percentage decreases.

We can easily do that by using Excel formulas and VBA Macros. This is an easy and time-saving task also.

How to calculate percentage decrease in Excel

In this article, we’ll learn 3 quick and suitable ways to calculate percentage decrease in Excel effectively with appropriate illustrations.


Introduction to Percentage Decrease

While subtracting the second value from the first value, you will get the change between these two values. If the first value is less than the second value, you will get a decreased value.

Decrease = First value – Second Value

Then divide this decreased value by the first value and multiply by 100, and you will get the percentage decreased.

Percentage Decreased = (Decrease / First value)*100%

Before diving into the big picture, let’s get to know about today’s Excel sheet first. This dataset contains 4 columns. They are Brand, Device, Old Price, and New Price. There are the products and prices respectively. Here, you can notice that the new prices are lower than the old prices.

Now for different products, we will show you how to calculate the percentage decrease in price in Excel.

Dataset to Calculate Percentage Decrease


1. Using Excel Formula to Calculate Percentage Decrease

We can easily calculate the percentage decrease by using the mathematical formula in Excel. This is a time-saving way also. From our dataset, we will calculate the percentage decrease. Let’s follow the instructions below to calculate the percentage decrease!

  • Select the cell where you want to calculate the percentage decrease and write the following formula.
=(E5-D5)/D5
  • Press Enter and drag the Fill Handle to copy the formula to the other cells.
  • Finally, you can see that we have copied the formula to the other cells and got the percentage decrease.

Calculating Percentage Decrease Using Mathematical Formula

Here, we calculated the percentage change between the Old Price and the New Price. We subtracted the Old Price from the New Price and then divided the result by the Old Price. The calculations have been done using cell reference.
  • You may find the results in decimal. To change that, select the cells where you have got the results in decimal.
  • Go to the Home tab >> Click on the drop-down menu from the Number group >> Select Percentage.

Changing Number Format

  • Finally, you will see that the results are shown in percentages.

Percentage Change in New Price

Read More: How to Calculate Average Percentage Increase in Excel


2. Calculating Percentage Decrease from Specific Value in Excel

Suppose you want to calculate the percentage decrease from a specific value. In this case, you will have to use an absolute cell reference.

Here, in cell C18 we have the Maximum Price according to the old prices and we want to calculate the percentage decrease of new prices from this value. Here are the steps to do that.

  • Select cell F5 and insert the following formula.
=(E5-$C$18)/$C$18
  • Press Enter and drag the Fill Handle to copy the formula to the other cells.

Calculating Percentage Decrease from Specific Value

In the above formula, we have used cell C18 as an absolute cell reference to calculate the percentage decrease from this value.

Read More: How to Calculate Price Increase Percentage in Excel


3. Running VBA Macro to Calculate Percentage Decrease

Now we’ll show how to calculate the percentage decrease in Excel by using a simple VBA code. It’s very helpful for some moments and a time-saving way also. From our dataset, we will calculate the percentage decrease.

  • Go to the Developer tab >> click on Visual Basic.

Clicking on Visual Basic to open Microsoft Visual Basic Editor

  • Now, the Microsoft Visual Basic editor will open. To add a module, click on Insert >> Select Module.

Inserting Module

  • Copy the following code in your module and go back to your worksheet.

Code to Calculate Percentage Decrease

Code Breakdown

  • In the above code, we declared lastRow and i as Long and oldPrice, newPrice and percentageDecrease as Double.
  • We calculated the number of rows using the Count method and assigned that value as lastRow.
  • Then, we assigned the values of column D as oldPrice and column E as newPrice starting from the 5th row.
  • We looped through each row and calculated the percentage decrease between the oldPrice and the newPrice using the formula:

percentageDecrease = (oldPrice - newPrice) / oldPrice * 100

  • Finally, we inserted the results in column F using the Format function to display the percentage with two decimal places and a “-“ sign to indicate a decrease.
  • Then, go to the Developer tab >> click on Macros.

Clicking on Macros to select a macro to run

  • Select the Percentage_Decrease named macro and click on the Run button.

Running a macro to Calculate Percentage Decrease

  • Now, you will be able to calculate all the values of the percentage decrease in the below screenshot.

Output after running code

Read More: How do you Calculate Percentage Increase or Decrease in Excel


4. Determining Percentage Decrease for Negative Values

In this section, we will explain how to calculate the percentage decrease in Excel for negative values. We will explain 2 different situations here.

Here, we have a dataset containing some values of City, State, Old Temperature, and New Temperature. Now, we will calculate the percentage decrease in temperature of each city.

Dataset to Determine Percentage Decrease for Negative Values in Excel


4.1. Both Values Are Negative

In this example both the old value and the new value are negative. For this type of situation, the formula for percentage change is:

Percentage Change = (Old Value – New Value)/Old Value

Suppose you have a dataset that contains the Old Temp and the New Temp. We will show you how you can calculate the percentage decrease. Let’s see the steps.

  • Select the cell where you want to calculate the percentage change and write the following formula.
=(D5-E5)/D5
  • Press Enter and drag the Fill Handle down to copy the formula to the other cells.
  • You can see that I have copied the formula to the other cells and got the percentage change.

Determining Percentage Decrease When Both values are Negative

Here, we calculated the percentage decrease between Old Temp and New Temp. We subtracted the New Temp from the Old Temp and then divided the result by the Old Temp.

4.2. New Value Is Negative and Old Value Is Positive

For this example, we have taken a dataset where the new value is negative and the old value is positive. For this situation, the formula for percentage change is:

Percentage Change = (New Value – Old Value)/Old Value
  • Select the cell where you want to calculate the percentage change and insert the following formula.
=(E5-D5)/D5
  • Press Enter and drag the Fill Handle down to copy the formula to the other cells.

Determining Percentage Decrease When New Value Is Negative and Old Value Is Positive

In the above formula, we calculated the percentage decrease between Old Temp and New Temp. We subtracted the Old Temp from the New Temp and then divided the result by the Old Temp.

Read More: How to Calculate Percentage Increase from Zero in Excel


How to Calculate Percentage Increase in Excel

You can calculate the percentage increase using a similar mathematical formula in Excel. Here, in the dataset given below you can notice that the new prices are higher than the old prices. So, to calculate the percentage increase in new prices follow the steps given below.

  • Select the cell where you want to calculate the percentage increase and write the following formula.
=(E5-D5)/D5
  • Press Enter and drag the Fill Handle to copy the formula to the other cells.

Calculating Percentage Increase in Excel


How to Determine Values After Percentage Decrease in Excel

In this example, we will show you how you can calculate the values after a percentage decrease in Excel. Suppose you have a product list, their Old Price, and Percentage Change. Here, the negative sign before the percentage change indicates the percentage decrease in the prices.

Dataset with different Percentage Decrease values

Now, we will show how to calculate the New Price from this dataset.

  • Select the cell where you want to calculate the New Price and insert the following formula in the selected cell.
=D5*(1+E5)
  • Press Enter and copy the formula to the other cells by dragging the Fill Handle down. Thus, you will get all the values of the New Price.

Determining Values after Percentage Decrease in Excel

Here, we summed 1 with the percentage change and then multiplied it by Old Price. The formula returns the New Price.

Read More: How to Calculate Average Percentage Increase for Marks in Excel Formula


How to Use Specific Percentage Decrease to Calculate Values in Excel

Now you may need to calculate values on the basis of a given specific percentage decrease.

Here, we have a Discount value in percentage. In this example, we will use this specific value (12%) as a percentage decrease to calculate values in Excel.

Dataset to Calculate Values Using Specific Percentage Change

Using the following formula you can calculate the New Value using a specific percentage decrease (Discount) in one go.

New Value = Old Value * (1 - Percentage Increase)

When you are told that the price will be decreased by 12%, your updated value will be (100% – 12%) of the present price. 1 is the decimal equivalent of 100%. When you are adding 12% to 1, it will add the decimal equivalent of 12% (0.12) to 1.

  • Select the cell where you want to calculate the New Price and insert the following formula in the selected cell.
=D7*(1-$C$4)
  • Press Enter and you will get the result.
  • Next, drag the Fill Handle to copy the formula.

Using Specific Percentage Decrease to Calculate Values

Here, we subtracted the Discount from 1 and then multiplied the result by Old Price. The formula returns the New Price. We used absolute cell reference for the discount percentage so that the formula does not change while using Autofill.

Common Errors While Calculating Percentage Increase or Decrease Using Formulas in Excel

You may run into different types of errors while using formulas in Excel. Here I have listed some of them with their possible reasons.

  • #DIV/0: It may occur if you try to divide any number by zero. To solve this error you may use the IFERROR function for datasets that contain zero.
  • #Value: It may occur when you leave cells blank or insert different types of data than you should have. Such as you are supposed to insert a number in the formula, but you have inserted a text value.
  • Num!: It may occur when you insert an invalid numeric value into the formula.
  • #Name?: It occurs when you misspell any function name.
  • #REF!: It may occur when you refer invalid cell or any cell that has been deleted in a formula.

Things to Remember

  • If the Developer tab is not visible in your ribbon, you can make it visible. To do that, go to File >> Option >> Customize Ribbon.
  • You can pop up the Microsoft Visual Basic for Applications window by pressing Alt + F11.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

We hope all of the suitable methods mentioned above on how to calculate percentage decrease will now help you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.


<< Go Back to Percentage Change | Calculating Percentages | Calculate in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdur Rahim Rasel
Md. Abdur Rahim Rasel

MD. ABDUR RAHIM is a marine engineer proficient in Excel and passionate about programming with VBA. He views programming as an efficient means to save time while managing data, handling files, and engaging with the internet. His interests extend to Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning, reflecting his versatile skill set. He earned a B.Sc in Naval Architecture & Marine Engineering from BUET, and now he has become a content developer, creating technical content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo